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

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

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

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

Hint

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

Practice

1. What does a RIGHT JOIN do in SQL?

easy

A. Returns all rows from the right table and matching rows from the left table.

B. Returns all rows from the left table and matching rows from the right table.

C. Returns only rows that have matching values in both tables.

D. Returns all rows from both tables, matching where possible.

Solution

1. Step 1: Understand the direction of RIGHT JOIN

   A RIGHT JOIN returns all rows from the right table regardless of matches in the left table.

2. Step 2: Identify matching rows from the left table

   It includes matching rows from the left table and fills NULL where no match exists.

3. Final Answer:

   Returns all rows from the right table and matching rows from the left table. -> Option A

4. Quick Check:

   RIGHT JOIN = all right table rows + matched left rows [OK]

Hint: RIGHT JOIN keeps all right table rows, left table may have NULLs [OK]

Common Mistakes:

• Confusing RIGHT JOIN with LEFT JOIN
• Thinking RIGHT JOIN returns only matching rows
• Assuming RIGHT JOIN returns all rows from left table

2. Which of the following is the correct syntax for a RIGHT JOIN in MySQL?

easy

A. SELECT * FROM table1 RIGHT OUTER JOIN table2 WHERE table1.id = table2.id;

B. SELECT * FROM table1 JOIN RIGHT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

D. SELECT * FROM table1 RIGHT JOIN table2 USING id;

Solution

1. Step 1: Check standard RIGHT JOIN syntax

   The correct syntax is SELECT ... FROM table1 RIGHT JOIN table2 ON condition;.

2. Step 2: Validate each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; uses correct syntax with ON clause. SELECT * FROM table1 JOIN RIGHT table2 ON table1.id = table2.id; has wrong keyword order. SELECT * FROM table1 RIGHT OUTER JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 RIGHT JOIN table2 USING id; uses invalid syntax (parentheses required: USING (id)).

3. Final Answer:

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; -> Option C

4. Quick Check:

   RIGHT JOIN syntax = FROM table1 RIGHT JOIN table2 ON condition [OK]

Hint: RIGHT JOIN needs ON clause, USING only if columns match exactly [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Incorrect keyword order like JOIN RIGHT
• Confusing RIGHT JOIN with RIGHT OUTER JOIN (both valid but syntax matters)

3. Given these tables:

Employees
id | name
1 | Alice
2 | Bob

Departments
id | dept_name
2 | Sales
3 | Marketing

What is the result of:
SELECT Employees.name, Departments.dept_name FROM Employees RIGHT JOIN Departments ON Employees.id = Departments.id;

medium

A. [('Bob', 'Sales')]

B. [('Alice', NULL), ('Bob', 'Sales')]

C. [('Alice', NULL), ('Bob', 'Sales'), (NULL, 'Marketing')]

D. [('Bob', 'Sales'), (NULL, 'Marketing')]

Solution

1. Step 1: Identify RIGHT JOIN behavior with given tables

   RIGHT JOIN keeps all rows from Departments (right table). Departments have ids 2 and 3.

2. Step 2: Match Employees.id to Departments.id

   Employees with id=2 is Bob, matches Sales. No employee with id=3, so NULL for name and Marketing for dept_name.

3. Final Answer:

   [('Bob', 'Sales'), (NULL, 'Marketing')] -> Option D

4. Quick Check:

   RIGHT JOIN keeps all right rows, unmatched left = NULL [OK]

Hint: RIGHT JOIN shows all right rows, NULL for unmatched left [OK]

Common Mistakes:

• Expecting all employees instead of all departments
• Including unmatched left rows only
• Confusing LEFT JOIN with RIGHT JOIN results

4. Consider this query:

SELECT a.id, b.value FROM tableA a RIGHT JOIN tableB b ON a.id = b.id;

It returns no rows even though tableB has data. What is the most likely cause?

medium

A. The join condition is incorrect or mismatched column names.

B. RIGHT JOIN does not return any rows if left table is empty.

C. RIGHT JOIN requires WHERE clause to filter rows.

D. The query syntax is invalid and causes an error.

Solution

1. Step 1: Understand RIGHT JOIN behavior

   RIGHT JOIN returns all rows from right table even if left table has no matching rows.

2. Step 2: Analyze why no rows returned

   If no rows appear, likely the join condition a.id = b.id does not match any rows due to wrong column names or data mismatch.

3. Final Answer:

   The join condition is incorrect or mismatched column names. -> Option A

4. Quick Check:

   No rows means join condition mismatch [OK]

Hint: No rows? Check join condition and column names first [OK]

Common Mistakes:

• Assuming RIGHT JOIN needs WHERE clause
• Thinking RIGHT JOIN returns no rows if left table empty
• Believing syntax error causes no rows instead of error

5. You have two tables:

Orders
order_id | customer_id
1 | 101
2 | 102

Customers
customer_id | name
101 | John
103 | Alice

You want to list all customers and their orders if any. Which query using RIGHT JOIN gives the correct result?

hard

A. SELECT Customers.name, Orders.order_id FROM Customers RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

B. SELECT Customers.name, Orders.order_id FROM Orders RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id;

C. SELECT Customers.name, Orders.order_id FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

D. SELECT Customers.name, Orders.order_id FROM Customers LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

Solution

1. Step 1: Identify which table is right to keep all customers

   We want all customers, so Customers must be the right table in RIGHT JOIN.

2. Step 2: Check join condition and table order

   SELECT Customers.name, Orders.order_id FROM Orders RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id; uses Orders RIGHT JOIN Customers ON matching customer_id, so all Customers appear with matching Orders or NULL.

3. Final Answer:

   SELECT Customers.name, Orders.order_id FROM Orders RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id; -> Option B

4. Quick Check:

   RIGHT JOIN keeps all right table rows (Customers) [OK]

Hint: Put table with all rows as right table in RIGHT JOIN [OK]

Common Mistakes:

• Swapping table order and losing all customers
• Using LEFT JOIN instead of RIGHT JOIN incorrectly
• Confusing join condition direction