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.