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 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-61 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.
{"id":30,"firstName":"Adam","lastName":"Smith","age":45}
Example 6-62 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.
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.{"fullname":"Henry Jenkins","fltRouteDest":["ORD","FRA"],"ticketNo":176234463813}
{"fullname":"Raymond Griffin","fltRouteDest":["FRA","HKG"],"ticketNo":1762399766476}