MySQL: BETWEEN Condition

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

Description

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

Syntax

The syntax for the BETWEEN Condition in MySQL 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 MySQL BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).
• When using the MySQL BETWEEN Condition with dates, be sure to use the CAST function to explicitly convert the values to dates.

Example - With Numeric

Let's look at some MySQL 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 contacts
WHERE contact_id BETWEEN 100 AND 200;

This MySQL BETWEEN example would return all rows from the contacts table where the contact_id is between 100 and 200 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM contacts
WHERE contact_id >= 100
AND contact_id = 200;

Example - With Date

Next, let's look at how you would use the MySQL BETWEEN condition with Dates. When using the BETWEEN condition in MySQL with dates, be sure to use the CAST function to explicitly convert the values to dates.

The following date example uses the BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM order_details
WHERE order_date BETWEEN CAST('2014-02-01' AS DATE) AND CAST('2014-02-28' AS DATE);

This MySQL BETWEEN condition example would return all records from the order_details table where the order_date is between Feb 1, 2014 and Feb 28, 2014 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM order_details
WHERE order_date >= CAST('2014-02-01' AS DATE)
AND order_date = CAST('2014-02-28' AS DATE);

Example - Using NOT Operator

The MySQL BETWEEN condition can also be combined with the 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 2000 AND 2999;

This MySQL BETWEEN example would return all rows from the suppliers table where the supplier_id was NOT between 2000 and 2999, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id  2000
OR supplier_id > 2999;