SQL Between Condition

SQL: BETWEEN Condition

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;