SQL uses a three-valued logic system with TRUE, FALSE, and NULL representing an “unknown” value.
The basic logical operators AND, OR, and NOT are available. The AND and OR operators are both commutative, meaning that the left and right operands are interchangeable without affecting the output of an operation.
The outcome of every possible logical operation is evident in the following truth tables:
| a | b | a AND b | a OR b |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| TRUE | NULL | NULL | TRUE |
| NULL | TRUE | NULL | TRUE |
| FALSE | NULL | FALSE | NULL |
| NULL | FALSE | FALSE | NULL |
| NULL | NULL | NULL | NULL |
| a | NOT a | a IS NULL | a IS NOT NULL | a = NULL |
|---|---|---|---|---|
| TRUE | FALSE | FALSE | TRUE | NULL |
| FALSE | TRUE | FALSE | TRUE | NULL |
| NULL | NULL | TRUE | FALSE | NULL |
Testing if a column value is NULL should be done with a IS NULL and not with a = NULL as the latter will change value a to NULL!
Beware, the logical operators are turned into proper identifiers by double quoting to avoid clashing with the corresponding operator.
| Function | Return type | Description | Example query | Result |
|---|---|---|---|---|
"and"(a, b) | boolean | a AND b | SELECT "and"(TRUE, FALSE); | false |
"not"(a) | boolean | NOT a | SELECT "not"(TRUE); | false |
"or"(a, b) | boolean | a OR b | SELECT "or"(TRUE, FALSE); | true |
"xor"(a, b) | boolean | a OR b, but NOT a AND b | SELECT "xor"(TRUE, TRUE); | false |