MySQL BETWEEN
The MySQL BETWEEN Operator
Values inside a certain range are chosen by the BETWEEN operator. Dates, text, or numbers can be used as the values.
The values at the beginning and end are included in the BETWEEN operator.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Demo Database
Below is a selection from the “Products” table in the Northwind sample database:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 1 | 2 | 48 – 6 oz jars | 22 |
| 5 | Chef Anton’s Gumbo Mix | 1 | 2 | 36 boxes | 21.35 |
BETWEEN Example
The following SQL statement selects all products with a price between 10 and 20:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Example
Use NOT BETWEEN to display the products outside of the preceding example’s range:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN with IN Example
The goods with prices between 10 and 20 are all chosen by the SQL query that follows. Furthermore, products with a CategoryID of 1, 2, or 3 should not be displayed.
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
BETWEEN Text Values Example
All items having a ProductName between “Carnarvon Tigers” and “Mozzarella di Giovanni” are chosen using the SQL statement that follows:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
All items with a ProductName between “Carnarvon Tigers” and “Chef Anton’s Cajun Seasoning” are chosen using the SQL query that follows:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
NOT BETWEEN Text Values Example
All items having a ProductName that is not between “Carnarvon Tigers” and “Mozzarella di Giovanni” are chosen by the SQL query that follows:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Sample Table
Below is a selection from the “Orders” table in the Northwind sample database:
| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
|---|---|---|---|---|
| 10248 | 90 | 5 | 7/4/1996 | 3 |
| 10249 | 81 | 6 | 7/5/1996 | 1 |
| 10250 | 34 | 4 | 7/8/1996 | 2 |
| 10251 | 84 | 3 | 7/9/1996 | 1 |
| 10252 | 76 | 4 | 7/10/1996 | 2 |
BETWEEN Dates Example
All orders with an OrderDate between “01-July-1996” and “31-July-1996” are selected using the SQL statement that follows:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
