NULL: the missing value

Step 1 Understand it

Why you need it. Real data has gaps: an employee with no manager, an order with no amount. NULL causes more wrong answers than anything else in SQL, because it doesn't behave like a normal value. Rows quietly vanish from filters, and sums and averages change, without any error message.

Watch it happen

Picture it

flowchart LR
  C["a condition<br/>on one row"] --> T["true"]
  C --> F["false"]
  C --> U["unknown<br/>anything compared with NULL"]
  T -->|"WHERE keeps"| K["kept"]
  F -->|"WHERE drops"| D["dropped"]
  U -->|"WHERE drops"| D

The idea

NULL is not zero and not empty text; it means "we don't know". So SQL uses three-valued logic: a condition can be true, false or unknown. manager_id = NULL is unknown for every row — even a NULL one, because "is this unknown thing equal to that unknown thing?" is itself unknown. WHERE keeps only true, so the query returns nothing. To test for NULL, use IS NULL or IS NOT NULL. Any arithmetic with NULL gives NULL (10 + NULL is NULL). COALESCE(a, b, …) returns the first value that isn't NULL, which is how you put in a default.

The shape of it

WHERE column IS NULL
WHERE column IS NOT NULL
COALESCE(column, default_value)

Words in lower case are the parts you fill in.

How it works, step by step

1. WHERE manager_id = NULL: for Asha, NULL = NULL is unknown; for everyone else, 1 = NULL is unknown. Nothing is true, so no rows come back.
2. WHERE manager_id IS NULL: true for Asha only. IS NULL is a real test that answers yes or no.
3. WHERE dept_id <> 1: for Gus, NULL <> 1 is unknown, so Gus is dropped — even though he is clearly not in department 1.
4. To keep him, say so: WHERE dept_id <> 1 OR dept_id IS NULL.
5. COALESCE(amount, 0) turns order 106's NULL into 0 and leaves the other amounts alone.

Analogy

A form with a blank box for "age". Is a blank age over 30? You can't say yes, and you can't say no — you just don't know. Are two blank ages equal? You don't know that either.

Where the analogy breaks

A person looking at two blank boxes might say "they're both blank, so they match". SQL never does that with =. Only IS NULL, IS NOT DISTINCT FROM, and grouping (GROUP BY puts all NULLs together) treat NULLs as alike.

Worked examples

SELECT name
FROM employees
WHERE manager_id = NULL;

SELECT name, manager_id
FROM employees
WHERE manager_id IS NULL;

SELECT name, dept_id
FROM employees
WHERE dept_id <> 1
ORDER BY name;

SELECT order_id, amount, COALESCE(amount, 0) AS amount_or_zero
FROM orders
WHERE customer_id = 3
ORDER BY order_id;

Must-know points

• NULL means unknown or missing. It is not 0 and not ''.
• Never write = NULL or <> NULL. Use IS NULL and IS NOT NULL.
• Any comparison with NULL is unknown, and WHERE drops unknown rows — including from <> and NOT IN.
• Arithmetic with NULL gives NULL: 10 + NULL is NULL.
• COALESCE(a, b, c) returns the first non-NULL value. NULLIF(a, b) returns NULL when a = b — handy to avoid dividing by zero.
• Aggregates (SUM, AVG, COUNT of a column) skip NULLs — Phase 2 covers this.

Common mistakes

• Writing WHERE manager_id = NULL and getting no rows, with no error.
• Forgetting that <> drops NULL rows: "everyone not in Sales" silently loses people with no department.
• Replacing NULL with 0 in an average, which changes the answer. Decide what the gap really means first.

In other databases

• NULL-safe equality: PostgreSQL and SQLite have a IS NOT DISTINCT FROM b (true when both are NULL); MySQL has a <=> b.
• COALESCE works everywhere. MySQL also has IFNULL(a, b), SQL Server has ISNULL(a, b), and Oracle has NVL(a, b) — all for two arguments.
• In Oracle, empty text '' is treated as NULL. No other major database does this.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL NULL Values

  short page with a try-it editor, free

• Read

  Select Star SQL

  a free interactive book

• Practice

  SQLBolt — Lesson 8: A short note on NULLs

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain three-valued logic, and why WHERE manager_id = NULL returns no rows.

   Show answer

   In SQL a condition can be true, false or unknown. That is three-valued logic. Any comparison with NULL is unknown, because you can't compare against a value nobody knows.

   WHERE manager_id = NULL asks "is manager_id equal to unknown?" for each row. The answer is unknown every time, even for Asha, whose manager_id really is NULL. WHERE keeps only rows that are true, so no rows come back.

   To find missing values, ask a different question: WHERE manager_id IS NULL. IS NULL is always true or false, never unknown.

2. Writefrom a blank page, then run it

   Write a query for customers with no city on record, showing name and city.

   Show answer

   Use IS NULL, never = NULL. Only Zoe has no city.

   sqlCopy

   SELECT name, city
   FROM customers
   WHERE city IS NULL;

   Try it

   1 row

   name	city
   Zoe	NULL

3. Predictsay it before you run it

   What do SELECT NULL = NULL, NULL IS NULL, 5 + NULL, COALESCE(NULL, NULL, 7); return? Say all four.

   Show answer

   NULL, 1 (true), NULL, 7.

   • NULL = NULL: two unknowns can't be called equal, so it is unknown: NULL.
   • NULL IS NULL: true, which SQLite shows as 1.
   • 5 + NULL: any sum with NULL is NULL.
   • COALESCE(NULL, NULL, 7): the first value that is not NULL, which is 7.

   sqlCopy

   SELECT NULL = NULL, NULL IS NULL, 5 + NULL, COALESCE(NULL, NULL, 7);

   Try it

   1 row

   NULL = NULL	NULL IS NULL	5 + NULL	COALESCE(NULL, NULL, 7)
   NULL	1	NULL	7

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Write "every employee not in department 1", making sure Gus is included.

   Show answer

   dept_id <> 1 alone misses Gus, because NULL <> 1 is unknown. Add the NULL case on purpose with OR dept_id IS NULL.

   Other ways that also work: COALESCE(dept_id, 0) <> 1, or dept_id IS NOT 1 in SQLite (IS DISTINCT FROM 1 in PostgreSQL).

   sqlCopy

   SELECT name, dept_id
   FROM employees
   WHERE dept_id <> 1 OR dept_id IS NULL
   ORDER BY name;

   Try it

   4 rows

   name	dept_id
   Dara	2
   Eli	2
   Fatima	3
   Gus	NULL

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

NULL represents a missing or unknown value, and SQL uses three-valued logic: comparisons with NULL return unknown, and WHERE keeps only rows that are true. So I never compare with equals NULL — I use IS NULL or IS NOT NULL — and I watch for filters like not-equal or NOT IN silently dropping NULL rows. When I need a default I use COALESCE, but only after deciding whether the gap really means zero.

Follow-up questions

Tick each check when you can do it without notes