Skip to content
Tutorials Center
My learning

Topics

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.

SQL Comparisons involving 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
SQL AND, OR and NOT with 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.

SQL Rows that disappear and the NOT IN trap ยท database: company

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, MAX and COUNT(column) ignore NULL inputs. COUNT(*) counts rows. If every input is NULL, SUM, AVG, MIN and MAX return NULL while COUNT returns 0.
  • Grouping and DISTINCT treat all NULLs as one group, even though NULL = NULL is 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.
  • CHECK constraints pass on unknown. CHECK (price > 0) accepts a NULL price, so add NOT NULL if needed.
SQL NULL in aggregates ยท database: company

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. See IS NULL and IS NOT NULL.
  • Replace NULL for display or math with COALESCE(); turn a value into NULL with NULLIF().
  • Prefer NOT EXISTS to NOT IN when the subquery column may be NULL.
  • Declare columns NOT NULL whenever a value is always required. Fewer NULLs means fewer three-valued surprises. See NOT NULL.
  • Outer joins create NULLs for unmatched rows; do not confuse them with stored NULLs.