LEFT JOIN preserving all left rows in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEFT JOIN preserving all left rows

Given two tables, Employees and Departments, what is the output of the following SQL query?

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID
ORDER BY Employees.ID;

Assume the tables contain:
Employees:
ID | Name | DepartmentID
1 | Alice | 10
2 | Bob | 20
3 | Charlie | NULL

Departments:
ID | DepartmentName
10 | HR
20 | IT

SQL

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID
ORDER BY Employees.ID;

A [{"Name": "Alice", "DepartmentName": "HR"}, {"Name": "Bob", "DepartmentName": null}, {"Name": "Charlie", "DepartmentName": null}]

B [{"Name": "Alice", "DepartmentName": null}, {"Name": "Bob", "DepartmentName": null}, {"Name": "Charlie", "DepartmentName": null}]

C [{"Name": "Alice", "DepartmentName": "HR"}, {"Name": "Bob", "DepartmentName": "IT"}, {"Name": "Charlie", "DepartmentName": null}]

D [{"Name": "Alice", "DepartmentName": "HR"}, {"Name": "Bob", "DepartmentName": "IT"}]

💡 Hint

Remember, LEFT JOIN keeps all rows from the left table even if there is no matching row in the right table.

The LEFT JOIN returns all rows from Employees. For Alice and Bob, matching Departments exist, so DepartmentName is shown. For Charlie, DepartmentID is NULL, so DepartmentName is NULL.

🧠 Conceptual

intermediate

Understanding LEFT JOIN behavior with NULL keys

Which statement best describes what happens when a LEFT JOIN is performed and the join key in the left table is NULL?

A The row from the left table is included with NULLs for the right table columns.

B The row from the left table is excluded because NULL cannot match any value.

C The join returns an error due to NULL in the join key.

D The row from the left table is duplicated for every row in the right table.

💡 Hint

Think about how LEFT JOIN preserves rows from the left table regardless of matches.

LEFT JOIN keeps all rows from the left table. If the join key is NULL, no matching right row exists, so right columns are NULL but the left row is still included.

📝 Syntax

advanced

Identify the syntax error in LEFT JOIN query

Which option contains a syntax error in the LEFT JOIN SQL query?

SQL

SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.ID;

A SELECT e.Name, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.ID WHERE d.ID IS NOT NULL;

B SELECT e.Name, d.DepartmentName FROM Employees e LEFT JOIN Departments d ON e.DepartmentID = d.ID;

C ;DI.d = DItnemtrapeD.e NO d stnemtrapeD NIOJ TFEL e seeyolpmE MORF emaNtnemtrapeD.d ,emaN.e TCELES

D SELECT e.Name, d.DepartmentName FROM Employees e LEFT JOIN Departments d e.DepartmentID = d.ID;

💡 Hint

Check the ON clause syntax carefully.

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

❓ optimization

advanced

Optimizing LEFT JOIN for large tables

You have two large tables, Employees and Departments. Which approach optimizes the LEFT JOIN query performance best?

A Ensure indexes exist on Employees.DepartmentID and Departments.ID before running the LEFT JOIN.

B Use SELECT * to retrieve all columns to avoid specifying columns.

C Run the LEFT JOIN without any indexes and add indexes after query execution.

D Use a CROSS JOIN instead of LEFT JOIN for faster results.

💡 Hint

Indexes help the database find matching rows faster.

Indexes on join keys speed up matching rows in LEFT JOIN, improving query performance.

🔧 Debug

expert

Why does this LEFT JOIN query exclude some left rows?

Consider this query:

SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.ID
WHERE d.DepartmentName = 'HR';

Why does this query exclude employees without a department or with a department other than 'HR'?

A Because the ON clause is incorrect and filters rows before the join.

B Because the WHERE clause filters out rows where d.DepartmentName is NULL, removing unmatched left rows.

C Because LEFT JOIN automatically excludes rows without matches in the right table.

D Because the query syntax is invalid and causes an error.

💡 Hint

Think about how WHERE affects rows after the join.

The WHERE clause filters the result after the LEFT JOIN. Rows with no matching department have NULL in d.DepartmentName, so they are excluded by the condition.