NULL, and why your filter returned nothing

NULL is not zero and not an empty string. It means unknown. Once you hold that one sentence, most of SQL’s surprising behaviour becomes derivable rather than memorised.

Comparisons return three values, not two

Every comparison involving NULL yields NULL — neither true nor false:

SELECT 1 = NULL;        -- NULL
SELECT 1 <> NULL;       -- NULL
SELECT NULL = NULL;     -- NULL

And WHERE keeps a row only when the condition is true. Not “not false” — true. So WHERE coupon_code = NULL returns nothing, ever, on any table. That is why IS NULL exists as separate syntax.

The logic tables follow from “unknown”:

false AND NULL is false because one false is enough. true OR NULL is true for the same reason. Everywhere else, unknown propagates.

NOT NULL is NULL, which is the detail behind the next section.

The NOT IN bug

This is the single most expensive NULL mistake, because it produces an empty result rather than an error.

-- Customers who have never ordered. Looks right. Is wrong.
SELECT * FROM customer
WHERE id NOT IN (SELECT customer_id FROM "order");

If any row of that subquery returns NULL, the whole query returns zero rows. IN is a chain of ORs, so NOT IN is a chain of ANDs over <>:

id <> 1 AND id <> 7 AND id <> NULL
      -- becomes --
   true  AND  true  AND  NULL   =   NULL   -->  row rejected

Every row gets rejected, and nothing tells you. In our schema order.customer_id is NOT NULL, so this particular query is safe today — and it stops being safe the day someone makes the column nullable. Do not rely on that.

Three fixes, in order of preference:

-- 1. NOT EXISTS: correct regardless of NULLs, and usually the fastest
SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- 2. LEFT JOIN ... IS NULL (an "anti-join"), see lesson 4
SELECT c.* FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 3. NOT IN with the NULLs excluded — works, but you have to remember why
SELECT * FROM customer
WHERE id NOT IN (SELECT customer_id FROM "order" WHERE customer_id IS NOT NULL);

NOT EXISTS is the habit worth building. It has no NULL trap to remember, and it can stop at the first matching row instead of building a full list.

Note the asymmetry: plain IN with a NULL in the list is not broken. It just cannot ever match the NULL, which is what you would expect.

Comparing two nullable columns

Sometimes you genuinely want “these are the same, treating NULL as a value”. The engines spell it differently:

-- PostgreSQL: rows whose coupon changed, including to or from NULL
WHERE new_coupon IS DISTINCT FROM old_coupon

-- MySQL
WHERE NOT (new_coupon <=> old_coupon)

This matters most in change detection and in MERGE/upsert logic. Writing WHERE new_coupon <> old_coupon there quietly ignores every row where one side is NULL — which is exactly the row where something was set or cleared.

COALESCE and NULLIF

COALESCE returns its first non-NULL argument. Both engines have it.

SELECT id, COALESCE(coupon_code, 'none') AS coupon FROM "order";

MySQL also has IFNULL(a, b) for the two-argument case, and PostgreSQL does not. Use COALESCE and the query moves between engines unchanged.

NULLIF(a, b) returns NULL when a = b, which is the tool for turning a sentinel back into a real absence — and for avoiding division by zero:

SELECT SUM(unit_cents * quantity) / NULLIF(SUM(quantity), 0) AS avg_cents
FROM order_item;

Division by NULL is NULL. Division by zero is an error in PostgreSQL and, in MySQL under default settings, also an error in strict mode. NULLIF turns the crash into a NULL you can then COALESCE.

CASE

CASE is SQL’s conditional, and it short-circuits in order:

SELECT id,
       CASE
         WHEN shipped_at IS NOT NULL           THEN 'shipped'
         WHEN status = 'paid'                  THEN 'awaiting dispatch'
         WHEN placed_at < now() - interval '7 days' THEN 'stale'
         ELSE 'new'
       END AS state
FROM "order";

Without ELSE, an unmatched row yields NULL — a common source of unexplained NULLs in a report. Write the ELSE even when you believe it is unreachable.

In MySQL, now() - interval '7 days' is written NOW() - INTERVAL 7 DAY.

Aggregates ignore NULL, and COUNT is where you notice

Every aggregate except COUNT(*) skips NULL inputs.

SELECT
  COUNT(*)            AS rows_total,       -- counts rows
  COUNT(shipped_at)   AS rows_shipped,     -- counts non-NULL values
  AVG(unit_cents)     AS avg_price         -- ignores NULLs in the average
FROM "order" o JOIN order_item i ON i.order_id = o.id;

COUNT(col) counting fewer than COUNT(*) is not a bug; it is the definition. And AVG skipping NULLs means the divisor is the count of known values — which is usually right, and is wrong if you meant to treat unknown as zero. Then say so: AVG(COALESCE(unit_cents, 0)).

An aggregate over zero rows is worth knowing too: SUM of nothing is NULL, not 0. COUNT of nothing is 0. So COALESCE(SUM(x), 0) is the safe form in a report that might have no rows.

NULL and UNIQUE

A UNIQUE constraint permits many NULLs, in both engines, because two unknowns are not known to be equal.

-- Both PostgreSQL and MySQL accept all of these
INSERT INTO "order" (customer_id, status, placed_at, coupon_code)
VALUES (1, 'new', now(), NULL), (1, 'new', now(), NULL);

That is standard behaviour and usually what you want. When it is not — when you need “at most one row with no coupon per customer” — the answer is a partial index in PostgreSQL (CREATE UNIQUE INDEX ... WHERE coupon_code IS NULL) and, in MySQL, a generated column, because MySQL has no partial indexes. Lesson 8 covers this.

What to take away

• NULL means unknown; WHERE keeps only true.
• NOT IN over a nullable column returns nothing. Use NOT EXISTS.
• NULL-safe equality: IS NOT DISTINCT FROM in PostgreSQL, <=> in MySQL.
• Prefer COALESCE over IFNULL; use NULLIF to make a divisor safe.
• Always write ELSE in a CASE.
• SUM over no rows is NULL, not zero.