SQL BETWEEN Operator : SQL 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;
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;
output
Number of Records: 29
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 |
15 | Genen Shouyu | 6 | 2 | 24 – 250 ml bottles | 15.5 |
16 | Pavlova | 7 | 3 | 32 – 500 g boxes | 17.45 |
21 | Sir Rodney’s Scones | 8 | 3 | 24 pkgs. x 4 pieces | 10 |
25 | NuNuCa Nuß-Nougat-Creme | 11 | 3 | 20 – 450 g glasses | 14 |
31 | Gorgonzola Telino | 14 | 4 | 12 – 100 g pkgs | 12.5 |
34 | Sasquatch Ale | 16 | 1 | 24 – 12 oz bottles | 14 |
35 | Steeleye Stout | 16 | 1 | 24 – 12 oz bottles | 18 |
36 | Inlagd Sill | 17 | 8 | 24 – 250 g jars | 19 |
39 | Chartreuse verte | 18 | 1 | 750 cc per bottle | 18 |
40 | Boston Crab Meat | 19 | 8 | 24 – 4 oz tins | 18.4 |
42 | Singaporean Hokkien Fried Mee | 20 | 5 | 32 – 1 kg pkgs. | 14 |
44 | Gula Malacca | 20 | 2 | 20 – 2 kg bags | 19.45 |
46 | Spegesild | 21 | 8 | 4 – 450 g glasses | 12 |
48 | Chocolade | 22 | 3 | 10 pkgs. | 12.75 |
49 | Maxilaku | 23 | 3 | 24 – 50 g pkgs. | 20 |
50 | Valkoinen suklaa | 23 | 3 | 12 – 100 g bars | 16.25 |
57 | Ravioli Angelo | 26 | 5 | 24 – 250 g pkgs. | 19.5 |
58 | Escargots de Bourgogne | 27 | 8 | 24 pieces | 13.25 |
66 | Louisiana Hot Spiced Okra | 2 | 2 | 24 – 8 oz jars | 17 |
67 | Laughing Lumberjack Lager | 16 | 1 | 24 – 12 oz bottles | 14 |
68 | Scottish Longbreads | 8 | 3 | 10 boxes x 8 pieces | 12.5 |
70 | Outback Lager | 7 | 1 | 24 – 355 ml bottles | 15 |
73 | Röd Kaviar | 17 | 8 | 24 – 150 g jars | 15 |
74 | Longlife Tofu | 4 | 7 | 5 kg pkg. | 10 |
76 | Lakkalikööri | 23 | 1 | 500 ml | 18 |
77 | Original Frankfurter grüne Soße | 12 | 2 | 12 boxes | 13 |
NOT BETWEEN Example
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
output
Number of Records: 48
ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 22 |
5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
6 | Grandma’s Boysenberry Spread | 3 | 2 | 12 – 8 oz jars | 25 |
7 | Uncle Bob’s Organic Dried Pears | 3 | 7 | 12 – 1 lb pkgs. | 30 |
8 | Northwoods Cranberry Sauce | 3 | 2 | 12 – 12 oz jars | 40 |
9 | Mishi Kobe Niku | 4 | 6 | 18 – 500 g pkgs. | 97 |
10 | Ikura | 4 | 8 | 12 – 200 ml jars | 31 |
11 | Queso Cabrales | 5 | 4 | 1 kg pkg. | 21 |
12 | Queso Manchego La Pastora | 5 | 4 | 10 – 500 g pkgs. | 38 |
13 | Konbu | 6 | 8 | 2 kg box | 6 |
14 | Tofu | 6 | 7 | 40 – 100 g pkgs. | 23.25 |
17 | Alice Mutton | 7 | 6 | 20 – 1 kg tins | 39 |
18 | Carnarvon Tigers | 7 | 8 | 16 kg pkg. | 62.5 |
19 | Teatime Chocolate Biscuits | 8 | 3 | 10 boxes x 12 pieces | 9.2 |
20 | Sir Rodney’s Marmalade | 8 | 3 | 30 gift boxes | 81 |
22 | Gustaf’s Knäckebröd | 9 | 5 | 24 – 500 g pkgs. | 21 |
23 | Tunnbröd | 9 | 5 | 12 – 250 g pkgs. | 9 |
24 | Guaraná Fantástica | 10 | 1 | 12 – 355 ml cans | 4.5 |
26 | Gumbär Gummibärchen | 11 | 3 | 100 – 250 g bags | 31.23 |
27 | Schoggi Schokolade | 11 | 3 | 100 – 100 g pieces | 43.9 |
28 | Rössle Sauerkraut | 12 | 7 | 25 – 825 g cans | 45.6 |
29 | Thüringer Rostbratwurst | 12 | 6 | 50 bags x 30 sausgs. | 123.79 |
30 | Nord-Ost Matjeshering | 13 | 8 | 10 – 200 g glasses | 25.89 |
32 | Mascarpone Fabioli | 14 | 4 | 24 – 200 g pkgs. | 32 |
33 | Geitost | 15 | 4 | 500 g | 2.5 |
37 | Gravad lax | 17 | 8 | 12 – 500 g pkgs. | 26 |
38 | Côte de Blaye | 18 | 1 | 12 – 75 cl bottles | 263.5 |
41 | Jack’s New England Clam Chowder | 19 | 8 | 12 – 12 oz cans | 9.65 |
43 | Ipoh Coffee | 20 | 1 | 16 – 500 g tins | 46 |
45 | Røgede sild | 21 | 8 | 1k pkg. | 9.5 |
47 | Zaanse koeken | 22 | 3 | 10 – 4 oz boxes | 9.5 |
51 | Manjimup Dried Apples | 24 | 7 | 50 – 300 g pkgs. | 53 |
52 | Filo Mix | 24 | 5 | 16 – 2 kg boxes | 7 |
53 | Perth Pasties | 24 | 6 | 48 pieces | 32.8 |
54 | Tourtière | 25 | 6 | 16 pies | 7.45 |
55 | Pâté chinois | 25 | 6 | 24 boxes x 2 pies | 24 |
56 | Gnocchi di nonna Alice | 26 | 5 | 24 – 250 g pkgs. | 38 |
59 | Raclette Courdavault | 28 | 4 | 5 kg pkg. | 55 |
60 | Camembert Pierrot | 28 | 4 | 15 – 300 g rounds | 34 |
61 | Sirop d’érable | 29 | 2 | 24 – 500 ml bottles | 28.5 |
62 | Tarte au sucre | 29 | 3 | 48 pies | 49.3 |
63 | Vegie-spread | 7 | 2 | 15 – 625 g jars | 43.9 |
64 | Wimmers gute Semmelknödel | 12 | 5 | 20 bags x 4 pieces | 33.25 |
65 | Louisiana Fiery Hot Pepper Sauce | 2 | 2 | 32 – 8 oz bottles | 21.05 |
69 | Gudbrandsdalsost | 15 | 4 | 10 kg pkg. | 36 |
71 | Fløtemysost | 15 | 4 | 10 – 500 g pkgs. | 21.5 |
72 | Mozzarella di Giovanni | 14 | 4 | 24 – 200 g pkgs. | 34.8 |
75 | Rhönbräu Klosterbier | 12 | 1 | 24 – 0.5 l bottles | 7.75 |
BETWEEN with IN Example
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:
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
output
Number of Records: 9
ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
31 | Gorgonzola Telino | 14 | 4 | 12 – 100 g pkgs | 12.5 |
36 | Inlagd Sill | 17 | 8 | 24 – 250 g jars | 19 |
40 | Boston Crab Meat | 19 | 8 | 24 – 4 oz tins | 18.4 |
42 | Singaporean Hokkien Fried Mee | 20 | 5 | 32 – 1 kg pkgs. | 14 |
46 | Spegesild | 21 | 8 | 4 – 450 g glasses | 12 |
57 | Ravioli Angelo | 26 | 5 | 24 – 250 g pkgs. | 19.5 |
58 | Escargots de Bourgogne | 27 | 8 | 24 pieces | 13.25 |
73 | Röd Kaviar | 17 | 8 | 24 – 150 g jars | 15 |
74 | Longlife Tofu | 4 | 7 | 5 kg pkg. | 10 |
BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName BETWEEN ‘Carnarvon Tigers’ and ‘Mozzarella di Giovanni’:
Example
SELECT * FROM Products
WHERE ProductName BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
ORDER BY ProductName;
output
Number of Records: 37
ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
18 | Carnarvon Tigers | 7 | 8 | 16 kg pkg. | 62.5 |
1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
39 | Chartreuse verte | 18 | 1 | 750 cc per bottle | 18 |
4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 22 |
5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
48 | Chocolade | 22 | 3 | 10 pkgs. | 12.75 |
38 | Côte de Blaye | 18 | 1 | 12 – 75 cl bottles | 263.5 |
58 | Escargots de Bourgogne | 27 | 8 | 24 pieces | 13.25 |
52 | Filo Mix | 24 | 5 | 16 – 2 kg boxes | 7 |
71 | Fløtemysost | 15 | 4 | 10 – 500 g pkgs. | 21.5 |
33 | Geitost | 15 | 4 | 500 g | 2.5 |
15 | Genen Shouyu | 6 | 2 | 24 – 250 ml bottles | 15.5 |
56 | Gnocchi di nonna Alice | 26 | 5 | 24 – 250 g pkgs. | 38 |
31 | Gorgonzola Telino | 14 | 4 | 12 – 100 g pkgs | 12.5 |
6 | Grandma’s Boysenberry Spread | 3 | 2 | 12 – 8 oz jars | 25 |
37 | Gravad lax | 17 | 8 | 12 – 500 g pkgs. | 26 |
24 | Guaraná Fantástica | 10 | 1 | 12 – 355 ml cans | 4.5 |
69 | Gudbrandsdalsost | 15 | 4 | 10 kg pkg. | 36 |
44 | Gula Malacca | 20 | 2 | 20 – 2 kg bags | 19.45 |
26 | Gumbär Gummibärchen | 11 | 3 | 100 – 250 g bags | 31.23 |
22 | Gustaf’s Knäckebröd | 9 | 5 | 24 – 500 g pkgs. | 21 |
10 | Ikura | 4 | 8 | 12 – 200 ml jars | 31 |
36 | Inlagd Sill | 17 | 8 | 24 – 250 g jars | 19 |
43 | Ipoh Coffee | 20 | 1 | 16 – 500 g tins | 46 |
41 | Jack’s New England Clam Chowder | 19 | 8 | 12 – 12 oz cans | 9.65 |
13 | Konbu | 6 | 8 | 2 kg box | 6 |
76 | Lakkalikööri | 23 | 1 | 500 ml | 18 |
67 | Laughing Lumberjack Lager | 16 | 1 | 24 – 12 oz bottles | 14 |
74 | Longlife Tofu | 4 | 7 | 5 kg pkg. | 10 |
65 | Louisiana Fiery Hot Pepper Sauce | 2 | 2 | 32 – 8 oz bottles | 21.05 |
66 | Louisiana Hot Spiced Okra | 2 | 2 | 24 – 8 oz jars | 17 |
51 | Manjimup Dried Apples | 24 | 7 | 50 – 300 g pkgs. | 53 |
32 | Mascarpone Fabioli | 14 | 4 | 24 – 200 g pkgs. | 32 |
49 | Maxilaku | 23 | 3 | 24 – 50 g pkgs. | 20 |
9 | Mishi Kobe Niku | 4 | 6 | 18 – 500 g pkgs. | 97 |
72 | Mozzarella di Giovanni | 14 | 4 | 24 – 200 g pkgs. | 34.8 |
NOT BETWEEN Text Values Example
The following SQL statement selects all products with a ProductName NOT BETWEEN ‘Carnarvon Tigers’ and ‘Mozzarella di Giovanni’:
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’
ORDER BY ProductName;
output
Number of Records: 40
ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
17 | Alice Mutton | 7 | 6 | 20 – 1 kg tins | 39 |
3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
40 | Boston Crab Meat | 19 | 8 | 24 – 4 oz tins | 18.4 |
60 | Camembert Pierrot | 28 | 4 | 15 – 300 g rounds | 34 |
30 | Nord-Ost Matjeshering | 13 | 8 | 10 – 200 g glasses | 25.89 |
8 | Northwoods Cranberry Sauce | 3 | 2 | 12 – 12 oz jars | 40 |
25 | NuNuCa Nuß-Nougat-Creme | 11 | 3 | 20 – 450 g glasses | 14 |
77 | Original Frankfurter grüne Soße | 12 | 2 | 12 boxes | 13 |
70 | Outback Lager | 7 | 1 | 24 – 355 ml bottles | 15 |
16 | Pavlova | 7 | 3 | 32 – 500 g boxes | 17.45 |
53 | Perth Pasties | 24 | 6 | 48 pieces | 32.8 |
55 | Pâté chinois | 25 | 6 | 24 boxes x 2 pies | 24 |
11 | Queso Cabrales | 5 | 4 | 1 kg pkg. | 21 |
12 | Queso Manchego La Pastora | 5 | 4 | 10 – 500 g pkgs. | 38 |
59 | Raclette Courdavault | 28 | 4 | 5 kg pkg. | 55 |
57 | Ravioli Angelo | 26 | 5 | 24 – 250 g pkgs. | 19.5 |
75 | Rhönbräu Klosterbier | 12 | 1 | 24 – 0.5 l bottles | 7.75 |
73 | Röd Kaviar | 17 | 8 | 24 – 150 g jars | 15 |
28 | Rössle Sauerkraut | 12 | 7 | 25 – 825 g cans | 45.6 |
45 | Røgede sild | 21 | 8 | 1k pkg. | 9.5 |
34 | Sasquatch Ale | 16 | 1 | 24 – 12 oz bottles | 14 |
27 | Schoggi Schokolade | 11 | 3 | 100 – 100 g pieces | 43.9 |
68 | Scottish Longbreads | 8 | 3 | 10 boxes x 8 pieces | 12.5 |
42 | Singaporean Hokkien Fried Mee | 20 | 5 | 32 – 1 kg pkgs. | 14 |
20 | Sir Rodney’s Marmalade | 8 | 3 | 30 gift boxes | 81 |
21 | Sir Rodney’s Scones | 8 | 3 | 24 pkgs. x 4 pieces | 10 |
61 | Sirop d’érable | 29 | 2 | 24 – 500 ml bottles | 28.5 |
46 | Spegesild | 21 | 8 | 4 – 450 g glasses | 12 |
35 | Steeleye Stout | 16 | 1 | 24 – 12 oz bottles | 18 |
62 | Tarte au sucre | 29 | 3 | 48 pies | 49.3 |
19 | Teatime Chocolate Biscuits | 8 | 3 | 10 boxes x 12 pieces | 9.2 |
29 | Thüringer Rostbratwurst | 12 | 6 | 50 bags x 30 sausgs. | 123.79 |
14 | Tofu | 6 | 7 | 40 – 100 g pkgs. | 23.25 |
54 | Tourtière | 25 | 6 | 16 pies | 7.45 |
23 | Tunnbröd | 9 | 5 | 12 – 250 g pkgs. | 9 |
7 | Uncle Bob’s Organic Dried Pears | 3 | 7 | 12 – 1 lb pkgs. | 30 |
50 | Valkoinen suklaa | 23 | 3 | 12 – 100 g bars | 16.25 |
63 | Vegie-spread | 7 | 2 | 15 – 625 g jars | 43.9 |
64 | Wimmers gute Semmelknödel | 12 | 5 | 20 bags x 4 pieces | 33.25 |
47 | Zaanse koeken | 22 | 3 | 10 – 4 oz boxes | 9.5 |
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
The following SQL statement selects all orders with an OrderDate BETWEEN ’04-July-1996′ and ’09-July-1996′:
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
output
Number of Records: 5
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/8/1996 | 1 |
10252 | 76 | 4 | 7/9/1996 | 2 |