NULL in SQL: why your WHERE clause silently drops rows

= NULL never matches

status = NULL returns unknown for every row, so the query returns nothing. Use IS NULL and IS NOT NULL — they are the only operators that test for it.

-- returns no rows
WHERE status = NULL

-- correct
WHERE status IS NULL

NOT IN with a NULL in the list returns nothing

If the subquery behind NOT IN can produce a NULL, the whole condition becomes unknown and you get an empty result. NOT EXISTS is immune, which is why it is the safer default.

-- risky
WHERE id NOT IN (SELECT customer_id FROM orders)

-- safe
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = customers.id
)

Aggregates skip NULL — except COUNT(*)

AVG(score) ignores NULL scores, so the average is over the rows that have a value, not over all rows. If you want missing scores treated as zero, say so with COALESCE. SUM() of an all-NULL set returns NULL, not 0 — wrap it if a number is required.

SELECT AVG(COALESCE(score, 0)) AS avg_including_missing,
       COALESCE(SUM(total), 0) AS total_spent
FROM results;

Displaying a fallback

COALESCE(value, fallback) is standard everywhere. MySQL also has IFNULL, SQL Server has ISNULL — COALESCE is the portable choice, and it accepts more than two arguments so you can chain fallbacks.

Finally, remember that a NULL and an empty string are different values. If your data mixes both, filter with (col IS NULL OR col = '') or clean the column up.