SQL NULL: handling missing values

In a table, some information can be missing: an age nobody entered, an employee without a manager… SQL represents these empty cells with a special value, NULL. It is one of the notions that trips up beginners the most, because NULL does not compare like a normal value.

Syntax

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

How it works

NULL means “unknown”

NULL is neither 0 nor an empty string: it is the absence of a value. Comparing something to an unknown value gives an unknown result. That is why age = NULL is never true, even when the age is missing.

To test for a missing value, there are two dedicated operators: IS NULL and IS NOT NULL.

NULL in calculations

• A calculation with NULL gives NULL: NULL + 5 is NULL.
• The aggregate functions SUM, AVG, MIN, MAX and COUNT(column) ignore NULLs. COUNT(*), however, counts every row.

Replacing NULL with a value

COALESCE(a, b, c) returns the first non-NULL value of the list. COALESCE(age, 0) shows 0 instead of a missing age. SQLite also offers IFNULL(a, b).

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT prenom, nom, age
FROM clients
WHERE age IS NULL;

SELECT prenom, nom, COALESCE(age, 'not provided') AS age
FROM clients
WHERE age IS NULL OR id <= 3;

SELECT COUNT(*) AS clients, COUNT(age) AS ages_connus
FROM clients;

SELECT prenom, nom, poste
FROM employes
WHERE manager_id IS NULL;

Common mistakes

SELECT * FROM clients WHERE age = NULL;

SELECT * FROM clients WHERE age IS NULL;

SELECT COUNT(*) FROM clients WHERE age <> 30;

SELECT COUNT(*) FROM clients WHERE age <> 30 OR age IS NULL;

SELECT * FROM clients WHERE email = NULL;

SELECT * FROM clients WHERE email IS NULL OR email = '';

Exercises

Try writing the query yourself before opening the solution.

SELECT COUNT(*) FROM clients WHERE age IS NOT NULL;

SELECT prenom, COALESCE(manager_id, 0) AS manager FROM employes;

Frequently asked questions