Search code examples
sqlnullboolean-logicansi-sql

Standard SQL boolean operator IS vs. equals (=) operator

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

Solution

  • 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 |
+---------+---------+---------+---------+