NOT BETWEEN Operator 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;
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
BETWEEN Operator with IN Example
The following SQL statement selects all products with a price BETWEEN 10 and
20, but products with a CategoryID of 1,2, or 3 should not be displayed:
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT
CategoryID IN (1,2,3);
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
BETWEEN Operator with Text Value Example
The following SQL statement selects all products with a ProductName beginning
with any of the letter BETWEEN 'C' and 'M':
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
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
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
NOT BETWEEN Operator with Text Value Example
The following SQL statement selects all products with a ProductName beginning
with any of the letter NOT BETWEEN 'C' and 'M':
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';
ProductID ProductName SupplierID CategoryID Unit Price
3
Aniseed Syrup
1
2
12 - 550 ml bottles
10
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
Sample Table
Below is a selection from the "Orders" table:
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 Operator with Date Value 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#;
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
