The BETWEEN keyword is used in SQL to filter results within a specified range of values. It can be used with numeric, string, or date values. The syntax for using BETWEEN typically looks like this:
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example Usage
1. Numeric Range:
Suppose you have a table named Products with a column Price, and you want to find products with prices between $10 and $50:
SELECT *
FROM Products
WHERE Price BETWEEN 10 AND 50;
2. Date Range:
If you have a table named Orders with a column OrderDate, and you want to retrieve orders placed between January 1, 2023, and January 31, 2023:
SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';
3. String Range:
For string values, if you have a table named Employees with a column LastName, and you want to find employees whose last names fall alphabetically between 'A' and 'M':
SELECT *
FROM Employees
WHERE LastName BETWEEN 'A' AND 'M';
Important Notes:
- The BETWEEN operator is inclusive, meaning it includes the boundary values specified (value1 and value2).
- The order of the values matters; value1 must be less than or equal to value2.
- BETWEEN can be combined with other SQL conditions using AND, OR, etc.
Alternative Syntax:
If you need to reverse the bounds or check for values not in a certain range, you can also use the NOT BETWEEN operator:
SELECT *
FROM Products
WHERE Price NOT BETWEEN 10 AND 50;
Using BETWEEN is a straightforward way to filter data within a specified range effectively.