NULL in SQL: the pitfalls that distort query results

In SQL, NULL is not zero, not an empty string and not false. It means «unknown value» or «not applicable». Put like that it sounds simple, but its behaviour causes some of the most insidious mistakes in queries: the ones that produce no error message, only wrong results that look right.

Three-valued logic

In most languages a condition is either true or false. SQL has a third outcome: unknown. Any comparison with NULL returns unknown:

• 5 = NULL → unknown
• NULL = NULL → unknown
• NULL <> 3 → unknown

The reasoning is consistent: if I do not know the age of two people, I cannot say it is the same. But the practical consequence is that WHERE column = NULL never returns any rows. The correct form is:

SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

The WHERE clause keeps only rows for which the condition is true. Rows for which it is false and rows for which it is unknown are discarded in exactly the same way.

Pitfall 1: the «not equal» filter

A products table has a category column that is not always filled in. You want all products not in the «Outlet» category:

SELECT * FROM products WHERE category <> 'Outlet';

Products with no category do not appear, because NULL <> 'Outlet' is unknown. If they must be included, you have to say so explicitly:

SELECT * FROM products
WHERE category <> 'Outlet' OR category IS NULL;

Some databases also offer the IS DISTINCT FROM operator, which treats NULL as a comparable value.

Pitfall 2: NOT IN with a subquery

This is the most dangerous pitfall. You are looking for customers who do not appear in the list of blocked customers:

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM blocks);

If even a single row in blocks has an empty customer_id, the query returns no customers at all. The reason: id NOT IN (1, 2, NULL) is equivalent to id <> 1 AND id <> 2 AND id <> NULL, and the last term is always unknown, so the whole condition is never true.

The robust solution is NOT EXISTS, which does not have this problem:

SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocks b WHERE b.customer_id = c.id
);

Pitfall 3: counts

Aggregate functions ignore NULL values, with one important exception.

The average is the most delicate case. If, in a ratings table, people who did not answer have NULL, AVG(score) averages only those who answered. If a missing answer should have counted as zero, the result is inflated. There is no universally right answer: you have to decide what absence means and write it into the query.

Another quirk: SUM over a set where every value is NULL, or over no rows, returns NULL rather than zero. In a report this can produce empty cells where you expect 0.

Pitfall 4: calculations

Any arithmetic operation or concatenation with NULL produces NULL:

SELECT price + shipping FROM orders;

If shipping is not specified, the total becomes unknown. The COALESCE function returns the first non-null value from a list and is the standard tool for these cases:

SELECT price + COALESCE(shipping, 0) AS total FROM orders;

Use it with judgement: replacing NULL with 0 makes sense for free shipping, but not for a temperature that was never measured.

Pitfall 5: sorting and uniqueness constraints

When sorting, databases disagree about where to put NULLs: some treat them as larger than every value, others as smaller. Where supported, ORDER BY column NULLS LAST makes the choice explicit.

In UNIQUE constraints, many databases allow several rows with NULL in the same column, precisely because two NULLs are not considered equal. It is useful behaviour, for example for an optional code that must be unique when present, but it surprises people who do not expect it.

Practical design rules

1. Declare NOT NULL on every column that must always have a value. It is the most effective defence.
2. Use DEFAULT values when a sensible one exists, such as 0 for a quantity.
3. Do not use conventional values such as -1 or «N/A» instead of NULL: they end up in sums and averages.
4. Prefer NOT EXISTS to NOT IN with subqueries.
5. When writing a report, ask for each column: what should happen if it is empty?

In short

• NULL means unknown, and any comparison with NULL yields unknown.
• Filter with IS NULL and IS NOT NULL, never with = NULL.
• NOT IN with a subquery containing NULL returns nothing: NOT EXISTS is better.
• COUNT(column), AVG and SUM ignore NULLs, which affects reports.
• A NOT NULL constraint prevents most problems at the source.