Relational and Logical Expressions

A logical expression or relational expression returns TRUE, FALSE, or UNKNOWN.

The general patterns are:

expression comparison_operator [any | all] expression

expression [not] in expression

[not] exists expression

expression [not] between expression and expression

expression [not] like "match_string" [escape "escape_character"]

not expression like "match_string" [escape "escape_character"]

expression is [not] null

not logical_expression

logical_expression {and | or} logical_expression

any is used with <, >, =, and a subquery. It returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement.
all is used with < or > and a subquery. It returns results when all values retrieved in the subquery are less than (<) or greater than (>) the value in the where or having clause of the outer statement.
in returns results when any value returned by the second expression matches the value in the first expression. The second expression must be a subquery or a list of values enclosed in parentheses. in is equivalent to = any.
and connects two expressions and returns results when both are true.
or connects two or more conditions and returns results when either condition is true.

When more than one logical operator is used in a statement, and is evaluated before or. Use parentheses to change the order of execution.

This truth table shows the results of logical operations, including those that involve null values:

and	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
NULL	UNKNOWN	FALSE	UNKNOWN
or	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
NULL	TRUE	UNKNOWN	UNKNOWN
not
TRUE	FALSE
FALSE	TRUE
NULL	UNKNOWN

The result UNKNOWN indicates that one or more of the expressions evaluates to NULL, and that the result of the operation cannot be determined to be either TRUE or FALSE.