SQL BETWEEN Operator

The SQL BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Examples

Example 1: Using BETWEEN with Numbers

SELECT * FROM products
WHERE price BETWEEN 10 AND 20;

Example 2: Using BETWEEN with Dates

SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

Example 3: Using BETWEEN with Text

SELECT * FROM employees
WHERE last_name BETWEEN 'A' AND 'M';

Example 4: Using NOT BETWEEN

SELECT * FROM products
WHERE price NOT BETWEEN 10 AND 20;

Using BETWEEN with Different Data Types

The BETWEEN operator can be used with various data types, including strings, numbers, and dates.

Example 5: Using BETWEEN with Date and Time

SELECT * FROM events
WHERE event_time BETWEEN '2024-06-01 10:00:00' AND '2024-06-01 18:00:00';

Performance Considerations

Using the BETWEEN operator can be more efficient than multiple AND conditions, especially with indexed columns. However, ensure that the column data types and the values being compared are compatible to avoid unexpected results.

Case Sensitivity

The case sensitivity of the BETWEEN operator depends on the collation of the column. For example, in MySQL, a case-insensitive collation will treat ‘abc’ and ‘ABC’ as the same value.

Practical Tips

• Use BETWEEN when you need to filter results within a specific range.
• Ensure the data types of the column and the range values match to avoid errors.
• Consider using BETWEEN with indexed columns for better performance.

The BETWEEN operator is a useful tool for filtering SQL query results within a specified range, making your queries more concise and readable.