Multiple LEFT JOINs in one query in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of multiple LEFT JOINs with missing matches

Consider these tables:

Employees: (id, name)

Departments: (id, dept_name)

Projects: (id, project_name, dept_id)

Query:

SELECT e.name, d.dept_name, p.project_name
FROM Employees e
LEFT JOIN Departments d ON e.id = d.id
LEFT JOIN Projects p ON d.id = p.dept_id
ORDER BY e.id;

What is the output of this query?

A [{"name": "Alice", "dept_name": "HR", "project_name": "Recruitment"}, {"name": "Bob", "dept_name": null, "project_name": null}, {"name": "Charlie", "dept_name": "IT", "project_name": "Infrastructure"}]

B [{"name": "Alice", "dept_name": null, "project_name": null}, {"name": "Bob", "dept_name": null, "project_name": null}, {"name": "Charlie", "dept_name": null, "project_name": null}]

C [{"name": "Alice", "dept_name": "HR", "project_name": null}, {"name": "Bob", "dept_name": null, "project_name": null}, {"name": "Charlie", "dept_name": "IT", "project_name": null}]

D [{"name": "Alice", "dept_name": "HR", "project_name": "Recruitment"}, {"name": "Bob", "dept_name": "Finance", "project_name": "Budgeting"}, {"name": "Charlie", "dept_name": "IT", "project_name": "Infrastructure"}]

💡 Hint

Remember that LEFT JOIN keeps all rows from the left table even if there is no match on the right.

The first LEFT JOIN matches employees to departments by employee id. Bob has no matching department, so dept_name is null. The second LEFT JOIN matches departments to projects by dept_id. Projects exist only for HR and IT, so project_name is null if no project matches.

📝 Syntax

intermediate

Identify the syntax error in multiple LEFT JOINs

Which option contains a syntax error in this SQL query with multiple LEFT JOINs?

SELECT a.id, b.value, c.status
FROM TableA a
LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c ON a.id = c.a_id;

A

LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c ON a.id = c.a_id;

B

LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c ON a.id = c.a_id AND b.status = 'active';

C

LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c ON a.id = c.a_id

D

LEFT JOIN TableB b ON a.id = b.a_id
LEFT JOIN TableC c a.id = c.a_id;

💡 Hint

Look carefully at the ON clauses for each JOIN.

Option D is missing the ON keyword before the join condition for TableC, causing a syntax error.

❓ optimization

advanced

Optimizing multiple LEFT JOINs for performance

You have a query joining three large tables with LEFT JOINs. Which option is the best way to optimize the query?

A Add indexes on the columns used in the ON conditions for all LEFT JOINs.

B Replace all LEFT JOINs with INNER JOINs to reduce rows.

C Use SELECT * to avoid specifying columns and speed up the query.

D Remove all JOIN conditions to speed up the query.

💡 Hint

Think about how databases find matching rows efficiently.

Indexes on join columns help the database quickly find matching rows, improving performance. Replacing LEFT JOINs with INNER JOINs changes results. SELECT * can slow queries by fetching unnecessary data. Removing join conditions causes errors or huge result sets.

🔧 Debug

advanced

Why does this multiple LEFT JOIN query return duplicate rows?

Given these tables:

Authors: (id, name)

Books: (id, author_id, title)

Reviews: (id, book_id, rating)

Query:

SELECT a.name, b.title, r.rating
FROM Authors a
LEFT JOIN Books b ON a.id = b.author_id
LEFT JOIN Reviews r ON b.id = r.book_id;

Why might this query return duplicate rows for the same author and book?

A Because the ON conditions are incorrect and cause cross joins.

B Because LEFT JOINs always duplicate rows regardless of data.

C Because each book can have multiple reviews, the join duplicates rows for each review.

D Because the SELECT statement is missing DISTINCT keyword.

💡 Hint

Think about how many reviews a single book can have.

Each book may have many reviews, so joining Reviews duplicates the book row for each review. This is expected behavior with one-to-many relationships.

🧠 Conceptual

expert

Understanding NULLs in multiple LEFT JOINs

In a query with multiple LEFT JOINs, what does a NULL value in a column from a right table indicate?

A The right table column value is actually NULL in the database.

B There was no matching row in the right table for the left table row.

C The LEFT JOIN failed due to a syntax error.

D The left table row was filtered out by a WHERE clause.

💡 Hint

Think about what LEFT JOIN does when no match is found.

LEFT JOIN keeps all rows from the left table. If no matching row exists in the right table, columns from the right table are filled with NULLs.