SQL WHERE BETWEEN Clause

What can I do with the 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 SQL WHERE BETWEEN syntax

The general syntax is

SELECT column-names
  FROM table-name
 WHERE column-name BETWEEN value1 AND value2

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued

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
Result:  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

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
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
Result:  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

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
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'
Result:
Count TotalAmount
33 66692.80


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas