1. SQL Reference Guide
  2. SQL Query Management
  3. Comparison Expressions
  4. Logical Operators: AND, OR, and NOT

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-56 Fetch the id, first name, last name, and age for users who are not in the age group of 20 to 40 or whose income is greater than 90K

SELECT id, firstName, lastName FROM users WHERE NOT age BETWEEN 20 AND 40 OR income > 90000

Consider an application that maintains the user data. The age field holds the age of the user and the income field includes the income of the user. In the above query, you use a combination of operators to get the list of users who are not in the age group of 20 to 40 years or have an income greater than 90K. A BETWEEN operator verifies if the input expression is within the range of the boundary values. Since you want users who are either less than 20 years or more than 40 years of age, use the BETWEEN operator on the age field with the logical operator NOT to fetch the users outside the given range. To fetch the list of users with income greater than 90K, use the value comparison operator '>' to compare the income field of the users. You use the OR operator to fetch the list of users who satisfy either of the conditions.

Output:
{"id":30,"firstName":"Adam","lastName":"Smith","age":45}

Example 6-57 Fetch the list of male passengers from the airline baggage tracking application who are bound toward FRA station and carrying only one checked bag

SELECT
fullname,
s.bagInfo[].flightLegs[].fltRouteDest,
ticketNo
FROM BaggageInfo s
WHERE gender = 'M'
AND s.bagInfo[].flightLegs[].fltRouteDest=any "FRA"
AND (size(s.bagInfo) = 1)

In the airline baggage tracking application, you can fetch the details of the male passengers who are bound toward a specific destination. The fltRouteDest field in the BaggageInfo table includes the destination airport code for each travel leg. The fltRouteDest field can hold the final station or a transit station. To retrieve a list of male passengers who are bound towards FRA and carrying only one checked bag, you perform value comparison operations and use the AND operator to narrow down the results to successfully match all the criteria. You fetch the list of all the male passengers by comparing the gender field. You retrieve the list of passengers bound towards the FRA station by performing a string comparison operation on the fltRouteDest field with the given airport code 'FRA'. You compare the size of the bagInfo array to select the passengers having only one checked bag. You use the AND operator to fetch the list of passengers that satisfy all the conditions mentioned above.

Here, the s.bagInfo.fltRouteDest is a sequence. The value comparison operators cannot operate on sequences of more than one item. A sequence comparison operator any is used in addition to the value comparison operator '=' to compare the fltRouteDest fields.

Note:

If there is more than one logical operator in the query, ensure the proper usage of parenthesis. The parenthesis is assessed first in the order of evaluation.
Output:
{"fullname":"Henry Jenkins","fltRouteDest":["ORD","FRA"],"ticketNo":176234463813}
{"fullname":"Raymond Griffin","fltRouteDest":["FRA","HKG"],"ticketNo":1762399766476}