Data · SQL
How to check for NULL in SQL
5 customers, 2 of them in Denver: how many come out with city <> 'Denver'?
And if one of the other 3 has no city?
From an unknown city to a query that returns nothing, in five steps:
Why does WHERE column = NULL return no rows?
Out of 40 customers, 9 with no city, city = NULL returns no rows, and city IS NULL returns 9.
Let’s take five customers, two of them with no city.
SELECT name FROM customers WHERE city = NULL
| name | city |
|---|---|
| Emma | Boston |
| Noah | NULL |
| Mia | Denver |
| Liam | NULL |
| Ava | Austin |
| name |
|---|
| no rows |
On Noah’s row, what does
city = NULLevaluate to?NULL means “unknown value.” So an unknown city is compared to an unknown value.
And on Emma’s row, where the city is Boston?
Boston is compared to an unknown value.
SELECT name FROM customers WHERE city IS NULL
customers name city Emma Denver Noah Boston Mia NULL Liam Miami SELECT name FROM customers WHERE city IS NOT NULL
customers name city Emma NULL Noah Austin Mia Boston Liam NULL Ava Denver Jack Dallas
Why doesn’t <> return rows with NULL?
Out of 40 customers, 6 in Denver and 9 with no city, city <> 'Denver' returns 25 rows, not 34: on a row with no city, the condition is unknown.
Let’s take six customers, two of them with no city.
SELECT name FROM customers WHERE city <> 'Denver'
| name | city |
|---|---|
| Emma | Boston |
| Noah | NULL |
| Mia | Denver |
| Liam | Austin |
| Ava | NULL |
| Jack | Boston |
| name |
|---|
| Emma |
| Liam |
| Jack |
On Noah’s row, what does
city <> 'Denver'evaluate to?Noah’s city is NULL: it’s unknown.
SELECT name FROM customers WHERE city <> 'Miami'
customers name city Emma Miami Noah Boston Mia NULL Liam Miami Ava Austin SELECT name FROM customers WHERE city <> 'Denver' AND city <> 'Boston'
customers name city Emma NULL Noah Denver Mia Boston Liam Dallas Ava NULL Jack Denver
Why does NOT IN return nothing when the list contains NULL?
city NOT IN ('Denver', NULL) reads as city <> 'Denver' AND city <> NULL. A customer in Dallas doesn’t come out: the second half isn’t true.
Let’s take five customers, two of them with no city.
SELECT name
FROM customers
WHERE city NOT IN ('Austin', NULL)| name | city |
|---|---|
| Emma | Boston |
| Noah | NULL |
| Mia | Austin |
| Liam | Miami |
| Ava | NULL |
| name |
|---|
| no rows |
On Emma’s row, what does
city <> NULLevaluate to?Emma’s city is Boston. It is compared to an unknown value.
So what does
city NOT IN ('Austin', NULL)evaluate to on this row?Boston isn’t Austin.
ANDis true only if both of its halves are true.
SELECT name FROM customers WHERE city NOT IN ('Denver', 'Miami')customers name city Emma Boston Noah NULL Mia Denver Liam Dallas Ava Miami SELECT name FROM customers WHERE city IN ('Austin', NULL)customers name city Emma Austin Noah Denver Mia NULL Liam Austin Ava Boston Jack NULL
How do you keep NULL rows in a WHERE clause?
To keep the customers with no city too, you add OR city IS NULL. Out of 40 customers, 6 in Denver and 9 with no city, city <> 'Denver' OR city IS NULL returns 34 rows.
Let’s take six customers, two of them in Denver and two with no city.
SELECT name FROM customers WHERE city <> 'Denver' OR city IS NULL
| name | city |
|---|---|
| Emma | Denver |
| Noah | NULL |
| Mia | Boston |
| Liam | Denver |
| Ava | NULL |
| Jack | Austin |
| name |
|---|
| Noah |
| Mia |
| Ava |
| Jack |
On Noah’s row, what does the whole condition evaluate to?
city <> 'Denver'is unknown there.ORis true as soon as one of its two halves is true.
SELECT name FROM customers WHERE city <> 'Austin' OR city IS NULL
customers name city Emma Austin Noah NULL Mia Boston Liam Austin Ava Miami SELECT name FROM customers WHERE city NOT IN ('Miami', 'Dallas') OR city IS NULLcustomers name city Emma NULL Noah Miami Mia Denver Liam Boston Ava NULL Jack Dallas
How do you quickly spot a NULL trap in a SQL query?
It goes faster if you read the rows with no city first: any comparison is unknown on them, and only IS NULL and IS NOT NULL are true or false there.
SELECT name FROM customers WHERE city <> 'Boston'
customers name city Emma Boston Noah NULL Mia Miami Liam Boston Ava Dallas SELECT name FROM customers WHERE city NOT IN ('Dallas', NULL)customers name city Emma Miami Noah Dallas Mia NULL Liam Boston SELECT name FROM customers WHERE city IS NOT NULL
customers name city Emma NULL Noah Denver Mia NULL Liam Austin Ava Denver SELECT name FROM customers WHERE city NOT IN ('Denver', 'Boston') OR city IS NULLcustomers name city Emma Boston Noah NULL Mia Miami Liam Denver Ava Austin Jack NULL