NULL

NULL trips up almost every SQL writer the first time they really encounter it. It is not zero. It is not an empty string. It is the database's way of saying we do not know what this value is - and that one distinction changes how comparisons, arithmetic, and aggregation behave in ways that feel deeply unintuitive until they click.

NULL is not equal to anything, including itself

Here is the thing that surprises everyone at first:

SELECT 1 = NULL;    -- returns NULL, not true or false
SELECT NULL = NULL; -- returns NULL, not true

The logic, once you accept the premise, is sound. If a value is unknown, you cannot conclude it equals anything - you simply do not know. An unknown value might equal 1, or it might not. So the result is also unknown, which is NULL.

That has one immediate practical consequence: you cannot filter for NULL using =:

-- Returns zero rows, even if many employees have no manager
SELECT * FROM employees WHERE manager_id = NULL;

-- Correct
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

Always use IS NULL and IS NOT NULL. Using = with NULL is not a syntax error - the query just silently returns nothing, which is the worst kind of bug to track down.

NULL propagates through expressions

NULL is infectious. Drop it into almost any expression and the whole thing becomes NULL:

Expression	Result
`5 + NULL`	NULL
`salary * 1.10` (if salary is NULL)	NULL
`'hello'
`NULL AND TRUE`	NULL
`NULL OR TRUE`	TRUE (exception)
`NULL OR FALSE`	NULL

The OR TRUE exception exists because one side being definitely true makes the whole thing true, no matter what the other side is. But AND and arithmetic follow the general rule: NULL in, NULL out.

Keep that in mind whenever you write a calculation that touches a nullable column. Those rows will silently produce NULL results unless you explicitly handle it.

COALESCE: substituting a default

COALESCE returns the first non-NULL value in its argument list. Think of it as a short-circuit: once it finds something real, it stops and returns that.

SELECT
  name,
  COALESCE(commission, 0) AS commission,
  salary + COALESCE(commission, 0) AS total_compensation
FROM employees;

If commission is NULL, you get 0. If it has a real value, you get that. Either way, the NULL stops there and does not poison the total_compensation calculation.

You can chain as many arguments as you need: COALESCE(preferred_name, first_name, 'Unknown') tries each one in order and returns the first that is not NULL.

NULL in aggregate functions

Aggregate functions mostly ignore NULLs - but there is one exception that catches people off guard:

Function	Behavior with NULL
`COUNT(*)`	Counts all rows, including those with NULLs
`COUNT(column)`	Counts only non-NULL values in that column
`SUM(column)`	Ignores NULLs, sums the rest
`AVG(column)`	Ignores NULLs, averages the rest
`MIN` / `MAX`	Ignores NULLs

The gap between COUNT(*) and COUNT(column) is wider than it looks:

SELECT
  COUNT(*)      AS total_employees,    -- everyone
  COUNT(bonus)  AS employees_with_bonus -- only those where bonus is not NULL
FROM employees;

AVG(bonus) has the same behavior - it divides by the count of non-NULL values, not total rows. So if half your employees have no bonus recorded, your average is computed over only the other half. If you want NULL to count as zero, you have to say so: AVG(COALESCE(bonus, 0)).

The NOT IN trap

This one is the most dangerous NULL behavior you will encounter, and it has burned a lot of people in production. If any value in a NOT IN list is NULL, the entire expression evaluates to NULL, and zero rows come back.

-- Finds employees who are not managers -- or tries to
SELECT * FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

If even one employee has a NULL manager_id - say, the CEO who reports to nobody - the subquery returns a list containing NULL. SQL cannot confirm that any id is "not in" a list that contains an unknown value, so every comparison returns NULL. Every row gets excluded. You get zero results with no error.

The fix: filter nulls from the subquery.

WHERE id NOT IN (
  SELECT manager_id FROM employees
  WHERE manager_id IS NOT NULL
)

Better yet, use NOT EXISTS, which handles NULL correctly by design and reads clearly too:

WHERE NOT EXISTS (
  SELECT 1 FROM employees mgr
  WHERE mgr.manager_id = employees.id
)

Designing for NULL