SQL WHERE BETWEEN Clause

작성자 admin 작성일자

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