sqlstreak

NULL in SQL

NULL means “unknown” or “missing”. It isn't zero and it isn't empty text, and it follows its own rules. Most surprising query results trace back to one.

Comparisons with NULL are unknown

Anything compared to NULL, even NULL itself, is unknown, and WHERE only keeps rows that are true. Test for missing values with IS NULL and IS NOT NULL.

SELECT name, breed
FROM pets
WHERE breed IS NOT NULL;

COALESCE: a default for missing values

COALESCE(a, b, …) returns the first value that isn't NULL. It's the standard way to show a fallback label or treat missing numbers as zero.

SELECT name, COALESCE(breed, 'mixed breed') AS breed
FROM pets;

NULL in aggregates

Aggregates skip NULLs. COUNT(breed) counts only known breeds, and AVG divides by the number of non-NULL values. COUNT(*) counts every row.

SELECT COUNT(*) AS all_pets, COUNT(breed) AS known_breed
FROM pets;

NULL spreads through expressions

Arithmetic or || with a NULL gives NULL. 'Rocket' || NULL is NULL, not 'Rocket'. Wrap the nullable part in COALESCE first.

SELECT name || ' (' || COALESCE(breed, '?') || ')' AS label
FROM pets;

Common mistakes

  • WHERE breed = NULL or WHERE breed <> 'Beagle'. The second one also skips every NULL breed.

  • NOT IN (subquery) when the subquery can return NULL: it then matches nothing. Use NOT EXISTS.

  • Concatenating a nullable column and losing the whole value.

Questions

Is NULL the same as an empty string?+

No. '' is a known, empty value; NULL is unknown. (Oracle is a famous exception that treats '' as NULL.)

How do I sort NULLs last?+

PostgreSQL and SQLite both support ORDER BY col NULLS LAST.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now