Working with NULL

NULL means no value: unknown, missing or not applicable. It isn't zero and it isn't an empty string. Lucas Martin's join date was never recorded:

SELECT name, joined FROM customers;

= NULL doesn't work

This looks reasonable, and returns nothing:

SELECT name FROM customers WHERE joined = NULL;

Any comparison with NULL gives NULL (unknown) rather than true, and WHERE keeps only the rows where its condition is true:

SELECT NULL = NULL AS equal, NULL <> 5 AS not_equal, 10 + NULL AS total;

Even NULL = NULL is unknown: two missing values aren't known to be the same value.

IS NULL and IS NOT NULL

SELECT name, city FROM customers WHERE joined IS NULL;

SELECT first_name, last_name, title
FROM employees
WHERE manager_id IS NULL;      -- the people at the top: nobody manages them

IS NOT NULL keeps the rows that do have a value.

COALESCE: a fallback value

COALESCE(a, b, ...) returns the first argument that isn't NULL:

SELECT name, COALESCE(joined, 'not recorded') AS joined
FROM customers;

COALESCE is standard SQL and works everywhere. You'll also meet IFNULL(a, b) (SQLite, MySQL) and ISNULL(a, b) (SQL Server), which do the same job with exactly two arguments.

NULLIF: turn a value into NULL

NULLIF(a, b) gives NULL when a equals b, and a otherwise:

SELECT NULLIF(5, 5) AS same, NULLIF(5, 3) AS different;

Its classic use is guarding against division by zero. SQLite quietly returns NULL for x / 0, but PostgreSQL and SQL Server stop with an error, so portable code writes x / NULLIF(y, 0).

NULL in sorting

SQLite, MySQL and SQL Server put NULLs first when sorting in ascending order; PostgreSQL and Oracle put them last. SQLite, PostgreSQL and Oracle let you choose with NULLS FIRST or NULLS LAST:

SELECT name, joined
FROM customers
ORDER BY joined NULLS LAST;

NULL in aggregates

Arithmetic with NULL gives NULL, but aggregate functions skip NULLs: COUNT(joined) counts 9, not 10, and AVG divides only by the values that exist. The aggregates lesson shows this in action.