1. SQL Reference Guide
  2. SQL Query Management
  3. Comparison Expressions
  4. BETWEEN Operator

BETWEEN Operator

Syntax

between_expression ::= input_expression BETWEEN low_bound_expression AND
    high_bound_expression
input_expression := concatenate_expression
low_bound_expression := concatenate_expression
high_bound_expression := concatenate_expression

Semantics

The BETWEEN operator checks 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 BETWEEN operator internally performs the following:
  1. Two value comparison operations: It checks the following conditions,
    1. If the low bound expression is <= the input expression.
    2. If the input expression is <= the high bound expression.
  2. Logical operation - the logical operator AND is applied to the results.

This essentially verifies if the input expression is within the range of the boundary values. The operation returns a TRUE value if both the expressions return TRUE. The operation returns a FALSE value if any one of the expressions returns FALSE. If either of the expressions is NULL or leads to a NULL value, the result of the operation is also NULL. 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. For more details on the value comparison operators and logical operators, see Comparison Expressions.

See the semantics in the Value Comparison Operators topic to understand the comparison of the input expressions for different data types.

Example 6-65 Fetch the passenger details whose ticket numbers are in a certain range.

SELECT
fullname,
ticketNo
FROM baggageinfo s
WHERE ticketNo BETWEEN 1762300000000 and 1762350000000;

Explanation: In this query, you fetch the details of passengers whose ticket numbers are between 1762300000000 and 1762350000000 from the baggageInfo table. You use the BETWEEN operator in the WHERE clause to select and display the rows that fall within the required range.

Output:
{"fullname":"Lorenzo Phil","ticketNo":1762320369957}
{"fullname":"Elane Lemons","ticketNo":1762324912391}
{"fullname":"Michelle Payne","ticketNo":1762330498104}
{"fullname":"Lucinda Beckman","ticketNo":1762320569757}
{"fullname":"Rosalia Triplett","ticketNo":1762311547917}
{"fullname":"Zulema Martindale","ticketNo":1762340579411}
{"fullname":"Omar Harvey","ticketNo":1762348904343}
{"fullname":"Adam Phillips","ticketNo":1762344493810}
{"fullname":"Mary Watson","ticketNo":1762340683564}
{"fullname":"Gerard Greene","ticketNo":1762341772625}

Example 6-66 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 6-67 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"}