SQL Reference ยท Operators and Predicates
SQL NULL and three-valued logic
How NULL behaves in SQL: unknown values, TRUE/FALSE/UNKNOWN truth tables for AND, OR and NOT, comparisons, WHERE, NOT IN, aggregates, sorting and constraints.
NULL is not a value like 0 or ''. It is a marker meaning "no value here" or "unknown". Because it stands for something unknown, SQL cannot always say whether a condition involving it is true or false. A comparison therefore has three possible outcomes: TRUE, FALSE and UNKNOWN (which SQL represents as NULL). This is called three-valued logic, and it explains most NULL surprises.
Remember the rule that ties it together: a WHERE clause (and ON, HAVING, WHEN) keeps a row only when its condition is TRUE. FALSE and UNKNOWN rows are both discarded.
Comparisons with NULL
Any ordinary comparison with NULL is UNKNOWN, including NULL = NULL.
Press Run (or Ctrl + Enter) to see the result here.
The first four columns show NULL (unknown); only IS NULL and IS NOT NULL return a definite answer. Use them, or IS DISTINCT FROM when both sides may be NULL.
Truth tables
AND returns FALSE if either side is FALSE, otherwise UNKNOWN if either side is unknown. OR returns TRUE if either side is TRUE. NOT UNKNOWN is UNKNOWN.
a |
b |
a AND b |
a OR b |
|---|---|---|---|
| TRUE | UNKNOWN | UNKNOWN | TRUE |
| FALSE | UNKNOWN | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
a |
NOT a |
|---|---|
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
Press Run (or Ctrl + Enter) to see the result here.
(SQLite writes true and false as 1 and 0, and shows unknown as NULL.)
NULL in filters, NOT IN and CASE
A filter such as WHERE budget <> 100000 silently drops rows whose budget is NULL, because the comparison is unknown. Likewise, x NOT IN (1, NULL) can never be true, so a subquery that returns even one NULL makes NOT IN match nothing.
Press Run (or Ctrl + Enter) to see the result here.
CASE follows the same rule: a WHEN condition that is unknown counts as not matched, so the ELSE branch runs. A simple CASE x WHEN NULL never matches; write WHEN x IS NULL instead.
NULL in aggregates, sorting and constraints
- Aggregates skip
NULL.SUM,AVG,MIN,MAXandCOUNT(column)ignoreNULLinputs.COUNT(*)counts rows. If every input isNULL,SUM,AVG,MINandMAXreturnNULLwhileCOUNTreturns0. - Grouping and
DISTINCTtreat allNULLs as one group, even thoughNULL = NULLis unknown. - Sorting:
NULLs come first when ascending in SQLite, MySQL and SQL Server; last in PostgreSQL and Oracle. - Unique constraints allow several
NULLs in SQLite, PostgreSQL, MySQL and Oracle (for single-column keys); SQL Server allows just one. CHECKconstraints pass on unknown.CHECK (price > 0)accepts aNULLprice, so addNOT NULLif needed.
Press Run (or Ctrl + Enter) to see the result here.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
Empty string '' is NULL |
No | No | No | No | Yes |
IS DISTINCT FROM |
Yes | Yes | <=> |
2022+ | 23ai+ |
Concatenation with NULL (||/+) |
NULL |
NULL |
CONCAT(): NULL |
+: NULL by default |
Treated as '' |
NULL default sort position (ascending) |
First | Last | First | First | Last |
Usage notes
- Test with
IS NULL, never= NULL. SeeIS NULL and IS NOT NULL. - Replace
NULLfor display or math withCOALESCE(); turn a value intoNULLwithNULLIF(). - Prefer
NOT EXISTStoNOT INwhen the subquery column may beNULL. - Declare columns
NOT NULLwhenever a value is always required. FewerNULLs means fewer three-valued surprises. SeeNOT NULL. - Outer joins create
NULLs for unmatched rows; do not confuse them with storedNULLs.