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

That’s a new one for me.

If I read that correctly 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.

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

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)