Logical functions and operators

Logical functions and operators dinther Wed, 01/22/2020 - 15:18

SQL uses a three-valued logic system with true, false, and null which represents “missing”.

Logical operators

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
true false false true
false true false true
null null true false

 

 

 

 

Logical functions

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