Boolean-predicate

The Boolean predicate retrieves rows where the value of an expression is true, false, or unknown (null).

Syntax

expression IS [NOT] TRUE

expression IS [NOT] FALSE

expression IS [NOT] UNKNOWN

Semantics

A Boolean predicate always return true or false, never a null value, even when the operand is null. A null input is treated as the value UNKNOWN.

Notes

• Do not confuse the boolean-predicate with Boolean Operators or the Boolean data type, which can have only two values: true and false.

  Syntax

  [ AND | OR | NOT ]

  Semantics

  SQL uses a three-valued Boolean logic where the null value represents "unknown."

  a	b	a AND b	a OR b
  TRUE	TRUE	TRUE	TRUE
  TRUE	FALSE	FALSE	TRUE
  TRUE	NULL	NULL	TRUE
  FALSE	FALSE	FALSE	FALSE
  FALSE	NULL	FALSE	NULL
  NULL	NULL	NULL	NULL

  a	NOT a
  TRUE	FALSE
  FALSE	TRUE
  NULL	NULL

  Notes

  • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
  • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
• IS UNKNOWN and IS NOT UNKNOWN are effectively the same as the NULL-predicate, except that the input expression does not have to be a single column value. To check a single column value for NULL, use the NULL-predicate.