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.

Practice

1. Which JOIN type keeps all rows from the first (left) table, even if there is no matching row in the second (right) table?

easy

A. RIGHT JOIN

B. INNER JOIN

C. LEFT JOIN

D. FULL JOIN

Solution

1. Step 1: Understand LEFT JOIN behavior

   LEFT JOIN returns all rows from the first table and matching rows from the second table. If no match, NULLs appear for second table columns.

2. Step 2: Compare with RIGHT JOIN

   RIGHT JOIN keeps all rows from the second table, not the first. INNER JOIN only returns matching rows. FULL JOIN returns all rows from both tables.

3. Final Answer:

   LEFT JOIN -> Option C

4. Quick Check:

   LEFT JOIN keeps all left table rows [OK]

Hint: LEFT JOIN keeps all from first table, RIGHT JOIN from second [OK]

Common Mistakes:

• Confusing LEFT JOIN with RIGHT JOIN
• Thinking INNER JOIN keeps unmatched rows
• Assuming FULL JOIN keeps only one table's rows

2. Which of the following is the correct syntax to perform a RIGHT JOIN between tables Employees and Departments on Employees.DeptID = Departments.ID?

easy

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

B. SELECT * FROM Employees LEFT JOIN Departments ON Employees.DeptID = Departments.ID;

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

D. SELECT * FROM Employees JOIN Departments ON Employees.DeptID = Departments.ID;

Solution

1. Step 1: Check JOIN syntax correctness

   RIGHT JOIN requires ON clause to specify join condition. SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DeptID = Departments.ID; uses correct syntax with ON clause.

2. Step 2: Identify errors in other options

   SELECT * FROM Employees LEFT JOIN Departments ON Employees.DeptID = Departments.ID; uses LEFT JOIN, not RIGHT JOIN. SELECT * FROM Employees RIGHT JOIN Departments WHERE Employees.DeptID = Departments.ID; uses WHERE instead of ON, which is incorrect for JOIN condition. SELECT * FROM Employees JOIN Departments ON Employees.DeptID = Departments.ID; uses JOIN without specifying LEFT or RIGHT, defaults to INNER JOIN.

3. Final Answer:

   SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DeptID = Departments.ID; -> Option A

4. Quick Check:

   RIGHT JOIN needs ON clause [OK]

Hint: RIGHT JOIN syntax requires ON, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Confusing LEFT JOIN with RIGHT JOIN syntax
• Omitting JOIN type defaults to INNER JOIN

3. Given tables Authors and Books, what will be the result of this query?

SELECT Authors.Name, Books.Title FROM Authors LEFT JOIN Books ON Authors.ID = Books.AuthorID;

Assuming Authors has 3 rows and Books has 2 rows, with one author having no books, what will the output include?

medium

A. Only authors who have books, excluding authors without books

B. All 3 authors with their book titles; NULL for authors without books

C. All books with their authors; NULL for books without authors

D. All authors and all books, matched and unmatched

Solution

1. Step 1: Understand LEFT JOIN output

   LEFT JOIN keeps all rows from Authors (left table). For authors without matching books, Books.Title will be NULL.

2. Step 2: Analyze given data

   Authors has 3 rows, Books has 2 rows. One author has no books, so that author appears with NULL for book title.

3. Final Answer:

   All 3 authors with their book titles; NULL for authors without books -> Option B

4. Quick Check:

   LEFT JOIN keeps all left rows, fills NULLs for no match [OK]

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

Common Mistakes:

• Thinking unmatched left rows are excluded
• Confusing LEFT JOIN with INNER JOIN
• Assuming unmatched right rows appear in LEFT JOIN

4. You wrote this query but it returns fewer rows than expected:

SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.ID;

What is the likely mistake?

medium

A. Using ON instead of WHERE for join condition

B. Missing WHERE clause to filter rows

C. Not specifying table aliases

D. Using RIGHT JOIN instead of LEFT JOIN when Orders is the main table

Solution

1. Step 1: Identify which table's rows to keep

   If Orders is the main table and you want all its rows, LEFT JOIN should be used, not RIGHT JOIN.

2. Step 2: Understand RIGHT JOIN effect

   RIGHT JOIN keeps all rows from Customers (right table), so if Orders has rows without matching Customers, those rows are excluded.

3. Final Answer:

   Using RIGHT JOIN instead of LEFT JOIN when Orders is the main table -> Option D

4. Quick Check:

   Use LEFT JOIN to keep all left table rows [OK]

Hint: Use LEFT JOIN to keep all rows from first table [OK]

Common Mistakes:

• Confusing LEFT and RIGHT JOIN roles
• Expecting RIGHT JOIN to keep left table rows
• Ignoring join direction impact on row count

5. You have two tables: Students (left) and Enrollments (right). You want a list of all students, including those not enrolled in any course, showing NULL for missing enrollment data.
Which query is best and why?

Option A: SELECT * FROM Students LEFT JOIN Enrollments ON Students.ID = Enrollments.StudentID;
Option B: SELECT * FROM Enrollments RIGHT JOIN Students ON Students.ID = Enrollments.StudentID;

hard

A. Option A, because LEFT JOIN keeps all students and matches enrollments

B. Option B, because RIGHT JOIN keeps all enrollments and matches students

C. Option A, but it excludes students without enrollments

D. Option B, but it excludes students without enrollments

Solution

1. Step 1: Identify which table's rows to keep

   You want all students, even those without enrollments, so keep all rows from Students (left table).

2. Step 2: Choose JOIN type accordingly

   LEFT JOIN keeps all rows from Students and matches enrollments or shows NULL if none. RIGHT JOIN would keep all enrollments, not all students.

3. Final Answer:

   Option A, because LEFT JOIN keeps all students and matches enrollments -> Option A

4. Quick Check:

   LEFT JOIN keeps all left table rows [OK]

Hint: Keep main table first, use LEFT JOIN to keep all its rows [OK]

Common Mistakes:

• Using RIGHT JOIN but expecting all left table rows
• Switching table order without adjusting JOIN type
• Assuming RIGHT JOIN keeps left table rows