NULL and IS NULL

NULL means a missing or unknown value; it needs IS NULL, not =, and it propagates through expressions.

Syntax

expr IS NULL | expr IS NOT NULL
expr IS DISTINCT FROM expr | expr IS NOT DISTINCT FROM expr

NULL is not a value but the absence of one. SQL uses three-valued logic: a comparison is TRUE, FALSE or UNKNOWN (NULL).

Any comparison with NULL is UNKNOWN: NULL = NULL, NULL <> 1, NULL > 0 are all NULL. WHERE keeps only TRUE rows, so these rows disappear.
Test with IS NULL / IS NOT NULL.
a IS DISTINCT FROM b is a NULL-safe <>: it treats two NULLs as equal and NULL vs a value as different.
Arithmetic and string concatenation with NULL give NULL: price + NULL, 'a' || NULL. concat() ignores NULLs.
Logic: TRUE OR NULL is TRUE, FALSE AND NULL is FALSE; everything else involving NULL is NULL. NOT NULL is NULL.
Aggregates skip NULLs (SUM, AVG, COUNT(col)); COUNT(*) counts rows. AVG divides by the non-NULL count.
NOT IN (subquery) returns no rows at all if the subquery yields a NULL. Prefer NOT EXISTS.
Sorting: NULLs come last in ASC and first in DESC. Grouping: all NULLs form one group.

Examples

Find missing values starter

SELECT customer_id, customer_name
FROM customers
WHERE email IS NULL;

WHERE email = NULL would return nothing.

The <> trap shop

SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE city <> 'Riyadh') AS not_riyadh,
       count(*) FILTER (WHERE city IS DISTINCT FROM 'Riyadh') AS not_riyadh_null_safe
FROM customers;

The NULL-safe count is larger by the number of customers whose city is NULL.

Aggregates ignore NULL shop

SELECT count(*) AS returns,
       count(refund_amount) AS refunded,
       sum(refund_amount) AS total_refunded,
       avg(refund_amount) AS avg_of_refunded_only
FROM returns;

count(refund_amount) skips pending refunds (NULL); avg divides by that smaller count.

Pitfalls

WHERE col = NULL or col <> NULL (always UNKNOWN, so no rows).
col <> 'x' silently excluding NULL rows; add OR col IS NULL or use IS DISTINCT FROM.
NOT IN with a subquery that can return NULL (the result becomes empty).
Treating NULL as 0 in averages: AVG(col) ignores NULLs; AVG(COALESCE(col, 0)) counts them as zero. Decide which the question means.
Concatenating with || when a part can be NULL (the whole string becomes NULL); use concat() or COALESCE.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

Oracle: The empty string '' is treated as NULL, so WHERE col = '' never matches.
MySQL: NULL-safe equality is the <=> operator; IS DISTINCT FROM is not supported.
SQL Server: IS [NOT] DISTINCT FROM is supported since SQL Server 2022.