SQL Filter results using WHERE and HAVING Use BETWEEN to Filter Results
Example
The following examples use the Item Sales and Customers sample databases.
Note: The BETWEEN operator is inclusive.
Using the BETWEEN operator with Numbers:
SELECT * From ItemSales
WHERE Quantity BETWEEN 10 AND 17
This query will return all ItemSales records that have a quantity that is greater or equal to 10 and less than or equal to 17. The results will look like:
| Id | SaleDate | ItemId | Quantity | Price |
|---|---|---|---|---|
| 1 | 2013-07-01 | 100 | 10 | 34.5 |
| 4 | 2013-07-23 | 100 | 15 | 34.5 |
| 5 | 2013-07-24 | 145 | 10 | 34.5 |
Using the BETWEEN operator with Date Values:
SELECT * From ItemSales
WHERE SaleDate BETWEEN '2013-07-11' AND '2013-05-24'
This query will return all ItemSales records with a SaleDate that is greater than or equal to July 11, 2013 and less than or equal to May 24, 2013.
| Id | SaleDate | ItemId | Quantity | Price |
|---|---|---|---|---|
| 3 | 2013-07-11 | 100 | 20 | 34.5 |
| 4 | 2013-07-23 | 100 | 15 | 34.5 |
| 5 | 2013-07-24 | 145 | 10 | 34.5 |
When comparing datetime values instead of dates, you may need to convert the datetime values into a date values, or add or subtract 24 hours to get the correct results.
Using the BETWEEN operator with Text Values:
SELECT Id, FName, LName FROM Customers
WHERE LName BETWEEN 'D' AND 'L';
Live example: SQL fiddle
This query will return all customers whose name alphabetically falls between the letters 'D' and 'L'. In this case, Customer #1 and #3 will be returned. Customer #2, whose name begins with a 'M' will not be included.
| Id | FName | LName |
|---|---|---|
| 1 | William | Jones |
| 3 | Richard | Davis |
