RIGHT JOIN in MySQL - Mini Project: Build & Apply

Understanding RIGHT JOIN in MySQL

📖 Scenario: You are managing a small bookstore database. You have two tables: books and authors. Some books may not have an author listed yet, but you want to see all authors including those who have not written any books.

🎯 Goal: Build a MySQL query using RIGHT JOIN to list all authors and their books, including authors without any books.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (string).

Create a table called books with columns book_id (integer), title (string), and author_id (integer).

Insert the exact data provided for both tables.

Write a RIGHT JOIN query to show all authors and their books, including authors with no books.

Select authors.author_name and books.title in the result.

💡 Why This Matters

🌍 Real World

RIGHT JOIN is useful when you want to see all records from one table (like authors) even if related records (like books) are missing.

💼 Career

Understanding joins is essential for database querying roles, data analysis, and backend development.

1

Create the authors table and insert data

Write SQL statements to create a table called authors with columns author_id (integer) and author_name (varchar 50). Then insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

MySQL

-- Create authors table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer), title (varchar 100), and author_id (integer). Then insert these exact rows: (101, 'Book A', 1), (102, 'Book B', 1), (103, 'Book C', 2).

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Create books table and insert data
-- Your code here

Need a hint?

Define the books table with three columns and insert the given rows.

3

Write a RIGHT JOIN query to list all authors and their books

Write a SQL query that uses RIGHT JOIN to join books to authors on books.author_id = authors.author_id. Select authors.author_name and books.title.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Book A', 1),
(102, 'Book B', 1),
(103, 'Book C', 2);

-- Write the RIGHT JOIN query below
-- Your code here

Need a hint?

Use RIGHT JOIN to include all authors even if they have no books.

4

Complete the query with an ORDER BY clause

Add an ORDER BY clause to the query to sort the results by authors.author_name in ascending order.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Book A', 1),
(102, 'Book B', 1),
(103, 'Book C', 2);

SELECT authors.author_name, books.title
FROM books
RIGHT JOIN authors ON books.author_id = authors.author_id
-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY authors.author_name ASC to sort alphabetically by author name.