NULL: Missing Values and Three-Valued Logic
Understand NULL as an unknown value, see why equals and not-equals miss rows, and use IS NULL, COALESCE and careful conditions.
In this lesson you will
- Explain NULL as unknown, not zero
- Test for NULL with IS NULL
- Spot rows lost by not-equal tests
- Use COALESCE and know its limits
About 28 minutes
The customer who vanished
Maya wants to email every customer who does not live in Leeds, as a test of a local event. She writes a query for cities that are not Leeds and gets three names. She has six customers, two live in Leeds, so she expects four. Where is the fourth? The answer is Femi, whose city is NULL, and the way NULL behaves is the most famous trap in SQL. Understanding it now will save you many strange results later.
NULL means unknown, not zero
A NULL marks a missing or unknown value. It is not the number zero, not an empty piece of text and not the word null. It says ‘nothing is stored here’. Femi’s city may be unknown, or may simply have never been entered. Because the value is unknown, any ordinary comparison with it has an unknown answer too. Is an unknown city equal to Leeds? Unknown. Is it different from Leeds? Also unknown. SQL therefore has three-valued logic: a condition can be true, false or unknown. And WHERE keeps only rows for which the condition is true. Rows where it is false and rows where it is unknown are both dropped.
Testing for NULL properly
To ask whether a value is missing, use IS NULL, or IS NOT NULL for the opposite. Writing = NULL is a classic mistake. It never matches anything, not even a NULL, because comparing an unknown with an unknown is unknown, not true. Some tools have options that change this behaviour, which is a good reason to stick to the standard form.
Six queries and a trap
The queries below show the trap from several sides. Query 1 asks for stock below 10. Query 2 and query 3 compare IS NULL with = NULL. Query 4 shows COALESCE, which swaps a NULL for a value you choose. Queries 5 and 6 are Maya’s problem, first wrong and then fixed. Query 7 shows a quieter trap with NOT IN.
-- 1
SELECT name, stock FROM products WHERE stock < 10
-- 2
SELECT name FROM products WHERE stock IS NULL
-- 3
SELECT name FROM products WHERE stock = NULL
-- 4
SELECT name, COALESCE(stock, 0) AS stock_or_zero FROM products WHERE category = 'care'
-- 5
SELECT name FROM customers WHERE city <> 'Leeds'
-- 6
SELECT name FROM customers WHERE city <> 'Leeds' OR city IS NULL
-- 7
SELECT name FROM customers WHERE city NOT IN ('Leeds', NULL)
-- 1
name | stock
Orchid | 5
Glass pot | 0
-- 2
name
Mist spray
-- 3
name
(no rows)
-- 4
name | stock_or_zero
Plant food | 40
Mist spray | 0
-- 5
name
Ben
Dev
Ella
-- 6
name
Ben
Dev
Ella
Femi
-- 7
name
(no rows)
Query 1 returned 2 products, Orchid, Glass pot. It left out the Mist spray, whose stock is NULL: its unknown stock is not known to be below 10. Query 2, with IS NULL, found it. Query 3, with = NULL, returned 0 rows, which is exactly the trap. Query 4 used COALESCE to show the missing stock as 0. That is a decision, not a fact: the mist spray’s stock is unknown, and showing 0 is only sensible if you agree that ‘unknown’ means ‘none’. Maya’s own problem is query 5. It returned 3 names, Ben, Dev, Ella, and Femi is missing. Adding OR city IS NULL in query 6 brought the answer to 4 names: Ben, Dev, Ella, Femi.
Query 7 is the quietest trap of all. It asks for customers whose city is not in a list that happens to contain a NULL, and it returned 0 rows, although you might expect Ben, Dev, Ella and Femi. The list makes every test unknown for rows that fail to match Leeds, so none can be true. Whenever a NOT IN list or a subquery might contain NULL, take care. A safer habit is to remove NULLs from the list first, or to use the NOT EXISTS pattern that a later course will show.
Rules of thumb for NULL
- Test for NULL with IS NULL or IS NOT NULL, never with an equals sign.
- When you filter with ‘not equal’, ask: what should happen to rows where the value is missing?
- Use COALESCE to display a default, and say so when you do.
- Count how many NULLs a column has before trusting any average or total built on it.
- Where you design a table, allow NULL only when ‘unknown’ is a real possibility.
Writing WHERE city <> ‘Leeds’ and trusting the result. Rows with a missing city silently disappear, and nothing warns you.
The customers and products are made up. Three-valued logic is part of standard SQL, and the main databases follow it, although a few offer options that change how comparisons with NULL behave. Check your documentation, and prefer IS NULL.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does NULL mean?
- The number zero
- An empty piece of text
- No value is stored, so the value is unknown
- An error message
NULL is a marker for missing information.
What does WHERE city = NULL return?
- Every row with no city
- Every row
- An error in all databases
- No rows, because the comparison is never true
Query 3 returned no rows.
How do you find rows with a missing city?
- WHERE city = NULL
- WHERE city IS NULL
- WHERE city = 0
- WHERE city = empty
IS NULL is the standard test.
Why was Femi missing from the not-equal result?
- Femi is not a customer
- WHERE ignores names
- An unknown city is not known to differ from Leeds, so the test is unknown
- The query had a typo
WHERE keeps only rows where the test is true.
What does COALESCE do?
- Replaces a NULL with a value you choose
- Sorts the rows
- Deletes NULL rows
- Counts the rows
It swapped the missing stock for 0 in query 4.
Summary
- NULL means unknown, and comparisons with it are unknown.
- WHERE keeps only rows where the test is true.
- Use IS NULL and IS NOT NULL.
- Beware not-equal tests and NOT IN lists with NULLs.
Questions
Is NULL the same as an empty string?
No. An empty string is a known piece of text with no characters. NULL means no value is known. Some databases treat them differently from others, so check yours.
Should I avoid NULL entirely?
Not always. It is the right choice when a value really can be unknown. But decide deliberately and handle it in every query that touches the column.
Do all databases follow three-valued logic?
The main ones follow the standard, so comparisons with NULL are unknown. A few have options that change this, so stick to IS NULL and check your documentation.
