Dofactory.com
Dofactory.com

SQL WHERE BETWEEN

WHERE BETWEEN returns values within a given range.

BETWEEN is a shorthand for >= AND <=.

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

Example

#

List all orders between $1000 and $2000.

SELECT OrderDate, OrderNumber, TotalAmount
  FROM [Order]
 WHERE TotalAmount BETWEEN 1000 AND 2000
Result: 208 records
OrderDate OrderNumber TotalAmount
2012-07-05 00:00:00.000 542379 1863.40
2012-07-08 00:00:00.000 542380 1813.00
2012-07-10 00:00:00.000 542383 1444.80
2012-07-16 00:00:00.000 542387 1119.90
2012-07-19 00:00:00.000 542390 1746.20

Syntax

WHERE BETWEEN syntax.

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

More Examples

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'

COUNT and SUM are built-in aggregate functions.

Result: 1 record
Count Total Sales
33 66692.80

You may also like



Last updated on Dec 21, 2023