Null Arithmetic and Comparisons in SQL


  • when operators have a NULL operand,
    • the result is NULL
  • when a WHERE clause evaluates to NULL, row is not selected

IS NULL Operator

  • comparison operators cannot be used to select NULL values
  • use IS NULL and IS NOT NULL operators to select NULL values

NULL Logic

  • Expressions can be TRUE, FALSE, or NULL
  • NULL indicates the value of the expression is uncertain
  • value of logical expressions is contained in truth tables

MySQL Truth Table

xyx AND yx OR y
TRUENULLNULLTRUE
NULLTRUENULLTRUE
FALSENULLFALSENULL
NULLFALSEFALSENULL
NULLNULLNULLNULL
—
xNOT x
NULLNULL