Finding unmatched rows with LEFT JOIN in SQL - Step-by-Step Execution

Concept Flow - Finding unmatched rows with LEFT JOIN

Start with LEFT JOIN

↓

Match rows from left table

↓

Check if right table row exists

Yes No↓

Keep combined row

↓

Result set

LEFT JOIN keeps all rows from the left table and matches rows from the right table. If no match, right side columns are NULL.

Execution Sample

SQL

SELECT A.id, B.id
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.a_id IS NULL;

This query finds rows in table A that have no matching row in table B.

Execution Table

Step	A.id	B.a_id	B.id	Join Condition (A.id = B.a_id)	Action	Output Row Included?
1	1	1	10	1=1 True	Match found, combine rows	Yes
2	2	NULL	NULL	No matching B row	No match, keep A row with NULLs	Yes
3	3	3	30	3=3 True	Match found, combine rows	Yes
4	4	NULL	NULL	No matching B row	No match, keep A row with NULLs	Yes
5	Filter WHERE B.a_id IS NULL				Keep only rows where B.a_id is NULL	Rows with A.id 2 and 4

💡 All rows from A processed; final filter keeps only unmatched rows where B.a_id is NULL

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
A.id	N/A	1	2	3	4	N/A
B.a_id	N/A	1	NULL	3	NULL	Filtered NULL only
Output Rows	Empty	Row with A.id=1 and B.id=10	Row with A.id=2 and B.id=NULL	Row with A.id=3 and B.id=30	Row with A.id=4 and B.id=NULL	Rows with A.id=2 and 4 only

Key Moments - 2 Insights

Why do some rows have NULL values in the right table columns after LEFT JOIN?

Because those left table rows have no matching row in the right table, so SQL fills right side columns with NULLs (see execution_table rows 2 and 4).

Why do we use WHERE B.a_id IS NULL to find unmatched rows?

Because unmatched rows have NULL in right table columns, filtering on B.a_id IS NULL keeps only those unmatched rows (see execution_table step 5).

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of B.a_id at step 2?

A 1

B NULL

C 2

D 10

💡 Hint

Check the 'B.a_id' column in execution_table row 2

At which step does the join condition fail, resulting in NULLs for right table columns?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look for 'No matching B row' in the 'Action' column of execution_table

If we remove the WHERE clause filtering B.a_id IS NULL, what rows will the output include?

A All rows from A with matched or unmatched B rows

B Only rows without matches in B

C Only rows with matches in B

D No rows at all

💡 Hint

LEFT JOIN keeps all rows from A regardless of matches; WHERE clause filters unmatched rows

Concept Snapshot

LEFT JOIN returns all rows from the left table.
If no match in right table, right columns are NULL.
Use WHERE right_table.key IS NULL to find unmatched rows.
This helps find rows in left table without matches in right table.

Full Transcript

This visual execution shows how LEFT JOIN works to find unmatched rows. We start by joining tables A and B on matching IDs. For each row in A, SQL tries to find matching rows in B. If a match exists, it combines the rows. If not, it keeps the A row and fills B columns with NULL. Then, filtering WHERE B.a_id IS NULL keeps only those unmatched rows from A. The execution table traces each step, showing variable values and actions. This helps beginners see why NULLs appear and how filtering finds unmatched rows.