The SQL BETWEEN condition allows you to retrieve values within a range. The SQL BETWEEN condition can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.
The syntax for the SQL BETWEEN condition is:
expression BETWEEN value1 and value2;
The SQL BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).
SQL BETWEEN Condition - Numeric example
The following is the SQL BETWEEN condition that retrieves values within a numeric range.
For example:
SELECT * FROM suppliers WHERE supplier_id BETWEEN 5000 AND 5010;
This SQL BETWEEN condition example would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL SELECT statement:
SELECT * FROM suppliers WHERE supplier_id >= 5000 AND supplier_id <= 5010;
SQL BETWEEN Condition - Date example
You can also use the SQL BETWEEN condition to retrieve values within a date range.
For example:
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');
This SQL BETWEEN condition example would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive). It would be equivalent to the following SQL SELECT statement:
SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');
SQL BETWEEN Condition - Using the NOT operator
The SQL BETWEEN condition can also be combined with the SQL NOT operator.
For example:
SELECT * FROM suppliers WHERE supplier_id not between 5000 and 5500;
This SQL BETWEEN condition example would return all rows where the supplier_id was not between 5000 and 5500, inclusive. It would be equivalent to the following SQL SELECT statement:
SELECT * FROM suppliers WHERE supplier_id < 5000 OR supplier_id > 5500;
