NULL: the value that breaks comparisons

Key points

• NULL means unknown, not zero and not empty string. Any comparison with it yields NULL rather than true or false, so WHERE x = NULL matches nothing at all - even rows where x is NULL.
• Use IS NULL and IS NOT NULL. They are the only operators that test for it.
• The trap that costs most people a bug: NOT IN with a subquery containing a single NULL returns no rows, because the comparison is unknown for every candidate.
• Aggregates skip NULL. COUNT(column) counts non-null values while COUNT(*) counts rows, and AVG divides by the non-null count - which quietly changes the answer.
• NULL sorts first ascending in MySQL, and it is treated as equal for GROUP BY and DISTINCT even though it is not equal to itself.
• Use COALESCE to substitute a default, and NULLIF to turn a sentinel such as an empty string back into NULL.

Example

-- Matches nothing, including rows where deleted_at IS NULL.
SELECT * FROM orders WHERE deleted_at = NULL;      -- always empty
SELECT * FROM orders WHERE deleted_at IS NULL;     -- correct

-- THE NOT IN TRAP. If ANY value in the subquery is NULL, this returns
-- zero rows - not "all rows that do not match".
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);  -- empty if any is NULL

-- Safe alternatives:
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- Aggregates ignore NULL, which changes the denominator:
SELECT COUNT(*)        AS rows_total,      -- 100
       COUNT(rating)   AS rated,           --  60  (40 are NULL)
       AVG(rating)     AS avg_of_rated,    -- divided by 60, not 100
       AVG(COALESCE(rating, 0)) AS avg_treating_null_as_zero
FROM reviews;

-- Substituting and producing NULL:
SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name,
       NULLIF(TRIM(phone), '') AS phone   -- empty string becomes NULL
FROM users;

-- NULL-safe equality: <=> treats NULL = NULL as TRUE.
SELECT * FROM a JOIN b ON a.code <=> b.code;   -- matches NULL to NULL

NULL is unknown, so every comparison with it is unknown - and NOT IN over a subquery containing NULL returns nothing.