Previous
Written by: rJTutorial Team | Last Updated:

SQL BETWEEN Operator

The BETWEEN operator in SQL is a logical operator used to filter values within a specific, continuous range. Instead of writing multiple comparison operators like >= and <=, you can use BETWEEN to write cleaner, more readable queries. It is versatile and works with numeric data, text strings, and date values.

Developer Tip: Think of BETWEEN as a shorthand for (column >= value1 AND column <= value2). It makes your SQL code much easier for other team members to read and maintain.

 

Basic Syntax of BETWEEN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • column_name: The specific column you want to filter.
  • value1 and value2: The start and end points of your range. These values are inclusive.
Best Practice: Always place the lower value first (value1) and the higher value second (value2). Most SQL engines will return zero results if you swap them (e.g., BETWEEN 500 AND 100).

 

Key Features of BETWEEN

1. Inclusive Range

One of the most important things to remember is that the BETWEEN operator includes both the start and end values you provide in the results.

SELECT product_name, price  
FROM products  
WHERE price BETWEEN 100 AND 500;
  • This query retrieves products where the price is exactly 100, exactly 500, or anything in the middle.
Common Mistake: Forgetting that the boundaries are inclusive. If you want prices strictly between 100 and 500 (excluding 100 and 500), you should use > and < operators instead.

2. Works with Dates

In real-world applications, BETWEEN is most commonly used to generate reports for specific timeframes, such as monthly sales or quarterly logs.

SELECT order_id, order_date  
FROM orders  
WHERE order_date BETWEEN '2024-01-01' AND '2024-06-30';
  • This retrieves every order placed from the very first second of January 1, 2024, through the end of June 30, 2024.
Watch Out: Be careful with DATETIME columns. If your column contains timestamps (like 2024-06-30 14:30:00), a search BETWEEN '2024-01-01' AND '2024-06-30' might exclude items from the final day because the database defaults the end time to 00:00:00.

3. Works with Text Values

When used with strings, BETWEEN filters based on alphabetical (dictionary) order. This is useful for finding ranges of names or categories.

SELECT name  
FROM customers  
WHERE name BETWEEN 'A' AND 'M';
  • This retrieves names starting with A, B, C, and so on, up to (and including) names that are exactly 'M'.
Developer Tip: Alphabetical filtering can be tricky. A name like 'Miller' might be excluded if you search BETWEEN 'A' AND 'M' because 'Miller' comes after 'M' in the dictionary. To include all 'M' names, use BETWEEN 'A' AND 'Mz' or 'N'.

4. NOT BETWEEN

If you need to find "outliers" or data points that fall outside of a specific range, you can use NOT BETWEEN.

SELECT product_name, price  
FROM products  
WHERE price NOT BETWEEN 100 AND 500;
  • This retrieves products that are either cheaper than 100 or more expensive than 500.

 

Example Queries

1. Filter Products by Price Range

Useful for e-commerce "price filter" features where a user selects a budget range.

SELECT product_name, price  
FROM products  
WHERE price BETWEEN 50 AND 200;

2. Filter Orders by Date Range

Standard for financial reporting and auditing.

SELECT order_id, customer_id, order_date  
FROM orders  
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';

3. Exclude Salaries Outside a Range

Often used by HR departments to identify entry-level roles or executive-level roles by excluding the middle-management pay scale.

SELECT name, salary  
FROM employees  
WHERE salary NOT BETWEEN 3000 AND 7000;

4. Filter Names Alphabetically

SELECT name  
FROM employees  
WHERE name BETWEEN 'D' AND 'K';

 

Combining BETWEEN with Other Conditions

In a production environment, you rarely filter by just one column. BETWEEN can be seamlessly combined with AND, OR, and IN clauses to build complex business logic.

SELECT name, salary, department  
FROM employees  
WHERE salary BETWEEN 4000 AND 8000 
AND department = 'IT'
AND status = 'Active';
  • This query targets active IT staff who fall within a specific "mid-tier" salary bracket.
Best Practice: When combining BETWEEN with OR conditions, use parentheses to ensure the database engine evaluates your logic in the correct order.

 

Summary

The BETWEEN operator is an essential tool for any SQL developer's toolkit. It provides a clean and readable way to filter inclusive ranges across numbers, dates, and strings. By understanding its inclusive nature and how it handles different data types, you can write more efficient queries and avoid common pitfalls like missing timestamp data or incorrect alphabetical boundaries.

Previous