Where Between

The BETWEEN operator selects values within a given range. The between operator is similar to >= AND <=.

SQL Where BETWEEN Syntax:

SELECT  Column1

FROM Table_Name

WHERE Column1 BETWEEN value1 AND value2;

Example:

Lets use the “Orders” table to find all orders placed in the month of September.

OrderID	ProductID	EmployeeID	OrderDate	CustomerID
10	2	1	9/9/2009	2
11	3	2	1/9/2009	3
12	4	1	3/9/2009	4
13	1	3	9/9/2010	5
14	3	1	9/30/2009	3
15	5	2	6/12/2009	1
16	3	4	4/9/2009	6

SELECT
OrderID,
ProductID,
EmployeeID,
OrderDate,
CustomerID
FROM Orders
WHERE OrderDate BETWEEN ‘8/31/2009’ AND ‘10/1/2009’

Results:

OrderID	ProductID	EmployeeID	OrderDate	CustomerID
10	2	1	9/9/2009	2
11	3	2	1/9/2009	3
13	1	3	9/9/2010	5
14	3	1	9/30/2009	3

The NOT Operator can also be used to include all values outside of the selected range while excluding the values in that selected range.

SQL Where NOT BETWEEN Syntax:

SELECT  Column1

FROM Table_Name

WHERE Column1 NOT BETWEEN value1 AND value2;

Example:

Lets use the “Customers” table to exclude all customers whose first name begins with the letters in the range of “N” to “V”.

CustomerID	FirstName	LastName	Address	City	State	zip
1	Rob	Johnson	1 York Rd	New York	NY	10013
2	Jessica	Simmons	2 High St	Maimi	FL	33101
3	Tracy	Baker	3 River Dr	Chicago	IL	60633
4	Tod	Williams	4 Cabin Ln	Denver	CO	80210
5	Tammy	Wilson	5 Peach St	Seattle	WA	98121
6	Mike	Jones	6 Rice Rd	Los Angeles	CA	90009

SELECT
CustomerID,
FirstName,
LastName,
Address,
City,
State,
zip
FROM Customer
WHERE LastName NOT BETWEEN ‘N’ AND ‘V’

Results:

CustomerID	FirstName	LastName	Address	City	State	zip
2	Jessica	Simmons	2 High St	Maimi	FL	33101
6	Mike	Jones	6 Rice Rd	Los Angeles	CA	90009

SQL Tutorial