BETWEEN Operator

The BETWEEN operator is used to check if the input expression value is in between the lower and the higher expressions (including the boundary values). This is equivalent to:

low_bound_expression <= input_expression AND input_expression <=
    high_bound_expression

The operation returns a TRUE value if both the expressions return TRUE. If either of the expressions is NULL or leads to a NULL value, the result of the operation is also NULL. The operation returns a FALSE value if any one of the expressions returns FALSE. If any expression returns more than one item, an error is raised as the comparison operators do not operate on sequences of more than one item.

Example 1: Fetch the passenger details and routing information of the baggage that falls within a range of reservation codes.

SELECT fullname AS FULLNAME,
confNo AS RESERVATION,
s.bagInfo.routing AS ROUTINGINFO 
FROM BaggageInfo s
WHERE confNo BETWEEN 'LE6J4Z' and 'ZG8Z5N'
ORDER BY confNo

Explanation: Every passenger has a reservation code (confNo). In this query, you fetch the passenger details, reservation code, and routing details for the baggage whose reservation codes are within the range of LE6J4Z and ZG8Z5N. You use the BETWEEN operator in the WHERE clause to perform a string comparison of the confNo value with the lower and the upper boundary values in the input strings. Only the rows that are within the range are selected and displayed in the output.

Output:

{"FULLNAME":"Adam Phillips","RESERVATION":"LE6J4Z","ROUTINGINFO":"MIA/LAX/MEL"}
{"FULLNAME":"Elane Lemons","RESERVATION":"LN0C8R","ROUTINGINFO":"MXP/CDG/SLC/BZN"}
{"FULLNAME":"Gerard Greene","RESERVATION":"MC0E7R","ROUTINGINFO":"SFO/IST/ATH/JTR"}
{"FULLNAME":"Henry Jenkins","RESERVATION":"MZ2S5R","ROUTINGINFO":"SFO/ORD/FRA"}
{"FULLNAME":"Omar Harvey","RESERVATION":"OH2F8U","ROUTINGINFO":"MEL/LAX/MIA"}
{"FULLNAME":"Kendal Biddle","RESERVATION":"PQ1M8N","ROUTINGINFO":"JFK/IST/VIE"}
{"FULLNAME":"Zina Christenson","RESERVATION":"QB1O0J","ROUTINGINFO":"MIA/LAX/MEL"}
{"FULLNAME":"Lorenzo Phil","RESERVATION":"QI3V6Q","ROUTINGINFO":["SFO/IST/ATH/JTR","SFO/IST/ATH/JTR"]}
{"FULLNAME":"Lucinda Beckman","RESERVATION":"QI3V6Q","ROUTINGINFO":"SFO/IST/ATH/JTR"}
{"FULLNAME":"Michelle Payne","RESERVATION":"RL3J4Q","ROUTINGINFO":"SFO/IST/ATH/JTR"}
{"FULLNAME":"Teena Colley","RESERVATION":"TX1P7E","ROUTINGINFO":"MSQ/FRA/HKG"}
{"FULLNAME":"Fallon Clements","RESERVATION":"XT1O7T","ROUTINGINFO":"MXP/CDG/SLC/BZN"}
{"FULLNAME":"Raymond Griffin","RESERVATION":"XT6K7M","ROUTINGINFO":"MSQ/FRA/HKG"}
{"FULLNAME":"Dierdre Amador","RESERVATION":"ZG8Z5N","ROUTINGINFO":"JFK/MAD"}

Example 2: Find the passengers who traveled from MIA within a fortnight from 15th Feb 2019.

SELECT fullname,
FROM BaggageInfo bag
WHERE exists bag.bagInfo.flightLegs[$element.fltRouteSrc = "MIA"
AND
$element.flightDate BETWEEN "2019-02-15T00:00:00Z" and "2019-03-02T00:00:00Z"]

Explanation: In this query, you fetch the details of the passengers who traveled from MIA between the 15th of Feb 2019 and the 2nd of March 2019. The flightDate field within the bagInfo JSON field contains the travel dates to the destination points. You use the BETWEEN operator to compare the flightDate in the passenger data with the upper and the lower range of the specified dates. The flightDate is a string and is directly compared with the supplied dates, which are also string values. You narrow down the passenger records listed within this range further to include only MIA as the source station using the AND operator. Here the flight source could be the starting point of the flight or any transit airport.

Output:

{"fullname":"Zulema Martindale"}
{"fullname":"Joanne Diaz"}