On the Wikipedia page for SQL there are some truth tables about boolean logic in SQL. [1] The Wikipedia page seems to source the SQL:2003 standard.
The truth table for the equals operator (=) is different from the IS operator from the SQL:2003 draft.
Also, the Wikipedia article notes that "IS NULL" (<null predicate>) is a special case.
In the SQL:2003 it seems that there is an "IS" opeartor which is a regular operator like AND, NOT and OR. However, the <null predicate> is still there.
Why is the <null predicate> there when the IS is a regular boolean operator? Is it to make sure you can use the "IS NULL" construct with non-boolean values without type coersion? Is it discouraged to use "=NULL"?
Does the SQL:2011 standard work differently?
[1]: Wikipedia on SQL
[2]: SQL:2011 draft PDF page 335
[3]: SQL:2003 draft PDF page 397
The Modern SQL page on three valued logic mentions these and says F571
was added as an optional feature in SQL:1999
.
The <boolean value expression>
grammar defines three predicates solely for use with the boolean
datatype IS TRUE
, IS FALSE
, IS UNKNOWN
.
These differ from their equality counterparts in that they only evaluate to True
or False
. Never to Unknown
. i.e. UNKNOWN = TRUE
would evaluate to UNKNOWN
but UNKNOWN IS TRUE
evaluates to False
.
A case where they are useful would be when wanting to return rows where <some_expression>
is either true
or unknown
.
In products that support this syntax this can be done without needing to repeat <some_expression>
by just writing
WHERE <some_expression> IS NOT FALSE
The full truth tables for IS
and =
are below.
+---------+-------+-------+---------+
| IS | TRUE | FALSE | UNKNOWN |
+---------+-------+-------+---------+
| TRUE | TRUE | FALSE | FALSE |
| FALSE | FALSE | TRUE | FALSE |
| UNKNOWN | FALSE | FALSE | TRUE |
+---------+-------+-------+---------+
As opposed to
+---------+---------+---------+---------+
| = | TRUE | FALSE | UNKNOWN |
+---------+---------+---------+---------+
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | TRUE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
+---------+---------+---------+---------+