Why does NULL = NULL return NULL instead of TRUE? How does this affect joins?

NULL represents "unknown" or "missing value". When you compare two unknowns, the result is also unknown (NULL), not TRUE or FALSE.

NULL = NULL → NULL (unknown = unknown → unknown)

In joins, the ON condition must be TRUE to match rows. Since NULL = NULL returns NULL (treated as FALSE), rows with NULL foreign keys never match anything - not even other NULLs.

Example:

Order with customer_id = NULL

Customer with id = NULL

These don't match because NULL = NULL is NULL, not TRUE.

This is why INNER JOIN excludes rows with NULL foreign keys - they cannot match.

What is the difference between INNER JOIN and LEFT JOIN when dealing with NULL foreign keys?

INNER JOIN excludes rows with NULL foreign keys:

SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id;

- Orders with NULL customer_id: Excluded

- Orders with valid customer_id: Included

LEFT JOIN preserves rows with NULL foreign keys:

SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

- Orders with NULL customer_id: Included (customer columns NULL)

- Orders with valid customer_id: Included (customer columns populated)

Use INNER JOIN when: NULL foreign keys are invalid (data quality issue)

Use LEFT JOIN when: NULL foreign keys are valid (optional relationship)

How do you find rows with NULL foreign keys? Why doesn't = NULL work?

Use IS NULL, not = NULL:

WRONG:

SELECT * FROM orders WHERE customer_id = NULL;

Returns 0 rows because NULL = NULL returns NULL (not TRUE)

CORRECT:

SELECT * FROM orders WHERE customer_id IS NULL;

Returns orders with NULL customer_id

Why = NULL doesn't work:

- NULL = NULL returns NULL

- WHERE clause needs TRUE to include row

- NULL is treated as FALSE in boolean context

- So no rows match

IS NULL is a special operator that returns TRUE when value is NULL.

How do you distinguish between NULL foreign key and invalid foreign key in a LEFT JOIN?

Use CASE statement with two checks:

SELECT

o.id,

o.customer_id,

c.name,