Handle NULLs — COALESCE, NULLIF, and IS DISTINCT FROM

COALESCE and NULLIF are ANSI-standard and behave identically in all six dialects.

-- COALESCE: first non-NULL value, useful for defaults
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

-- NULLIF: returns NULL if the two arguments are equal — classic use is a safe divisor
SELECT revenue / NULLIF(unit_count, 0) AS revenue_per_unit
FROM sales;
-- unit_count = 0  ->  NULLIF returns NULL  ->  the division returns NULL, not a divide-by-zero error

Plain = and <> never return true when either side is NULL — NULL = NULL is NULL (unknown), not true. When you need NULL to count as a comparable value (e.g. “did this column actually change?”), use IS [NOT] DISTINCT FROM:

-- PostgreSQL, SQLite 3.39+, DuckDB, BigQuery, Snowflake
SELECT * FROM t WHERE status IS DISTINCT FROM 'archived';   -- true even when status IS NULL
SELECT * FROM a JOIN b ON a.key IS NOT DISTINCT FROM b.key; -- NULL joins NULL

-- MySQL — no IS DISTINCT FROM; use the null-safe equal operator <=> instead
SELECT * FROM t WHERE NOT (status <=> 'archived');
SELECT * FROM a JOIN b ON a.key <=> b.key;

Three different tools, three different jobs. COALESCE picks a fallback value; NULLIF manufactures a NULL on purpose (mainly to disarm a divisor or hide a sentinel value); IS DISTINCT FROM compares two values while treating NULL as an ordinary, comparable value instead of “unknown.” Mixing them up is the usual bug: NULLIF(a, b) does not tell you whether a and b differ — for that, use a IS DISTINCT FROM b. MySQL is the outlier for the last one: it has no IS DISTINCT FROM syntax at all, so <=> (available since MySQL 4.1) is the only null-safe comparison operator there.