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 sql equal operator is used to check whether two expressions are equal or not. If it's equal, the condition will be true and will return matched records. The sql not equal operator is used to check whether two expressions are equal or not.
SQL Logical OR operator Logical OR compares two Booleans as expression and returns TRUE when either of the conditions is TRUE and returns FALSE when both are FALSE.
There is boolean data type in SQL Server. Its values can be TRUE , FALSE or UNKNOWN . However, the boolean data type is only the result of a boolean expression containing some combination of comparison operators (e.g. = , <> , < , >= ) or logical operators (e.g. AND , OR , IN , EXISTS ).
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 | +---------+---------+---------+---------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With