The BETWEEN operator in SQL is used to filter data within a specified range. It can be applied to numeric, date, or text data types. The syntax for using the BETWEEN operator is straightforward:
SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Key Points:
1. Inclusive Range: The BETWEEN operator is inclusive, meaning it includes the endpoints. For instance, if you use BETWEEN 10 AND 20, it will return records where the value is greater than or equal to 10 and less than or equal to 20.
2. Data Types: You can use BETWEEN with different data types:
- Numeric: For example, filtering sales records in a range of amounts.
- Dates: For example, filtering records between two dates.
- Text: For strings, BETWEEN compares lexicographically.
Example Queries:
1. Filtering Numeric Values:
SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 50;
2. Filtering Dates:
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
3. Filtering Text:
SELECT employee_id, last_name
FROM employees
WHERE last_name BETWEEN 'A' AND 'M';
Notes:
- The BETWEEN operator can be used alongside other operators (like AND, OR) and conditions.
- When filtering with dates, ensure that the date format is compatible with your database system.
- The values used in the BETWEEN statement are evaluated based on the data type of the column being filtered.
Using the BETWEEN operator can simplify your queries and make them more readable by clearly defining a range of values.