Data · SQL

How to check for NULL in SQL

Published

5 customers, 2 of them in Denver: how many come out with city <> 'Denver'?

From an unknown city to a query that returns nothing, in five steps:

  1. Test for NULL with IS NULL
  2. Spot the NULL rows that <> leaves out
  3. Read a NOT IN whose list contains NULL
  4. Keep the NULL rows with OR IS NULL
  5. Practice

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
customers
namecity
EmmaBoston
NoahNULL
MiaDenver
LiamNULL
AvaAustin
result
name
no rows
  1. On Noah’s row, what does city = NULL evaluate to?

    NULL means “unknown value.” So an unknown city is compared to an unknown value.

  2. And on Emma’s row, where the city is Boston?

    Boston is compared to an unknown value.

  3. WHERE keeps a row only if the condition is true on it. The other rows read like Emma’s and Noah’s.

  1. SELECT name
    FROM customers
    WHERE city IS NULL
    customers
    namecity
    EmmaDenver
    NoahBoston
    MiaNULL
    LiamMiami

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'
customers
namecity
EmmaBoston
NoahNULL
MiaDenver
LiamAustin
AvaNULL
JackBoston
result
name
Emma
Liam
Jack
  1. On Noah’s row, what does city <> 'Denver' evaluate to?

    Noah’s city is NULL: it’s unknown.

  2. A row comes out if its city is known and isn’t Denver.

    rows

  1. SELECT name
    FROM customers
    WHERE city <> 'Miami'
    customers
    namecity
    EmmaMiami
    NoahBoston
    MiaNULL
    LiamMiami
    AvaAustin

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)
customers
namecity
EmmaBoston
NoahNULL
MiaAustin
LiamMiami
AvaNULL
result
name
no rows
  1. On Emma’s row, what does city <> NULL evaluate to?

    Emma’s city is Boston. It is compared to an unknown value.

  2. So what does city NOT IN ('Austin', NULL) evaluate to on this row?

    Boston isn’t Austin. AND is true only if both of its halves are true.

  3. The other rows read the same way: each one also compares its city to NULL.

  1. SELECT name
    FROM customers
    WHERE city NOT IN ('Denver', 'Miami')
    customers
    namecity
    EmmaBoston
    NoahNULL
    MiaDenver
    LiamDallas
    AvaMiami

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
customers
namecity
EmmaDenver
NoahNULL
MiaBoston
LiamDenver
AvaNULL
JackAustin
result
name
Noah
Mia
Ava
Jack
  1. On Noah’s row, what does the whole condition evaluate to?

    city <> 'Denver' is unknown there. OR is true as soon as one of its two halves is true.

  2. A row comes out if its city isn’t Denver, whether the city is known or not.

    rows

  1. SELECT name
    FROM customers
    WHERE city <> 'Austin'
       OR city IS NULL
    customers
    namecity
    EmmaAustin
    NoahNULL
    MiaBoston
    LiamAustin
    AvaMiami

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.

  1. SELECT name
    FROM customers
    WHERE city <> 'Boston'
    customers
    namecity
    EmmaBoston
    NoahNULL
    MiaMiami
    LiamBoston
    AvaDallas