SQL NULL explained for beginners: IS NULL, COALESCE and the traps

A customer did not give a phone number. A product has not been priced yet. A bonus has not been decided. A database needs a way to say "there is nothing here", and that way is NULL. It is the most common cause of "my query returns the wrong answer and I cannot see why", and the rules are small enough to learn in one sitting.

The queries below ran in DuckDB, and each output is exactly what it printed. The rules are the same in MySQL, PostgreSQL, Snowflake and BigQuery, apart from the small notes marked in the text.

The table

6 employees. Some have no bonus recorded and one has no email.

CREATE TABLE staff (
  emp_id INTEGER,
  name   VARCHAR,
  email  VARCHAR,
  salary INTEGER,
  bonus  INTEGER
);

INSERT INTO staff VALUES
  (1, 'Asha',  '[email protected]',  50000, 5000),
  (2, 'Ravi',  '[email protected]',  60000, NULL),
  (3, 'Meena', NULL,             55000, 0),
  (4, 'Kiran', '[email protected]', 70000, NULL),
  (5, 'Divya', '[email protected]', 65000, 8000),
  (6, 'Imran', '[email protected]', 52000, NULL);

SELECT * FROM staff ORDER BY emp_id;

emp_id | name  | email          | salary | bonus
-------+-------+----------------+--------+------
1      | Asha  | [email protected]  | 50000  | 5000
2      | Ravi  | [email protected]  | 60000  | NULL
3      | Meena | NULL           | 55000  | 0
4      | Kiran | [email protected] | 70000  | NULL
5      | Divya | [email protected] | 65000  | 8000
6      | Imran | [email protected] | 52000  | NULL
(6 rows)

Look closely at Meena's bonus: it is 0, a real number meaning "she gets no bonus". Ravi's bonus is NULL, which means "we do not know". They are different things. A NULL is not zero, and it is not an empty string either.

1Why = NULL finds nothing

You want everyone whose bonus is missing. The natural attempt is:

SELECT name FROM staff WHERE bonus = NULL;

name
----
(0 rows)

No rows, and no error. SQL treats NULL as "unknown", and asking "is this unknown value equal to another unknown value?" is itself unknown, never true. WHERE keeps only rows where the condition is true, so every row is dropped. Even NULL = NULL is unknown:

SELECT NULL = NULL AS equal, NULL IS NULL AS is_null;

equal | is_null
------+--------
NULL  | true
(1 row)

The only correct test is IS NULL, and its opposite is IS NOT NULL:

SELECT name FROM staff WHERE bonus IS NULL ORDER BY name;

name
-----
Imran
Kiran
Ravi
(3 rows)

SELECT name FROM staff WHERE bonus IS NOT NULL ORDER BY name;

name
-----
Asha
Divya
Meena
(3 rows)

Ravi, Kiran and Imran have no bonus recorded. Asha, Meena and Divya have one, and Meena's zero counts as a value, so she appears in the second list.

2What aggregates do with NULL

Aggregate functions quietly skip NULLs. Compare these five numbers:

SELECT COUNT(*)      AS all_rows,
       COUNT(bonus)  AS rows_with_a_bonus,
       SUM(bonus)    AS total_bonus,
       AVG(bonus)    AS average_bonus,
       COUNT(email)  AS rows_with_an_email
FROM staff;

all_rows | rows_with_a_bonus | total_bonus | average_bonus     | rows_with_an_email
---------+-------------------+-------------+-------------------+-------------------
6        | 3                 | 13000       | 4333.333333333333 | 5
(1 row)

COUNT(*) counts every row, 6. COUNT(bonus) counts only the rows where the bonus is not NULL, 3. SUM adds 5000, 0 and 8000. And AVG divides by 3, not by 6, because it ignores the NULL rows entirely. So the average bonus is 4333.33, not 2166.67. Whether that is the number you want depends on the question, which is the main lesson: decide what a missing value should mean before you aggregate.

3COALESCE: replace NULL with a default

COALESCE takes a list and returns the first value that is not NULL. The usual use is giving a missing value a default.

SELECT name, bonus, COALESCE(bonus, 0) AS bonus_or_zero
FROM staff
ORDER BY emp_id;

name  | bonus | bonus_or_zero
------+-------+--------------
Asha  | 5000  | 5000
Ravi  | NULL  | 0
Meena | 0     | 0
Kiran | NULL  | 0
Divya | 8000  | 8000
Imran | NULL  | 0
(6 rows)

Now Ravi, Kiran and Imran show 0 in the new column. Use it to make arithmetic safe, because anything plus NULL is NULL:

SELECT name,
       salary + bonus               AS total_with_null,
       salary + COALESCE(bonus, 0)  AS total_pay
FROM staff
ORDER BY emp_id;

name  | total_with_null | total_pay
------+-----------------+----------
Asha  | 55000           | 55000
Ravi  | NULL            | 60000
Meena | 55000           | 55000
Kiran | NULL            | 70000
Divya | 73000           | 73000
Imran | NULL            | 52000
(6 rows)

salary + bonus is NULL for every employee without a bonus, so those people would vanish from a total. Wrapping the bonus in COALESCE fixes that. If you replace NULL with a default and average the result, remember you have changed what the average means:

SELECT AVG(bonus) AS ignoring_nulls, AVG(COALESCE(bonus, 0)) AS counting_missing_as_zero
FROM staff;

ignoring_nulls    | counting_missing_as_zero
------------------+-------------------------
4333.333333333333 | 2166.6666666666665
(1 row)

4333.33 against 2166.67. Neither is wrong. They answer different questions, "the average of the bonuses we know" and "the average per employee".

4NULLIF and CASE: the other direction

NULLIF(a, b) returns NULL when the two are equal, and a otherwise. It is handy for turning a placeholder into a real NULL, and for avoiding division by zero:

SELECT name, NULLIF(bonus, 0) AS bonus_if_any
FROM staff
ORDER BY emp_id;

name  | bonus_if_any
------+-------------
Asha  | 5000
Ravi  | NULL
Meena | NULL
Kiran | NULL
Divya | 8000
Imran | NULL
(6 rows)

Meena's 0 became NULL. A CASE expression does the same job with more control, and it is the way to label missing values:

SELECT name,
       CASE WHEN email IS NULL THEN 'no email on file' ELSE email END AS contact
FROM staff
ORDER BY emp_id;

name  | contact
------+-----------------
Asha  | [email protected]
Ravi  | [email protected]
Meena | no email on file
Kiran | [email protected]
Divya | [email protected]
Imran | [email protected]
(6 rows)

5Sorting, grouping and joining with NULL

Sorting. In DuckDB and PostgreSQL, NULLs come last in an ascending sort. Oracle does the same, and MySQL and SQL Server put them first, so state it when it matters:

SELECT name, bonus FROM staff ORDER BY bonus DESC NULLS LAST, name;

name  | bonus
------+------
Divya | 8000
Asha  | 5000
Meena | 0
Imran | NULL
Kiran | NULL
Ravi  | NULL
(6 rows)

Grouping. GROUP BY treats all NULLs as one group, so they are not lost:

SELECT bonus IS NULL AS missing_bonus, COUNT(*) AS people
FROM staff
GROUP BY bonus IS NULL
ORDER BY missing_bonus;

missing_bonus | people
--------------+-------
false         | 3
true          | 3
(2 rows)

Joining. A join never matches NULL to NULL, because NULL = NULL is unknown. 2 rows that both have a missing key will not be paired. This is why a left join can leave NULLs in the columns of the right table: there was simply nothing to match.

NOT IN. One NULL inside the list of a NOT IN makes the whole condition unknown, so it returns no rows at all. Prefer NOT EXISTS or a LEFT JOIN ... IS NULL, which are safe. The full trap, with output, is in SQL interview questions for data engineers.

Quick checklist

1. 1Can this column be NULL? Look at the data before you write the query.
2. 2Test with IS NULL, never = NULL.
3. 3Say what a missing value should mean for each total or average.
4. 4Use COALESCE to supply a default, and write down why you chose it.
5. 5Remember that <>, NOT IN and joins skip NULL rows.

Practise with Employees without a manager and Coalesce null salaries to zero, both free.