SQL WHERE BETWEEN Clause

  • WHERE BETWEEN returns values that fall within a given range.
  • WHERE BETWEEN is a shorthand for >= AND <=.
  • BETWEEN operator is inclusive: begin and end values are included.
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL WHERE BETWEEN syntax


The general syntax is:
SELECT column-names
  FROM table-name
 WHERE column-name BETWEEN value1 AND value2




SQL WHERE BETWEEN Examples




Problem: List all products between $10 and $20
SELECT Id, ProductName, UnitPrice
  FROM Product
 WHERE UnitPrice BETWEEN 10 AND 20
 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.
SELECT Id, ProductName, UnitPrice
  FROM Product
 WHERE UnitPrice NOT BETWEEN 5 AND 100
 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.
SELECT COUNT(Id), SUM(TotalAmount)
  FROM [Order]
 WHERE OrderDate BETWEEN '1/1/2013' AND '1/31/2013'


Results:



Count TotalAmount
33 66692.80