SQL WHERE BETWEEN Clause

The SQL WHERE BETWEEN syntax

The general syntax is:

1. SELECT column–names
2. FROM table–name
3. WHERE column–name BETWEEN value1 AND value2

 

---

 

SQL WHERE BETWEEN Examples

Problem: List all products between $10 and $20

1. SELECT Id, ProductName, UnitPrice
2. FROM Product
3. WHERE UnitPrice BETWEEN 10 AND 20
4. ORDER BY UnitPrice

Results: 29 records.

Id	ProductName	UnitPrice
3	Aniseed Syrup	10.00
21	Sir Rodney’s Scones	10.00
74	Longlife Tofu	10.00
46	Spegesild	12.00
31	Gorgonzola Telino	12.50

 

---

Problem: List all products not between $10 and $100 sorted by price.

1. SELECT Id, ProductName, UnitPrice
2. FROM Product
3. WHERE UnitPrice NOT BETWEEN 5 AND 100
4. ORDER BY UnitPrice

Results: 4 records.

Id	ProductName	UnitPrice
33	Geitost	2.50
24	Guaraná Fantástica	4.50
29	Thüringer Rostbratwurst	123.79
38	Côte de Blaye	263.50

---

Problem: Get the number of orders and amount sold between Jan 1, 2013 and Jan 31, 2013.

1. SELECT COUNT(Id), SUM(TotalAmount)
2. FROM [Order]
3. WHERE OrderDate BETWEEN ‘1/1/2013’ AND ‘1/31/2013’

Results:

Count	TotalAmount
33	66692.80