HTML tutorial
CSS3 tutorial
Bootstrap tutorial
JavaScript tutorial
JQuery tutorial
AngularJS tutorial
React tutorial
NodeJS tutorial
PHP tutorial
Python tutorial
Python3 tutorial
Django tutorial
Linux tutorial
Docker tutorial
Ruby tutorial
Java tutorial
C tutorial
C ++ tutorial
Perl tutorial
JSP tutorial
Lua tutorial
Scala tutorial
Go tutorial
ASP.NET tutorial
C # tutorial
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates
The BETWEEN
operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN
operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
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 |
The following SQL statement selects all products with a price between 10 and 20:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
To display the products outside the range of the previous example, use
NOT BETWEEN
:
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
The following SQL statement selects all products with a price between 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Mozzarella di Giovanni:
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni'
ORDER BY ProductName;
The following SQL statement selects all products with a ProductName between Carnarvon Tigers and Chef Anton's Cajun Seasoning:
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND
"Chef Anton's Cajun Seasoning"
ORDER BY ProductName;
The following SQL statement selects all products with a ProductName not between Carnarvon Tigers and Mozzarella di Giovanni:
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni'
ORDER BY ProductName;
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 |
The following SQL statement selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
OR:
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Use the BETWEEN
operator to select all the records where the value of the Price
column is between 10 and 20.
SELECT * FROM Products WHERE Price
;