TopolearnMisunderstanding NULL is the single most common source of silently wrong SQL results.
NULL is not zero, not an empty string, and not a value at all: it is a marker for unknown or missing data. Dev's city is NULL because we do not know it.
Any comparison with an unknown value produces an unknown result. NULL = NULL is not true; it is NULL. So SQL uses three-valued logic: conditions evaluate to true, false or unknown, and WHERE keeps only rows where the condition is true. That has consequences:
WHERE city = NULLmatches nothing. UseWHERE city IS NULL/IS NOT NULL.WHERE city <> 'London'does not return Dev, becauseNULL <> 'London'is unknown.- Arithmetic with NULL yields NULL:
price + NULLis NULL. So does'a' || NULLin PostgreSQL (Oracle treats the empty string as NULL, a notorious difference).
The nastiest trap is NOT IN with a NULL in the list. x NOT IN (1, NULL) means x <> 1 AND x <> NULL; the second part is unknown, so the whole thing can never be true and the query returns no rows. This happens most often with a subquery: WHERE id NOT IN (SELECT customer_id FROM ...) breaks as soon as one customer_id is NULL. NOT EXISTS, covered later, does not have this problem.
When you do want "equal, treating NULLs as equal", PostgreSQL and the standard offer IS NOT DISTINCT FROM: a IS NOT DISTINCT FROM b is true when both are NULL. To substitute a default for NULL, use COALESCE(city, 'unknown'), which returns its first non-NULL argument.
Decide deliberately whether a column may be NULL. If a value is always required, declare the column NOT NULL and the whole class of problems disappears.
Example
SELECT name FROM customers WHERE city = NULL; -- 0 rows!
SELECT name FROM customers WHERE city IS NULL; -- Dev
SELECT name FROM customers WHERE city <> 'London';
-- Ana <- Dev is missing: NULL <> 'London' is unknown
SELECT name FROM customers
WHERE city IS DISTINCT FROM 'London';
-- Ana
-- Dev
SELECT name, COALESCE(city, 'unknown') AS city FROM customers;
-- Ana | Yerevan
-- Ben | London
-- Chloe | London
-- Dev | unknown
SELECT 1 WHERE 3 NOT IN (1, 2, NULL); -- 0 rows!Remember
- NULL means unknown; comparing with it yields unknown, not true or false
- Test with IS NULL / IS NOT NULL, never = NULL
- NOT IN with any NULL in the list returns no rows
- COALESCE supplies a fallback for NULL values
Watch out for
- Expecting WHERE col <> 'x' to include rows where col is NULL.