SQL: BETWEEN Condition

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

Description

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the BETWEEN condition in SQL 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).

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using BETWEEN Condition with Numeric Values

Let's look at an example of how to use the BETWEEN condition to retrieve values within a numeric range.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Enter the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 300 AND 600;

There will be 4 records selected. These are the results that you should see:

supplier_id	supplier_name	city	state
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin

This example would return all rows from the suppliers table where the supplier_id is between 300 and 600 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 300
AND supplier_id <= 600;

Example - Using BETWEEN Condition with Date Values

Dates can be somewhat tricky in SQL and how you use the BETWEEN condition with dates depends on the database you are running (ie: Oracle, SQL Server, MySQL, etc). We will show you an example for each of the major database technologies. So let's get started.

In this example, we have a table called orders with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

Enter one of the following SQL statements, depending on the database you are running.

For SQL Server, PostgreSQL and SQLite:

SELECT *
FROM orders
WHERE order_date BETWEEN '2016/04/19' AND '2016/05/01';

For Oracle (use the TO_DATE function):

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2016/04/19', 'yyyy/mm/dd')
AND TO_DATE ('2016/05/01', 'yyyy/mm/dd');

For MySQL and MariaDB (use the CAST function):

SELECT *
FROM orders
WHERE order_date BETWEEN CAST('2016/04/19' AS DATE) AND CAST('2016/05/01' AS DATE);

There will be 3 records selected. These are the results that you should see:

order_id	customer_id	order_date
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

This example would return all records from the orders table where the order_date is between Apr 19, 2016 and May 1, 2016 (inclusive).

]Example - Using NOT Operator with the BETWEEN Condition

The BETWEEN condition can be used with the NOT operator to create a NOT BETWEEN condition. Let's explore an example that shows how to use the NOT BETWEEN condition in a query.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following SQL statement:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 5000 AND 8000;

There will be 2 records selected. These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
9000	Johnson	Derek	google.com

This would return all rows where the customer_id was NOT between 5000 and 8000, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 5000
OR customer_id > 8000;