NULL, and the rows it quietly drops

NULL is not a value, it is the absence of one

Every comparison with NULL returns unknown — not true, not false. Including comparing it to itself:

SELECT NULL = NULL;    -- NULL, not true
SELECT NULL <> NULL;   -- NULL, not true either
SELECT NULL = 0;       -- NULL
SELECT NULL || 'text'; -- NULL

WHERE keeps rows where the condition is true. Unknown is not true, so the row is dropped — which is why a filter can quietly lose rows nobody asked it to lose:

-- Does NOT return employees whose bonus is NULL
SELECT * FROM employees WHERE bonus <> 500;

An employee with no bonus recorded is not "not 500" as far as SQL is concerned; it is unknown whether they are. Say it explicitly:

SELECT * FROM employees
WHERE bonus <> 500 OR bonus IS NULL;

IS NULL is the only test that works

WHERE manager_id IS NULL      -- correct
WHERE manager_id = NULL       -- always empty, never an error

The second is the bug that produces zero rows and no complaint. Some engines offer a null-safe equality — IS NOT DISTINCT FROM in PostgreSQL, <=> in MySQL — which treats two NULLs as equal. Useful, and not portable.

NOT IN with a NULL returns nothing at all

This one costs people an afternoon:

SELECT * FROM employees
WHERE department_id NOT IN (SELECT id FROM departments WHERE active = 1);

If that subquery returns a single NULL among its ids, the whole query returns zero rows. NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last comparison is unknown, so the conjunction can never be true.

NOT EXISTS does not have the problem, which is the main reason to prefer it:

SELECT * FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM departments d
  WHERE d.id = e.department_id AND d.active = 1
);

Where NULL is ignored instead

Aggregates skip it, which is usually what you want and occasionally not:

AVG is the trap: over a column with half its values missing, it is the average of the half that exists, not of the column. If a missing value should count as zero, say so with AVG(COALESCE(col, 0)).

The functions that give you a value back

SELECT COALESCE(mobile, landline, email, 'no contact') AS contact
FROM customers;

COALESCE returns its first non-NULL argument, takes as many as you like, and is standard everywhere. NULLIF(a, b) is the reverse — it returns NULL when the two are equal, which is the neat way to avoid a division by zero:

SELECT revenue / NULLIF(orders, 0) AS average_order
FROM daily_totals;

Zero orders now produces NULL — an honest "no answer" — instead of an error that kills the whole query.

The design question underneath

Every nullable column is a question the schema refuses to answer: does this row have no value, or an unknown one, or a value nobody has filled in yet? Those are three different things, and NULL is all of them at once. When a column can be NOT NULL with a sensible default, that is one fewer three-valued comparison in every query that ever touches it.