SQL BETWEEN Keyword
The BETWEEN operator in SQL is used to filter data based on a range of values. It's a convenient way to select rows where a column's value falls within a specified minimum and maximum.
BETWEEN: Definition and Usage
BETWEEN is inclusive, meaning that the start and end values of the range are included in the selection. It works with numeric, text, and date data types.
Syntax
Syntax
SELECT column_names
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
--To exclude values within the range:
SELECT column_names
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;
Examples
Selecting Products Within a Price Range
This query selects products with a price between 10 and 20 (inclusive).
Syntax
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Output
All rows from the 'Products' table where the 'Price' column is between 10 and 20 will be returned.
ProductID | ProductName | Price
-------------------------------------
1 | Product A | 12.50
2 | Product B | 15.00
Selecting Products Outside a Price Range
Using NOT BETWEEN, this query selects products whose prices are *not* within the 10-20 range.
Syntax
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Output
All rows from 'Products' where 'Price' is less than 10 or greater than 20 will be returned.
ProductID | ProductName | Price
-------------------------------------
3 | Product C | 25.00
4 | Product D | 8.00
Selecting Products Within a Name Range (Alphabetical)
BETWEEN also works with text, ordering alphabetically. This example selects product names between 'Carnarvon Tigers' and 'Mozzarella di Giovanni'.
Syntax
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Output
Products with names alphabetically between 'Carnarvon Tigers' and 'Mozzarella di Giovanni' are returned, sorted alphabetically.
ProductID | ProductName | Price
-----------------------------------------
5 | Chai | 18.00
6 | Chang | 19.00
7 | Guaraná Fantástica | 4.50