MySQL: BETWEEN Condition
This MySQL 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. Note that the order_date field is of type DATE and not of type DATETIME. (This is why we don't have to CAST the order_date field to a DATE type in the examples below.)
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;
Advertisements