Logical Operators: AND, OR, and NOT

Syntax

expression ::= or_expression

or_expression ::= and_expression | (or_expression OR and_expression)

and_expression ::= not_expression | (and_expression AND not_expression)

not_expression ::= [NOT] is_null_expression

Semantics

The binary AND and OR operators and the unary NOT operator have the usual semantics. Their operands are conditional expressions, which must have type BOOLEAN?. An empty result from an operand is treated as the false value. If an operand returns NULL, then:
  • The AND operator returns false if the other operand returns false; otherwise, it returns NULL.
  • The OR operator returns true if the other operand returns true; otherwise it returns NULL.
  • The NOT operator returns NULL.

Example 6-40 Logical Operators

Select the id and the last name for users whose age is between 30 and 40 or their income is greater than 100K.

SELECT id, lastName FROM users
WHERE 30 <= age 
    AND age <= 40 
    OR income > 100000;