Using = NULL Instead of IS NULL

-- Trying to find NULL foreign keys

SELECT * FROM orders WHERE customer_id = NULL;

-- Returns 0 rows!

-- Correct way to find NULL

SELECT * FROM orders WHERE customer_id IS NULL;

-- Returns orders with NULL customer_id

NULL = NULL returns NULL, not TRUE. The WHERE clause needs TRUE to include a row. Since NULL is treated as FALSE in boolean context, no rows match. Always use IS NULL to check for NULL values.

Remember: IS NULL for checking, = for comparing values. NULL is not a value, it's the absence of a value.

Critical

= NULL always returns NULL (treated as FALSE), so no rows match

Expecting NULL Foreign Keys in INNER JOIN Results

-- Expecting all orders including those with NULL customer_id

SELECT o.id, c.name

FROM orders o

INNER JOIN customers c ON o.customer_id = c.id;

-- Orders with NULL customer_id silently excluded!

-- Use LEFT JOIN to include NULL foreign keys

SELECT o.id, c.name

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

-- All orders included, c.name is NULL for orders with NULL customer_id

INNER JOIN only returns rows where join condition is TRUE. Since NULL = anything returns NULL (not TRUE), rows with NULL foreign keys are excluded. This causes silent data loss in reports. Use LEFT JOIN when NULL foreign keys should appear.

Ask: "Should rows with NULL foreign keys appear?" Yes = LEFT JOIN. No = INNER JOIN.

Critical

Missing data in report - orders with NULL customer_id don't appear

Not Distinguishing NULL from Invalid Foreign Key

-- Both NULL and invalid foreign keys show NULL customer_name

SELECT o.id, o.customer_id, c.name

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id;

-- Can't tell if customer_id is NULL or just invalid

-- Distinguish the two cases

SELECT

o.id,

o.customer_id,

c.name,

CASE

WHEN o.customer_id IS NULL THEN 'No customer'

WHEN c.id IS NULL THEN 'Invalid customer'