SQLite: BETWEEN Condition
This SQLite tutorial explains how to use the SQLite BETWEEN condition with syntax and examples.
Description
The SQLite BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the BETWEEN Condition in SQLite is:
expression BETWEEN value1 AND value2;
Parameters or Arguments
- expression
- A column or calculation.
- value1 and value2
- These values create an inclusive range that expression is compared to.
Note
- The SQLite BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).
Example - With Numeric
Let's look at some SQLite BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.
For example:
SELECT * FROM employees WHERE employee_id BETWEEN 1 AND 10;
This SQLite BETWEEN example would return all rows from the employees table where the employee_id is between 1 and 10 (inclusive). It is equivalent to the following SELECT statement:
SELECT * FROM employees WHERE employee_id >= 1 AND employee_id <= 10;
Example - With Date
Next, let's look at how you would use the SQLite BETWEEN condition with Dates. When using the BETWEEN condition in SQLite with dates, be sure to use the CAST function to explicitly convert the values to dates.
The following date example uses the BETWEEN condition to retrieve values within a date range.
For example:
SELECT * FROM employees WHERE hire_date BETWEEN '2014-01-01' AND '2014-12-31';
This SQLite BETWEEN condition example would return all records from the employees table where the hire_date is between Jan 1, 2014 and Dec 31, 2014 (inclusive). It would be equivalent to the following SELECT statement:
SELECT * FROM employees WHERE hire_date >= '2014-01-01' AND hire_date <= '2014-12-31';
Example - Using NOT Operator
The SQLite BETWEEN condition can also be combined with the NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.
For example:
SELECT * FROM employees WHERE employee_id NOT BETWEEN 20 AND 25;
This SQLite BETWEEN example would return all rows from the employees table where the employee_id was NOT between 20 and 25, inclusive. It would be equivalent to the following SELECT statement:
SELECT * FROM employees WHERE employee_id < 20 OR employee_id > 25;
Advertisements