NULL Handling

Beginner

⏱️ 10 mins read

Introduces the orders table (id, customer_id, status, total, items, order_date, shipped_at) — shipped_at is NULL until an order ships, which makes it the perfect NULL playground.

What You'll Learn

NULL represents missing or unknown data — not zero, not empty string. NULL propagates: any arithmetic with NULL returns NULL. Comparisons with NULL always return UNKNOWN. Use COALESCE to replace NULL with a default. Use NULLIF to return NULL when two values are equal.

Syntax

WHERE col IS NULL
WHERE col IS NOT NULL
COALESCE(col, default_value)
NULLIF(col, value)

Example

-- Check for NULL (NEVER use = NULL)
SELECT id, name FROM employees WHERE department_id IS NULL;   -- Edsger
SELECT id, status FROM orders WHERE shipped_at IS NULL;       -- pending orders

-- Replace NULL with a default in the output
SELECT id, COALESCE(shipped_at, order_date) AS effective_date
FROM orders;

-- NULLIF: returns NULL if items = 0 (avoids division by zero)
SELECT id, total / NULLIF(items, 0) AS avg_item_price
FROM orders;

-- COUNT(*) vs COUNT(col) — may differ!
SELECT COUNT(*), COUNT(shipped_at) FROM orders;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

-- orders (introduced by this topic)
-- id | customer_id | status    | total  | items | order_date | shipped_at
-- 1  | 1           | shipped   | 240.00 | 3     | 2024-01-15 | 2024-01-18
-- 2  | 2           | shipped   | 89.90  | 1     | 2024-02-03 | 2024-02-07
-- 3  | 1           | pending   | 430.00 | 2     | 2024-03-11 | NULL
-- 4  | 3           | shipped   | 59.50  | 1     | 2024-04-02 | 2024-04-05
-- 5  | 4           | cancelled | 120.00 | 0     | 2024-05-20 | NULL

NULL is a state, not a value — and it propagates

There is no 'NULL value': NULL marks the absence of one. It is not zero, not an empty string, and not equal to anything — including itself. Any arithmetic with NULL yields NULL, and any comparison with NULL yields UNKNOWN, never TRUE or FALSE.

-- Pending orders have shipped_at = NULL:
SELECT id, total, shipped_at FROM orders WHERE status = 'pending';

-- None of these behave the way beginners expect:
SELECT id FROM orders WHERE shipped_at = NULL;           -- WRONG: always empty
SELECT id FROM orders WHERE shipped_at IS NULL;          -- RIGHT

-- Not-equal is bitten too — pending orders fail BOTH branches:
SELECT id FROM orders WHERE shipped_at < '2024-06-01';   -- pending rows vanish
SELECT id FROM orders
WHERE shipped_at < '2024-06-01' OR shipped_at IS NULL;   -- explicit is safe
Nullable column → every filter needs an 'OR col IS NULL' escape hatch, or those rows disappear silently.

Aggregates quietly ignore NULLs — except COUNT(*)

SUM, AVG, MIN, and MAX skip NULL rows entirely; COUNT(*) counts rows while COUNT(col) counts non-NULL values. That is why two 'counts' on the same table can disagree — and why AVG can come out higher than expected when the missing values are exactly the interesting ones.

SELECT
  COUNT(*)           AS all_orders,     -- every row
  COUNT(shipped_at)  AS shipped_orders, -- pending orders excluded silently
  AVG(total)         AS avg_total
FROM orders;

-- Make the split explicit instead of implicit:
SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status;   -- Topic 9 formalizes this pattern
'Why does the report say 42 orders when the dashboard says 38?' — because COUNT(col) isn't COUNT(*). Decide what missing should mean, then encode it with COALESCE.

COALESCE and NULLIF are CASE in disguise

COALESCE(a, b, c) returns the first non-NULL argument; NULLIF(a, b) returns NULL when a = b. Both are shorthands for CASE expressions (Topic 11) — knowing the equivalence tells you exactly how they behave around three-valued logic.

-- Display a placeholder without changing stored data:
SELECT id, COALESCE(shipped_at, order_date) AS effective_date
FROM orders;

-- Guard against division by zero (items = 0 → NULL, not an error):
SELECT id, total / NULLIF(items, 0) AS avg_item_price
FROM orders;
-- NULLIF(a, b) is exactly: CASE WHEN a = b THEN NULL ELSE a END
COALESCE chooses a value; NULLIF manufactures a NULL to suppress a computation. Topic 11 generalizes both.

Common Mistakes

COUNT(*) counts ALL rows including NULLs. COUNT(shipped_at) ignores NULL shipping dates — the two can return different numbers. Also: NULL + 5 = NULL. NULL = NULL is UNKNOWN (not TRUE). This is why WHERE col = NULL never matches.

Interview Tips

This is a frequent gotcha question. Be ready to explain three-valued logic in SQL (TRUE, FALSE, UNKNOWN). Mention that NOT IN with a subquery containing NULLs always returns empty — a classic bug.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. Which test correctly finds orders that have not shipped yet?

2. What does NULL + 5 evaluate to?

3. SELECT COUNT(*), COUNT(shipped_at) FROM orders — what does ShopCo return?

4. What does COALESCE(shipped_at, order_date) do?

5. x NOT IN (SELECT customer_id FROM orders) returns nothing. Why?

6. Is NULL equal to NULL?

Practice

Return the id of each transaction that has no category, labelling its category 'Uncategorized'.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why does NOT IN with a subquery that contains NULLs return nothing?

x NOT IN (1, 2, NULL) is evaluated as x != 1 AND x != 2 AND x != NULL — and that last comparison is UNKNOWN, so the whole AND-chain can never be TRUE. It is one of the most famous SQL traps; the fix is NOT EXISTS or filtering NULLs out first (Topic 16).

Should I just avoid NULLs with default values?

Sometimes — but they are not interchangeable. A default says 'we know the value and it is this'; NULL says 'we do not know (or it does not apply)'. AVG(total) correctly ignores unknowns but would happily include a fake default of 0 and drag the average down. Model the meaning first, then pick.

Back to Full Roadmap