SQL WHERE BETWEEN

SQL WHERE BETWEEN Explained

WHERE BETWEEN returns values within a given range.

WHERE BETWEEN is a shorthand for >= AND <=.

BETWEEN is inclusive, i.e. begin and end values are included.

Example

#

Problem: List all orders between $1000 and $2000.

SELECT *
  FROM [Order]
 WHERE TotalAmount BETWEEN 1000 AND 2000

Syntax

BETWEEN syntax.

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

More Examples

SQL WHERE BETWEEN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products between $10 and $20, sorted by price.
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

SQL WHERE NOT BETWEEN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products not between $5 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

SQL WHERE BETWEEN Dates

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: Get the number of orders and total amount sold between Jan 1, 2013 and Jan 31, 2013.
SELECT COUNT(Id) AS Count, SUM(TotalAmount) AS 'Total Sales'
  FROM [Order]
 WHERE OrderDate BETWEEN '1/1/2013' AND '1/31/2013'
Result:
Count TotalAmount
33 66692.80

You may also like



Guides