LEFT JOIN vs RIGHT JOIN decision in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Choosing between LEFT JOIN and RIGHT JOIN

You have two tables: Employees and Departments. You want to list all employees and their departments, including employees who are not assigned to any department.

Which JOIN type should you use?

A INNER JOIN Employees to Departments

B LEFT JOIN Employees to Departments

C FULL OUTER JOIN Employees to Departments

D RIGHT JOIN Employees to Departments

💡 Hint

Think about which table you want to keep all rows from.

LEFT JOIN keeps all rows from the left table (Employees) and matches rows from the right table (Departments). This shows all employees, even those without a department.

❓ query_result

intermediate

Result of LEFT JOIN vs RIGHT JOIN

Given these tables:

Employees: (id, name)
1, Alice
2, Bob
3, Carol

Departments: (id, dept_name)
2, Sales
3, HR
4, IT

What is the result of this query?

SELECT Employees.name, Departments.dept_name
FROM Employees
LEFT JOIN Departments ON Employees.id = Departments.id;

A [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

D [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

💡 Hint

LEFT JOIN keeps all rows from Employees and matches Departments where IDs are equal.

Alice has no matching department so dept_name is null. Bob and Carol match Sales and HR respectively. IT department is not included because no employee has id 4.

📝 Syntax

advanced

Identify the syntax error in RIGHT JOIN query

Which option contains a syntax error in this RIGHT JOIN query?

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.id = d.id;

A ;di.d = di.e NO d stnemtrapeD NIOJ THGIR e seeyolpmE MORF eman_tped.d ,eman.e TCELES

B SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.id = d.id;

C SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d WHERE e.id = d.id;

D SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.id = d.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 a query with LEFT JOIN vs RIGHT JOIN

You want to optimize a query that lists all departments and their employees, including departments with no employees. Which join is generally more efficient?

A LEFT JOIN Departments to Employees

B INNER JOIN Departments to Employees

C RIGHT JOIN Employees to Departments

D FULL OUTER JOIN Departments to Employees

💡 Hint

Think about which table you want to keep all rows from and typical indexing.

LEFT JOIN starting from Departments keeps all departments and matches employees. It is usually more efficient because the driving table is the one with fewer rows or the one you want to keep all rows from.

🔧 Debug

expert

Debugging unexpected NULLs in RIGHT JOIN result

You run this query:

SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.id = d.id;

But you see NULLs in e.name for some rows unexpectedly. What is the most likely cause?

A There are departments with no matching employee IDs

B The JOIN condition is incorrect and should be e.dept_id = d.id

C RIGHT JOIN always produces NULLs in the left table columns

D Employees table has NULL values in the id column

💡 Hint

Think about what RIGHT JOIN does and when NULLs appear.

RIGHT JOIN keeps all rows from Departments. If a department has no matching employee, employee columns show NULL.