NULL

NULL means unknown, not empty — which is why NULL = NULL is not true and you need IS NULL or the <=> operator.

Syntaxexpr IS [NOT] NULL -- or a <=> b for NULL-safe equality

NULL is a missing, unknown value. It is not zero, not an empty string, and not FALSE — 0 IS NULL and '' IS NULL both return 0, and you can store either in a NOT NULL column because they are real values.

Testing for it

You cannot use =, <>, < or > to test for NULL: the result of any such comparison is itself NULL, and WHERE keeps only rows whose condition is TRUE. NULL = NULL is NULL, because two unknowns are not known to be the same. Use IS NULL and IS NOT NULL. ISNULL(expr) is a function form returning 1 or 0; <=> is the NULL-safe equality operator, so NULL <=> NULL is 1 and 1 <=> NULL is 0.

Where it leaks

Arithmetic and string functions propagate it: 1 + NULL and CONCAT('a', NULL) are both NULL. Aggregates go the other way and ignore it — COUNT(col) counts non-NULL values while COUNT(*) counts rows, and AVG() divides by the non-NULL count.

Two other places treat NULLs as equal to each other: GROUP BY collects them into one group, and DISTINCT keeps one. Sorting has its own rule — ORDER BY ... ASC puts NULLs first, DESC puts them last.

Example

Example · sql
-- orders.shipped_at is NULL until the order ships.

-- Returns nothing, ever. shipped_at = NULL evaluates to NULL for
-- every row, including the rows where shipped_at really is NULL.
SELECT id FROM orders WHERE shipped_at = NULL;

-- Correct.
SELECT id FROM orders WHERE shipped_at IS NULL;

-- Counting: these three numbers are different on the same table.
SELECT COUNT(*)           AS orders_total,     -- every row
       COUNT(shipped_at)   AS orders_shipped,   -- non-NULL values only
       SUM(shipped_at IS NULL) AS orders_pending -- 1/0 summed
FROM orders;

-- Substitute a default for display. COALESCE returns its first
-- non-NULL argument.
SELECT id, COALESCE(tracking_code, 'not dispatched') AS tracking
FROM orders;

When to use it

  • List orders that have not shipped yet, where the ship date column is nullable
  • Compare an incoming row against the stored one to decide whether anything actually changed
  • Sort a report so that rows with no value appear last instead of first
  • Explain why a total and an average disagree about how many rows contributed

More examples

IS NULL, ISNULL() and <=> side by side

IS NULL is the form to write; the other two are worth recognising. ISNULL() appears in older code and inside SELECT lists where a 1/0 is wanted. <=> earns its place when both sides can be NULL, which is the normal case in change detection and in diffing a staging table against a live one.

Example · sql
SELECT 1 IS NULL, 1 IS NOT NULL;   -- 0, 1
SELECT 0 IS NULL, '' IS NULL;      -- 0, 0  (zero and '' are values)

-- Three ways to ask the same question:
SELECT * FROM orders WHERE shipped_at IS NULL;
SELECT * FROM orders WHERE ISNULL(shipped_at);      -- function form
SELECT * FROM orders WHERE shipped_at <=> NULL;     -- NULL-safe equal

-- And the longhand that <=> replaces when comparing two columns:
SELECT * FROM rows_a a JOIN rows_b b ON a.id = b.id
WHERE (a.note = b.note OR (a.note IS NULL AND b.note IS NULL));
SELECT * FROM rows_a a JOIN rows_b b ON a.id = b.id
WHERE a.note <=> b.note;   -- identical

Putting NULLs last in a sort

The extra leading sort key is the standard workaround, and it works because the IS NULL test itself produces a sortable 0 or 1. Reverse it to NOT shipped_at IS NULL to force NULLs first under DESC. Expect the sort to cost more, since an expression as the first key rules out reading the rows in index order.

Example · sql
-- Default: ASC puts NULLs first, so unshipped orders lead the report.
SELECT id, shipped_at FROM orders ORDER BY shipped_at;

-- MySQL 8.4 has no NULLS LAST clause. Sort on the test first:
-- shipped_at IS NULL is 0 for shipped rows and 1 for the rest.
SELECT id, shipped_at
FROM orders
ORDER BY shipped_at IS NULL, shipped_at;

Aggregates skip NULL; GROUP BY does not

Decide deliberately which average you mean: AVG(rating) answers "how did the people who rated feel", AVG(COALESCE(rating, 0)) answers something quite different. The second query shows the other half of the rule — NULLs are equal to each other for grouping, so there will be one row with assignee_id NULL holding every unassigned ticket.

Example · sql
-- AVG ignores NULL ratings, so it averages over responders only.
SELECT COUNT(*)              AS responses,
       COUNT(rating)         AS rated,
       AVG(rating)           AS avg_rated_only,
       AVG(COALESCE(rating, 0)) AS avg_counting_blank_as_zero
FROM survey_answers;

-- GROUP BY treats all NULLs as one group, so unassigned rows
-- collapse into a single bucket rather than disappearing.
SELECT assignee_id, COUNT(*) AS tickets
FROM tickets
GROUP BY assignee_id
ORDER BY tickets DESC;

Related

sql coalescesql comparisonsql insql not nullsql case

Discussion

  • Be the first to comment on this lesson.