Basic about BETWEEN Condition – using and example

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

Description

The SQL BETWEEN situation permits you to effortlessly check if an expression is inside a vary of values (inclusive). It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Subscribe

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 the place expression is within the range of value1 and value2 (inclusive).

DDL/DML for Examples

If you desire to comply with alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your personal database!

Get DDL/DML

Example – Using BETWEEN Condition with Numeric Values

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

In this example, we have a table referred to as suppliers with the following data:

supplier_id supplier_name town 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:

Try It

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 300 AND 600;

There will be 4 data selected. These are the results that you ought to see:

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

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

Try It

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

Example – Using BETWEEN Condition with Date Values

Dates can be quite complex in SQL and how you use the BETWEEN condition with dates relies upon on the database you are going for walks (ie: Oracle, SQL Server, MySQL, etc). We will exhibit you an instance for each of the important database technologies. So let’s get started.

In this example, we have a desk referred to as 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:

Try It

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 three 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 documents 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 circumstance can be used with the NOT operator to create a NOT BETWEEN condition. Let’s explore an instance that suggests how to use the NOT BETWEEN circumstance 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 techonthenet.com 5000 Smith Jane digminecraft.com 6000 Ferguson Samantha bigactivities.com 7000 Reynolds Allen checkyourmath.com 8000 Anderson Paige NULL 9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It

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

There will be 2 information selected. These are the results that you need to see:

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

This would return all rows the place the customer_id used to be NOT between 5000 and 8000, inclusive. It would be equal to the following SELECT statement:

Try It

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