BETWEEN CONDITION

This SQL tutorial explains how to use the SQL BETWEEN condition with syntax and examples.

DESCRIPTION

The SQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL BETWEEN Condition 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 SQL 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 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 suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;

This SQL BETWEEN 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;

EXAMPLE - WITH DATE

Next, let's look at how you would use the BETWEEN condition with Dates. The following date example uses 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');

EXAMPLE - USING NOT OPERATOR

The SQL BETWEEN condition can also be combined with the SQL NOT operator. Here is an example of how you would combine the BETWEEN condition with the 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;