SQL uses a three-valued logic system with true, false, and null which represents “missing”.
The basic logical operators are available: AND, OR, NOT. The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. Observe 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 |
Note that testing if a column value is NULL should be done with a IS NULL
and not with a = NULL as the equals (=) comparison always results 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 | Result |
|---|---|---|---|---|
| "all"(a boolean, b boolean, c boolean) | boolean | if c is null then true else if a is false then false else if (b is true or c is true) then null else true | "all"(true, false, true) | null |
| "and"(a boolean, b boolean) | boolean | a AND b | "and"(true, false) | false |
| "any"(a boolean, b boolean, c boolean) | boolean | if c is null then false else if a is true then true else if (b is true or c is true) then null else false | "any"(true, false, true) | true |
| "not"(a boolean) | boolean | NOT a | "not"(true) | false |
| "or"(a boolean, b boolean) | boolean | a OR b | "or"(true, false) | true |
| "xor"(a boolean, b boolean) | boolean | a OR b, but NOT, a AND b | "xor"(true, true) | false |