BETWEEN
BETWEEN is a filter that chooses values within a specified range. When used with the optional keyword NOT, BETWEEN chooses values outside of a specified range.
Syntax
[NOT] BETWEEN low_value AND high_value
Remarks
• The BETWEEN range contains a low value and a high value. The low value must be less than or equal to the high value.
• Both low and high values are included in the search.
• BETWEEN can be used in both WHERE and HAVING clauses.
• BETWEEN works with character strings, numbers, and date-times. Only the values that are identical to the search values are returned.
• BETWEEN is equivalent to using <= and >= with this syntax:
WHERE test_column >= low_value AND test_column <= high_value
Example (Between Values)
SELECT ProductID, ProductName
FROM /shared/examples/ds_orders/products
WHERE UnitPrice BETWEEN 50 and 100
This query returns the product ID and name for all products whose unit price is between 50 and 100, inclusive.
Example (Between Dates)
SELECT OrderID
FROM /shared/examples/ds_orders/orders
WHERE OrderDate BETWEEN DATE '2012-05-03' AND DATE '2012-05-04'
This query returns the order ID for all orders with an order date of May 3 or May 4, 2012.