RIGHT JOIN execution behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of RIGHT JOIN with matching rows

Given two tables Employees and Departments:

Employees:
id | name | dept_id
1  | Alice | 10
2  | Bob   | 20
3  | Carol | 30

Departments:
dept_id | dept_name
10      | HR
20      | IT
30      | Finance

What is the output of this query?

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

SQL

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

A

name | dept_name
Alice | HR
Bob   | IT
Carol | Finance

B

name | dept_name
Alice | HR
Bob   | IT

C

name | dept_name
Alice | HR
Bob   | IT
Carol | Finance
NULL  | NULL

D

name | dept_name
NULL  | HR
NULL  | IT
NULL  | Finance

💡 Hint

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

Since all department IDs in Employees match Departments, the RIGHT JOIN returns all departments with their employee names.

❓ query_result

intermediate

RIGHT JOIN with unmatched rows in right table

Consider the same tables as before but with an extra department:

Departments:
dept_id | dept_name
10      | HR
20      | IT
30      | Finance
40      | Marketing

What is the output of this query?

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

SQL

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;

A

name  | dept_name
Alice | HR
Bob   | IT
Carol | Finance
NULL  | Marketing

B

name  | dept_name
Alice | HR
Bob   | IT
Carol | Finance

C

name  | dept_name
NULL  | HR
NULL  | IT
NULL  | Finance
NULL  | Marketing

D

name  | dept_name
Alice | HR
Bob   | IT
Carol | Finance
Marketing | NULL

💡 Hint

RIGHT JOIN includes all rows from the right table, even if no match exists in the left table.

The Marketing department has no matching employee, so employee name is NULL for that row.

📝 Syntax

advanced

Identify the syntax error in RIGHT JOIN query

Which of the following RIGHT JOIN queries will cause a syntax error?

A SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

B SELECT * FROM A RIGHT JOIN B ON A.id = B.id LIMIT 5;

C SELECT * FROM A RIGHT JOIN B WHERE A.id = B.id;

D SELECT * FROM A RIGHT JOIN B ON A.id = B.id ORDER BY B.id;

💡 Hint

Check the placement of the ON clause in JOIN syntax.

Option C uses WHERE instead of ON for the join condition, causing a syntax error.

❓ optimization

advanced

Optimizing RIGHT JOIN with large tables

You have two large tables: Orders (millions of rows) and Customers (thousands of rows). You want to get all customers and their orders if any.

Which query is likely to perform best?

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

B SELECT c.name, o.order_id FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id;

C SELECT c.name, o.order_id FROM Orders o INNER JOIN Customers c ON o.customer_id = c.id;

D SELECT c.name, o.order_id FROM Customers c FULL JOIN Orders o ON o.customer_id = c.id;

💡 Hint

Consider which table is smaller and how join direction affects performance.

LEFT JOIN starting from the smaller Customers table is more efficient than RIGHT JOIN starting from large Orders.

🧠 Conceptual

expert

Effect of RIGHT JOIN on NULL values in result

Given tables Products and Sales, a RIGHT JOIN is performed:

SELECT p.product_name, s.sale_date
FROM Products p
RIGHT JOIN Sales s ON p.product_id = s.product_id;

Which statement about the NULL values in the output is true?

A No NULLs appear because RIGHT JOIN always matches all rows.

B NULLs appear only in sale_date when a product exists without a matching sale.

C NULLs appear in both columns when no matching rows exist in either table.

D NULLs appear only in product_name when a sale exists without a matching product.

💡 Hint

Think about which table is on the right and how RIGHT JOIN works.

RIGHT JOIN returns all rows from Sales (right table). If a sale has no matching product, product_name is NULL.

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 RIGHT JOIN behavior

   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: Remember: RIGHT JOIN keeps all right table rows [OK]

Common Mistakes:

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

2. Which of the following is the correct syntax for a RIGHT JOIN between tables Employees and Departments on DepartmentID?

easy

A. SELECT * FROM Employees RIGHT Departments JOIN ON Employees.DepartmentID = Departments.DepartmentID;

B. SELECT * FROM Employees JOIN Departments RIGHT ON Employees.DepartmentID = Departments.DepartmentID;

C. SELECT * FROM Employees RIGHT JOIN Departments WHERE Employees.DepartmentID = Departments.DepartmentID;

D. SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Solution

1. Step 1: Identify correct JOIN syntax

   The correct syntax is: FROM left_table RIGHT JOIN right_table ON condition.

2. Step 2: Match the syntax with given options

   SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; correctly uses RIGHT JOIN with ON clause and proper table order.

3. Final Answer:

   SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; -> Option D

4. Quick Check:

   RIGHT JOIN syntax = FROM left RIGHT JOIN right ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left RIGHT JOIN right ON condition [OK]

Common Mistakes:

• Placing RIGHT keyword after JOIN
• Using WHERE instead of ON for join condition
• Incorrect table order in JOIN

3. Given tables:
Employees:
ID | Name | DeptID
1 | Alice | 10
2 | Bob | 20
3 | Carol | NULL

Departments:
DeptID | DeptName
10 | Sales
20 | Marketing
30 | HR

What is the result of:
SELECT Employees.Name, Departments.DeptName FROM Employees RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;?

medium

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

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

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

D. [('Alice', 'Sales'), ('Bob', 'Marketing'), ('Carol', 'HR')]

Solution

1. Step 1: Identify RIGHT JOIN effect on rows

   RIGHT JOIN keeps all Departments rows (right table), matching Employees rows or NULL if no match.

2. Step 2: Match Employees to Departments by DeptID

   DeptID 10 matches Alice, 20 matches Bob, 30 has no employee so NULL for Name.

3. Final Answer:

   [('Alice', 'Sales'), ('Bob', 'Marketing'), (NULL, 'HR')] -> Option A

4. Quick Check:

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

Hint: RIGHT JOIN keeps all right rows, unmatched left columns NULL [OK]

Common Mistakes:

• Ignoring unmatched right table rows
• Assuming unmatched left rows appear
• Mixing up NULL placement

4. Consider this SQL query:
SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.ID;
It returns fewer rows than expected. What is a likely cause?

medium

A. RIGHT JOIN always returns fewer rows than LEFT JOIN.

B. Orders table is empty, so no rows are returned.

C. The JOIN condition uses wrong column names causing no matches.

D. RIGHT JOIN syntax requires WHERE instead of ON clause.

Solution

1. Step 1: Analyze JOIN condition correctness

   If column names in ON clause are wrong, no matches occur, reducing rows.

2. Step 2: Understand RIGHT JOIN behavior with no matches

   RIGHT JOIN still returns all right table rows, but if condition is wrong, matches fail and left columns are NULL.

3. Final Answer:

   The JOIN condition uses wrong column names causing no matches. -> Option C

4. Quick Check:

   Wrong ON columns cause fewer matches [OK]

Hint: Check ON clause column names carefully [OK]

Common Mistakes:

• Assuming RIGHT JOIN returns fewer rows by default
• Confusing ON and WHERE clauses
• Ignoring empty tables impact

5. You have two tables:
Products:
ProductID | Name
1 | Pen
2 | Pencil
3 | Eraser

Sales:
SaleID | ProductID | Quantity
101 | 1 | 10
102 | 2 | 5

You want a report showing all products and their sold quantities, including products with no sales (show quantity as 0). Which query correctly uses RIGHT JOIN and handles missing sales?

hard

A. SELECT Products.Name, Sales.Quantity FROM Products RIGHT JOIN Sales ON Products.ProductID = Sales.ProductID;

B. SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Sales RIGHT JOIN Products ON Sales.ProductID = Products.ProductID;

C. SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Products LEFT JOIN Sales ON Products.ProductID = Sales.ProductID;

D. SELECT Products.Name, Sales.Quantity FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;

Solution

1. Step 1: Identify which table is right and which is left

   Products is the right table to keep all products; Sales is left table.

2. Step 2: Use RIGHT JOIN from Sales to Products and handle NULLs

   RIGHT JOIN keeps all Products rows; COALESCE replaces NULL sales quantity with 0.

3. Final Answer:

   SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Sales RIGHT JOIN Products ON Sales.ProductID = Products.ProductID; -> Option B

4. Quick Check:

   RIGHT JOIN keeps all right rows; COALESCE handles NULLs [OK]

Hint: Use COALESCE to replace NULLs after RIGHT JOIN [OK]

Common Mistakes:

• Using LEFT JOIN instead of RIGHT JOIN
• Not handling NULL sales quantities
• Swapping table order in JOIN