NULLs in JOIN conditions in SQL - Step-by-Step Execution

Concept Flow - NULLs in JOIN conditions

Start with two tables

↓

Perform JOIN

↓

Compare join keys

↓

Check if keys are NULL

Yes No↓

No match

↓

Return joined rows or NULLs

↓

End

This flow shows how SQL JOIN compares keys, treats NULLs as unknown, and only matches rows when keys are equal and not NULL.

Execution Sample

SQL

SELECT A.id, B.id
FROM A
LEFT JOIN B ON A.id = B.id;

This query joins tables A and B on the id column, showing how NULLs affect matching.

Execution Table

Step	A.id	B.id	Join Condition (A.id = B.id)	Match Result	Output Row
1	1	1	1 = 1 (True)	Match	(1, 1)
2	2	NULL	2 = NULL (Unknown)	No Match	(2, NULL) from A with NULL in B columns
3	NULL	2	NULL = 2 (Unknown)	No Match	(NULL, NULL) from A with NULL in B columns
4	NULL	NULL	NULL = NULL (Unknown)	No Match	(NULL, NULL) from A with NULL in B columns
5	3	3	3 = 3 (True)	Match	(3, 3)

💡 All rows processed; NULL comparisons never match, so LEFT JOIN returns NULLs for B columns when no match.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
A.id	1	1	2	NULL	NULL	3	3
B.id	1	1	NULL	2	NULL	3	3
Join Condition Result	N/A	True	Unknown	Unknown	Unknown	True	True
Match Result	N/A	Match	No Match	No Match	No Match	Match	Match
Output Row	N/A	(1,1)	(2,NULL)	(NULL,NULL)	(NULL,NULL)	(3,3)	(3,3)

Key Moments - 2 Insights

Why does a join condition comparing NULL to any value not result in a match?

Because in SQL, NULL means unknown, so any comparison with NULL returns unknown (not true). The join only matches when the condition is true, so rows with NULL keys do not match (see execution_table rows 2-4).

Why does a LEFT JOIN still return rows from the left table even when there is no match?

LEFT JOIN returns all rows from the left table. When no match is found on the right, it fills right table columns with NULLs. This is shown in execution_table rows 2-4 where B columns are NULL.

Visual Quiz - 3 Questions

Look at the execution table, what is the match result when A.id is 2 and B.id is NULL?

A No Match

B Match

C Error

D Unknown but treated as Match

💡 Hint

Check row 2 in the execution_table under 'Match Result' column.

At which step does the join condition evaluate to true?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Join Condition (A.id = B.id)' column in the execution_table.

If the join was INNER JOIN instead of LEFT JOIN, what would happen to rows where A.id is 2 and B.id is NULL?

A They would appear with NULLs for B columns

B They would be excluded from the output

C They would cause an error

D They would match anyway

💡 Hint

INNER JOIN only returns rows where join condition is true; see execution_table rows 2-4 for no matches.

Concept Snapshot

SQL JOIN compares keys to match rows.
NULL means unknown, so NULL = value is never true.
JOIN matches only when condition is true.
LEFT JOIN returns all left rows, filling NULLs for no matches.
NULLs in join keys cause no matches, not errors.

Full Transcript

This visual execution shows how SQL JOIN handles NULLs in join conditions. We start with two tables A and B, each having some NULL values in the join key column 'id'. The JOIN compares A.id and B.id for equality. When either side is NULL, the comparison returns unknown, so no match occurs. For example, when A.id=2 and B.id=NULL, the join condition is unknown, so no match. LEFT JOIN returns all rows from A, and for no matches, fills B columns with NULLs. Only rows where both keys are equal and not NULL match. This explains why NULLs in join keys prevent matching rows in SQL JOINs.