SQL practice › SQL reference › NULL

SQL NULL: why = NULL finds nothing, IS NULL, COALESCE and the NOT IN trap

NULL means there is no value: unknown, or not applicable. It is not 0 and it is not an empty string. Because the value is unknown, comparing anything with it (=, <>, <) gives NULL instead of true or false, and WHERE keeps only the rows where the condition is true. That is why WHERE bonus = NULL returns no rows, even when the column is full of NULLs.

To test for it, write bonus IS NULL or bonus IS NOT NULL. To replace it with something, COALESCE(bonus, 0).

Skip to the 4 exercises ↓

The table

Six people. Bo and Eli have no bonus on record (NULL); Dee's bonus is a real 0. Cy and Eli have no phone (NULL), and Bo's phone is an empty string, which looks the same in many tools and is not the same thing. Ada has no manager.

staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Live query= NULL: no error, no rows
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

What a comparison with NULL returns

A condition in SQL has three possible results: true, false and NULL (unknown). The next query puts the two tests side by side as columns, so you can see what WHERE was looking at.

Live query= NULL and IS NULL, row by row
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 6 rows
namebonusequals_nullis_null
Ada500NULL0
BoNULLNULL1
Cy300NULL0
Dee0NULL0
EliNULLNULL1
Fay200NULL0

The same thing happens with every other comparison, and that one gives wrong rows without an error. Ask for everyone whose bonus is not 500:

Live query<> leaves the NULLs out too
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 3 rows
namebonus
Cy300
Dee0
Fay200

COALESCE: a value instead of NULL

COALESCE(a, b, ...) returns the first of its arguments that isn't NULL. Use it to show a default, or to make a NULL count as 0 in arithmetic: bonus + 100 is NULL when the bonus is NULL, and COALESCE(bonus, 0) + 100 is 100.

Live queryCOALESCE on a number and on text
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 6 rows
namebonusphone
Ada500555-0101
Bo0
Cy300none
Dee0555-0104
Eli0none
Fay200555-0106

NULL or empty string

An empty string, '', is text of length zero: a value. NULL is the absence of a value. In this table phone IS NULL finds Cy and Eli and misses Bo, and phone = '' finds Bo and misses the other two. To catch both, test both, phone IS NULL OR phone = '', or turn the empty string into NULL first with NULLIF(phone, '').

One database blurs the line: Oracle's documentation says it treats a character value with a length of zero as null.

NULL in COUNT, SUM and AVG

COUNT(*) counts rows. COUNT(bonus), SUM(bonus) and AVG(bonus) skip the rows where the bonus is NULL. The one that surprises people is AVG: it divides by the number of values it found, not by the number of rows.

Live queryAggregates skip NULL
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 1 row
peoplewith_bonustotalaverageaverage_all
641000250166.67

NOT IN and NULL

x NOT IN (list) is true only when x is known to be different from every value in the list. If the list holds a NULL, "different from NULL" is unknown, so the test can never come out true. One NULL in a subquery empties the whole result, without an error.

Live queryNOT IN with a NULL in the subquery: no rows
Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1Who has no bonus on record?

Return the name of everyone whose bonus is missing (NULL).

Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Show the answer
SELECT name
FROM staff
WHERE bonus IS NULL
Result · 2 rows
name
Bo
Eli

IS NULL is the test for a missing value. = NULL returns nothing, and = 0 finds Dee, whose bonus is known to be zero.

Exercise 2Everyone who didn't get 500

Return the name and bonus of everyone whose bonus is not 500. People with no bonus on record didn't get 500 either: they must be in the result.

Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, bonus
FROM staff
WHERE bonus <> 500 OR bonus IS NULL
Result · 5 rows
namebonus
BoNULL
Cy300
Dee0
EliNULL
Fay200

bonus <> 500 is NULL for Bo and Eli, so they need their own test: OR bonus IS NULL. In SQLite, bonus IS NOT 500 does both at once.

Exercise 3NULL or empty: show none

Return each person's name and phone. Where there is no phone, whether it is NULL or an empty string, show the text none instead.

Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, COALESCE(NULLIF(phone, ''), 'none')
FROM staff
Result · 6 rows
nameCOALESCE(NULLIF(phone, ''), 'none')
Ada555-0101
Bonone
Cynone
Dee555-0104
Elinone
Fay555-0106

NULLIF(phone, '') turns the empty string into NULL, and COALESCE then replaces every NULL with none. A CASE WHEN phone IS NULL OR phone = '' THEN 'none' ELSE phone END does the same.

Exercise 4Who manages nobody?

Return the name of everyone who doesn't manage anyone: no row has their id as manager_id.

Table: staff
staff
idnamephonebonusmanager_id
1Ada555-0101500NULL
2BoNULL1
3CyNULL3001
4Dee555-010401
5EliNULLNULL4
6Fay555-01062004
Ctrl/⌘ + Enter to run
Show the answer
SELECT name
FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff WHERE manager_id IS NOT NULL)
Result · 4 rows
name
Bo
Cy
Eli
Fay

The managers are the ids that appear in manager_id: 1 and 4. Everyone else manages nobody. The subquery has to drop the NULL, or NOT IN returns no rows at all. NOT EXISTS with a subquery gives the same four names and has no such trap.

Questions people ask

Why does NULL = NULL return no rows?

Because the result of NULL = NULL is NULL, not true: two unknown values can't be said to be equal. PostgreSQL's documentation says it directly: don't write expression = NULL, use IS NULL. To compare two columns and treat two NULLs as equal, SQLite has a IS b, and PostgreSQL documents a IS NOT DISTINCT FROM b.

How do I check for NULL or an empty string?

Test both: WHERE phone IS NULL OR phone = ''. Or fold the empty string into NULL and test once: WHERE NULLIF(phone, '') IS NULL. Both return three people on this page's table.

What is the difference between COALESCE, IFNULL and NVL?

COALESCE is the standard one and takes any number of arguments: it returns the first that isn't NULL. In SQLite, IFNULL(a, b) is the same as COALESCE with two arguments. PostgreSQL's documentation describes NVL and IFNULL as similar functions used in some other database systems. If you write COALESCE, you rarely need the others.

Do COUNT, SUM and AVG ignore NULL?

COUNT(column), SUM and AVG skip NULLs; only COUNT(*) counts every row. One more case: when there is no non-NULL value at all, SUM returns NULL, not 0. Write COALESCE(SUM(bonus), 0) if you need a number.

Where do NULLs go in ORDER BY and GROUP BY?

In SQLite a NULL sorts as smaller than any other value: first with ORDER BY bonus, last with ORDER BY bonus DESC, and NULLS LAST or NULLS FIRST after the column changes that. That is SQLite's rule: check your own database's documentation before relying on it elsewhere. In GROUP BY, all the NULLs form one group.

Keep going

Lesson 4 is seven drills on subqueries and CTEs, on a shop where one order was placed by a guest and has a NULL customer. It includes COUNT(*) against COUNT(column) and the NOT IN trap from this page. It's free, like lessons 1, 2 and 3 and the level test.

Related

SQL COUNT(DISTINCT), with live queriesSQL LIKE: wildcards and multiple patternsSQL CASE WHEN: conditions, ELSE and NULLSQL JOIN: INNER and LEFT, with live queriesSQL subquery practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SQL Language Expressions (operators, IS, IN and NOT IN), SQLite documentation: SELECT (WHERE, GROUP BY, ORDER BY), SQLite documentation: Built-in Aggregate Functions, SQLite documentation: Built-In Scalar SQL Functions (coalesce, ifnull, nullif), PostgreSQL documentation: Comparison Functions and Operators, PostgreSQL documentation: Conditional Expressions (COALESCE), Oracle Database 19c SQL Language Reference: Nulls. Last checked November 2026.