Summary: in this tutorial, you will learn how to use the SQLite
BETWEEN operator to test whether a value is in a range of values.
Introduction to SQLite
BETWEEN operator is a logical operator that tests whether a value is in range of values. If the value is in the specified range, the
BETWEEN operator returns true. The
BETWEEN operator can be used in the
WHERE clause of the
The following illustrates the syntax of the SQLite
test_expression BETWEEN low_expression AND high_expression
In this syntax:
test_expressionis an expression to test for in the range defined by
high_expressionis any valid expression that specify the low and high values of the range. The
low_expressionshould be less than or equal to
high_expression, or the
BETWEENis always returns false.
ANDkeyword is a placeholder which indicates the
test_expressionshould be within the range specified by
Note that the
BETWEEN operator is inclusive. It returns true when the
test_expression is less than or equal to
high_expression and greater than or equal to the value of
test_expression >= low_expression AND test_expression <= high_expression
To specify an exclusive range, you use the greater than (>) and less than operators (<).
Note that if any input to the
BETWEEN operator is NULL, the result is NULL, or unknown to be precise.
To negate the result of the
BETWEEN operator, you use the
NOT BETWEEN operator as follows:
test_expression NOT BETWEEN low_expression AND high_expression
BETWEEN returns true if the value of
test_expression is less than the value of
low_expression or greater than the value of
test_expression < low_expression OR test_expression > high_expression
BETWEEN operator examples
We will use the
invoices table from the sample database for the demonstration:
BETWEEN numeric values example
The following statement finds invoices whose total is
between 14.96 and 18.86:
Total BETWEEN 14.91 and 18.86
Here is the output:
As you can see, the invoices whose total is 14.91 or 18.86 are included in the result set.
NOT BETWEEN numeric values example
To find the invoices whose total are not between 1 and 20, you use the
NOT BETWEEN operator as shown in the following query:
Total NOT BETWEEN 1 and 20
The following picture shows the output:
As clearly shown in the output, the result includes the invoices whose total is less than 1 and greater than 20.
BETWEEN dates example
The following example finds invoices whose invoice dates are from
January 1 2010 and
January 31 2010:
InvoiceDate BETWEEN '2010-01-01' AND '2010-01-31'
Here is the output:
NOT BETWEEN dates example
The following statement finds invoices whose dates are not between January 03, 2009, and December 01, 2013:
InvoiceDate NOT BETWEEN '2009-01-03' AND '2013-12-01'
The output is as follows:
In this tutorial, you have learned how to use the SQLite
BETWEEN operator to test whether a value is in a range of values