LEFT JOIN with NULL result rows in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of LEFT JOIN with unmatched rows

Consider two tables: Employees and Departments.

Employees has columns EmpID, Name, and DeptID. Departments has columns DeptID and DeptName.

What is the output of this query?

SELECT e.EmpID, e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID
ORDER BY e.EmpID;

SQL

CREATE TABLE Employees (EmpID INT, Name VARCHAR(20), DeptID INT);
CREATE TABLE Departments (DeptID INT, DeptName VARCHAR(20));

INSERT INTO Employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', NULL), (4, 'Diana', 30);
INSERT INTO Departments VALUES (10, 'HR'), (20, 'Finance');

A

1, Alice, HR
2, Bob, Finance
3, Charlie, NULL
4, Diana, NULL

B

1, Alice, HR
2, Bob, Finance
3, Charlie, NULL

C

1, Alice, HR
2, Bob, Finance
4, Diana, NULL

D

1, Alice, HR
2, Bob, Finance
3, Charlie, NULL
4, Diana, 30

💡 Hint

LEFT JOIN returns all rows from the left table, even if there is no matching row in the right table. If no match, columns from the right table are NULL.

Rows from Employees are all included. For DeptID 10 and 20, matching DeptName is shown. For DeptID NULL or 30 (no match), DeptName is NULL.

🧠 Conceptual

intermediate

Understanding NULLs in LEFT JOIN results

Why do some columns from the right table show NULL in a LEFT JOIN result?

A Because there is no matching row in the right table for the left table row

B Because the left table columns are NULL and cause right table columns to be NULL

C Because the right table has NULL values in those columns for matching rows

D Because the LEFT JOIN filters out rows with non-NULL values in the right table

💡 Hint

Think about what LEFT JOIN means: all rows from the left table, matched rows from the right table or NULL if no match.

When a row in the left table has no matching row in the right table, the columns from the right table appear as NULL in the result.

📝 Syntax

advanced

Identify the syntax error in LEFT JOIN query

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

SELECT e.EmpID, e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID
WHERE d.DeptName IS NULL;

A SELECT e.EmpID, e.Name, d.DeptName FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.DeptID HAVING d.DeptName IS NULL;

B SELECT e.EmpID, e.Name, d.DeptName FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.DeptID WHERE IS NULL d.DeptName;

C SELECT e.EmpID, e.Name, d.DeptName FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.DeptID WHERE d.DeptName IS NULL;

D SELECT e.EmpID, e.Name, d.DeptName FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.DeptID WHERE d.DeptName = NULL;

💡 Hint

Check the syntax of the WHERE clause and NULL comparisons.

Option B has incorrect syntax: WHERE IS NULL d.DeptName is invalid SQL syntax.

Option B uses = NULL which is syntactically valid but logically incorrect (it returns no rows), not a syntax error.

Option B uses HAVING which is valid syntax but not appropriate here.

❓ optimization

advanced

Optimizing LEFT JOIN with NULL filtering

You want to find all employees without a department assigned. Which query is more efficient?

A SELECT e.EmpID, e.Name FROM Employees e LEFT JOIN Departments d ON e.DeptID = d.DeptID WHERE d.DeptID IS NULL;

B SELECT e.EmpID, e.Name FROM Employees e WHERE e.DeptID IS NULL;

C SELECT e.EmpID, e.Name FROM Employees e WHERE NOT EXISTS (SELECT 1 FROM Departments d WHERE d.DeptID = e.DeptID);

D SELECT e.EmpID, e.Name FROM Employees e INNER JOIN Departments d ON e.DeptID = d.DeptID WHERE d.DeptID IS NULL;

💡 Hint

Think about how to efficiently find rows in one table that have no matching rows in another.

Option C uses NOT EXISTS which is often more efficient than LEFT JOIN with NULL filtering.

Option C uses LEFT JOIN and filters NULLs but can be slower on large data.

Option C only finds employees with NULL DeptID, missing those with invalid DeptID values.

Option C is invalid because INNER JOIN never returns NULLs.

🔧 Debug

expert

Debugging unexpected NULLs in LEFT JOIN result

You run this query:

SELECT e.EmpID, e.Name, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID
WHERE d.DeptName IS NULL;

But you notice some employees with valid departments are missing. What is the most likely cause?

A The LEFT JOIN is filtering out employees with NULL <code>DeptID</code>

B The query should use INNER JOIN instead of LEFT JOIN

C The WHERE clause should be <code>WHERE e.DeptID IS NULL</code> instead

D Some <code>DeptID</code> values in <code>Employees</code> do not exactly match <code>DeptID</code> in <code>Departments</code> due to data type mismatch or trailing spaces

💡 Hint

Check data types and values used in the JOIN condition.

If DeptID values do not match exactly (e.g., one is string, other is int, or trailing spaces), the JOIN fails and rows appear with NULL DeptName.

This causes employees with valid departments to appear as unmatched.