<< Click to Display Table of Contents >> Logical Operators 
Logical operators let you establish conditions and test their results.
Operator 
Unary/Binary 
Description 
Operands 
NOT 
U 
Logical negation 
Boolean 
AND 
B 
Conjunction 
Boolean 
OR 
B 
Disjunction 
Boolean 
IS 
B 
Logical assertion 
Boolean 
IS NOT UNKNOWN 
U 
Negated unknown comparison: <expr> IS NOT UNKNOWN 
Boolean 
IS NULL 
U 
Null comparison: <expr> IS NULL 
Any 
IS NOT NULL 
U 
Negated null comparison: <expr> IS NOT NULL 
Any 
= 
B 
Equality 
Any 
!= 
B 
Inequality 
Any 
<> 
B 
Inequality 
Any 
> 
B 
Greater than 
Ordered types (Numeric, String, Date, Time) 
>= 
B 
Greater than or equal to (not less than) 
Ordered types 
< 
B 
Less than 
Ordered types 
<= 
B 
Less than or equal to (not more than) 
Ordered types 
BETWEEN 
Ternary 
Range comparison: col1 BETWEEN expr1 AND expr2 
Ordered types 
IS DISTINCT FROM 
B 
Distinction 
Any 
B 
Negated distinction 
Any 
SQL boolean values have three possible states rather than the usual two: TRUE, FALSE, and UNKNOWN, the last of which is equivalent to a boolean NULL. TRUE and FALSE operands generally function according to normal twostate boolean logic, but additional rules apply when pairing them with UNKNOWN operands, as the tables that follow will show.
Note: UNKOWN represents "maybe TRUE, maybe FALSE" or, to put it another way, "not definitely TRUE and not definitely FALSE." This understanding may help you clarify why some of the expressions in the tables evaluate as they do.
Negation (NOT):
Operation 
Result 
NOT TRUE 
FALSE 
NOT FALSE 
TRUE 
NOT UNKNOWN 
UNKNOWN 
Conjunction (AND):
Operation 
Result 
TRUE AND TRUE 
TRUE 
TRUE AND FALSE 
FALSE 
TRUE AND UNKNOWN 
UNKNOWN 
FALSE AND TRUE 
FALSE 
FALSE AND FALSE 
FALSE 
FALSE AND UNKNOWN 
FALSE 
UNKNOWN AND TRUE 
UNKNOWN 
UNKNOWN AND FALSE 
FALSE 
UNKNOWN AND UNKNOWN 
UNKNOWN 
Disjunction (OR):
Operation 
Result 
TRUE OR TRUE 
TRUE 
TRUE OR FALSE 
TRUE 
TRUE OR UNKNOWN 
TRUE 
FALSE OR TRUE 
TRUE 
FALSE OR FALSE 
FALSE 
FALSE OR UNKNOWN 
UNKNOWN 
UNKNOWN OR TRUE 
TRUE 
UNKNOWN OR FALSE 
UNKNOWN 
UNKNOWN OR UNKNOWN 
UNKNOWN 
Assertion (IS):
Operation 
Result 
TRUE IS TRUE 
TRUE 
TRUE IS FALSE 
FALSE 
TRUE IS UNKNOWN 
FALSE 
FALSE IS TRUE 
FALSE 
FALSE IS FALSE 
TRUE 
FALSE IS UNKNOWN 
FALSE 
UNKNOWN IS TRUE 
FALSE 
UNKNOWN IS FALSE 
FALSE 
UNKNOWN IS UNKNOWN 
TRUE 
Operation 
Result 
TRUE IS NOT UNKNOWN 
TRUE 
FALSE IS NOT UNKNOWN 
TRUE 
UNKNOWN IS NOT UNKNOWN 
FALSE 
IS NOT UNKNOWN is a special operator in and of itself. The expression "x IS NOT UNKNOWN" is equivalent to "(x IS TRUE) OR (x IS FALSE)", not "x IS (NOT UNKNOWN)". Thus, substituting in the table above:
x 
Operation 
Result 

Result of substituting for x in "(x IS TRUE) OR (x IS FALSE)" 
TRUE 
TRUE IS NOT UNKNOWN 
TRUE 
becomes 
"(TRUE IS TRUE) OR (TRUE IS FALSE)"  hence TRUE 
FALSE 
FALSE IS NOT UNKNOWN 
TRUE 
becomes 
"(FALSE IS TRUE) OR (FALSE IS FALSE)"  hence TRUE 
UNKNOWN 
UNKNOWN IS NOT UNKNOWN 
FALSE 
becomes 
"(UNKNOWN IS TRUE) OR (UNKNOWN IS FALSE)"  hence FALSE, since UNKNOWN is neither TRUE not FALSE 
Since IS NOT UNKNOWN is a special operator, the operations above are not transitive around the word IS:
Operation 
Result 
NOT UNKNOWN IS TRUE 
FALSE 
NOT UNKNOWN IS FALSE 
FALSE 
NOT UNKNOWN IS UNKNOWN 
TRUE 
IS NULL and IS NOT NULL
Operation 
Result 
UNKNOWN IS NULL 
TRUE 
UNKNOWN IS NOT NULL 
FALSE 
NULL IS NULL 
TRUE 
NULL IS NOT NULL 
FALSE 
IS DISTINCT FROM and IS NOT DISTINCT FROM
Operation 
Result 
UNKNOWN IS DISTINCT FROM TRUE 
TRUE 
UNKNOWN IS DISTINCT FROM FALSE 
TRUE 
UNKNOWN IS DISTINCT FROM UNKNOWN 
FALSE 
UNKNOWN IS NOT DISTINCT FROM TRUE 
FALSE 
UNKNOWN IS NOT DISTINCT FROM FALSE 
FALSE 
UNKNOWN IS NOT DISTINCT FROM UNKNOWN 
TRUE 
Informally, "x IS DISTINCT FROM y" is similar to "x <> y", except that it is true even when either x or y (but not both) is NULL. "DISTINCT FROM" is the opposite of identical, whose usual meaning is that a value (true, false, or unknown) is identical to itself, and distinct from every other value. The IS and IS NOT operators treat UNKOWN in a special way, because it represents "maybe TRUE, maybe FALSE".
Other logical operators:
For all other operators, passing a NULL or UNKNOWN operand will cause the result to be UNKNOWN (which is the same as NULL).
Operation 
Result 
TRUE AND CAST( NULL AS BOOLEAN) 
UNKNOWN 
FALSE AND CAST( NULL AS BOOLEAN) 
FALSE 
1 > 2 
FALSE 
1 < 2 
TRUE 
'foo' = 'bar' 
FALSE 
'foo' <> 'bar' 
TRUE 
'foo' <= 'bar' 
FALSE 
'foo' <= 'bar' 
TRUE 
3 BETWEEN 1 AND 5 
TRUE 
1 BETWEEN 3 AND 5 
FALSE 
3 BETWEEN 3 AND 5 
TRUE 
5 BETWEEN 3 AND 5 
TRUE 
1 IS DISTINCT FROM 1.0 
FALSE 
CAST( NULL AS INTEGER ) IS NOT DISTINCT FROM CAST (NULL AS INTEGER) 
TRUE 