Pointer Walk

NULL and Three-Valued Logic

NULL = NULL is neither true nor false in SQL. Why the answer is unknown, and what IS NULL is for.

DatabasesBeginner6 min read

NULL is not a value

In SQL, NULL does not mean zero, and it does not mean an empty string. It means "there is no value here: we do not know it."

That one idea explains every surprise on this page. If you do not know what something is, you cannot say what it equals.

Example

Two sealed envelopes

Two envelopes each contain a number you cannot see. Are the numbers equal?

You cannot answer "yes". You cannot answer "no" either. The only honest answer is "I do not know".

That is what SQL says for NULL = NULL.

Three answers, not two

Most programming languages have two truth values. SQL has three:

  • TRUE
  • FALSE
  • UNKNOWN

Any comparison that involves a NULL gives UNKNOWN. And here is the rule that causes the trouble:

A WHERE clause keeps a row only when the answer is TRUE. FALSE rows are dropped. UNKNOWN rows are dropped too.

Looking for NULLs the wrong way

The table has four orders. Two used a coupon and two did not, so their coupon is NULL. Try to find the ones with no coupon:

WHERE coupon = NULLInteractive
idcouponWHERE saysreturned?
1SAVE10
2NULL
3FREESHIP
4NULL

Start. The database checks WHERE coupon = NULL against each row. A row is returned only if the answer is TRUE.

Rows returned
0

Nothing comes back. The comparison gave UNKNOWN for every row, including the rows that really are NULL.

Looking for NULLs the right way

SQL has a separate test that does not compare values. It simply asks whether a value is present.

WHERE coupon IS NULLInteractive
idcouponWHERE saysreturned?
1SAVE10
2NULL
3FREESHIP
4NULL

Start. The database checks WHERE coupon IS NULL against each row. A row is returned only if the answer is TRUE.

Rows returned
0

IS NULL and IS NOT NULL always answer TRUE or FALSE. They are the only correct way to test for NULL.

The rows that vanish

The first trap is easy to learn. This one is worse, because the query looks right and returns a plausible result. Find every order that did not use the coupon SAVE10:

WHERE coupon <> 'SAVE10'Interactive
idcouponWHERE saysreturned?
1SAVE10
2NULL
3FREESHIP
4NULL

Start. The database checks WHERE coupon <> 'SAVE10' against each row. A row is returned only if the answer is TRUE.

Rows returned
0

Three orders did not use SAVE10. One row came back. The two orders with no coupon at all were dropped, because "is an unknown value different from SAVE10?" is UNKNOWN.

To include them, say so:

sql
SELECT * FROM orders
WHERE coupon <> 'SAVE10' OR coupon IS NULL;

NOT IN with a NULL in the list

NOT IN (a, b) is shorthand for "different from a AND different from b". Put a NULL in the list and one of those comparisons is always UNKNOWN, so the whole condition can never be TRUE.

WHERE coupon NOT IN ('SAVE10', NULL)Interactive
idcouponWHERE saysreturned?
1SAVE10
2NULL
3FREESHIP
4NULL

Start. The database checks WHERE coupon NOT IN ('SAVE10', NULL) against each row. A row is returned only if the answer is TRUE.

Rows returned
0

Nobody writes a NULL in the list on purpose. It arrives through a subquery:

sql
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If a single order has a NULL customer_id, this returns no rows at all. Use NOT EXISTS instead, which does not have the problem.

WHERE coupon = NULLInteractive
WHERE
idcouponWHERE saysreturned?
1SAVE10
2NULL
3FREESHIP
4NULL

Start. The database checks WHERE coupon = NULL against each row. A row is returned only if the answer is TRUE.

Rows returned
0

How the three values combine

ExpressionResultWhy
NULL = NULLUNKNOWNTwo unknowns may or may not be equal
NULL <> 5UNKNOWNAn unknown may or may not be 5
TRUE AND UNKNOWNUNKNOWNIt depends on the unknown part
FALSE AND UNKNOWNFALSEOne FALSE is enough to make AND false
TRUE OR UNKNOWNTRUEOne TRUE is enough to make OR true
NOT UNKNOWNUNKNOWNThe opposite of "do not know" is still "do not know"
NULL + 1NULLArithmetic with an unknown is unknown

Other places NULL behaves differently

  • COUNT(*) counts rows. COUNT(column) skips rows where that column is NULL.
  • SUM and AVG ignore NULLs, so an average is over the rows that have a value.
  • GROUP BY and DISTINCT treat all NULLs as one group, even though they are not "equal".

Key takeaways

  • NULL means "no value, unknown". It is not zero and not an empty string.
  • Comparing anything with NULL gives UNKNOWN, a third truth value.
  • WHERE keeps only TRUE rows, so UNKNOWN rows silently disappear.
  • Test for NULL with IS NULL and IS NOT NULL, never with = or <>.
  • A NULL in a NOT IN list makes the query return nothing. Use NOT EXISTS.

Your progress

Check your progress

0%

  1. Read the page50% of this page

    When you have gone through the page and its animations, tick it off.

  2. Take the quiz50% of this page

    6 questions, from easy to hard. Take it as often as you like: the questions change, and only your best score counts.

Saved on this device only. See all your progress