How To Use Between In Sql Query

The BETWEEN operator in SQL is a fundamental tool for filtering data within a specific range. Which means it allows developers and analysts to retrieve rows where a column's value falls inclusively between two boundary values. Whether you are working with numbers, dates, or even text strings, mastering this operator simplifies query logic and improves readability compared to chaining multiple comparison operators Took long enough..

The official docs gloss over this. That's a mistake.

Understanding the Syntax and Core Logic

At its heart, the syntax is straightforward. You specify the column name, followed by the BETWEEN keyword, the lower bound, the AND keyword, and finally the upper bound Easy to understand, harder to ignore..

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

The critical concept to internalize is inclusivity. Plus, mathematically, this translates to column_name >= value1 AND column_name <= value2. The operator includes both value1 and value2 in the result set. This behavior differs from some programming languages where range functions might exclude the upper bound, so always remember that the boundaries are part of the match.

The operator works naturally with SELECT, UPDATE, DELETE, and INSERT statements, making it versatile for data retrieval and manipulation tasks.

Practical Applications with Numeric Data

Numeric columns are the most common use case. Imagine a scenario involving an Products table where you need to find items priced for a mid-range market segment.

Basic Range Filtering

To find products costing between $50 and $100 (inclusive), the query looks like this:

SELECT product_name, price
FROM Products
WHERE price BETWEEN 50 AND 100;

This returns every product where the price is exactly 50, exactly 100, or any value in between. It is cleaner and often more performant for the query optimizer to parse than writing WHERE price >= 50 AND price <= 100 Worth knowing..

Handling Boundary Order

A common pitfall occurs when the lower and upper bounds are swapped accidentally. Standard SQL requires the lower bound first. If you write BETWEEN 100 AND 50, most database engines (like PostgreSQL, MySQL, SQL Server) will return an empty result set because no number can be simultaneously greater than or equal to 100 and less than or equal to 50. Always verify that your first value is the minimum and the second is the maximum And that's really what it comes down to..

Working with Date Ranges

Date filtering is arguably where BETWEEN shines brightest in reporting and analytics. Still, it introduces nuances regarding time components that often trap developers.

The "Midnight" Trap

Consider an Orders table with a order_date column of type DATETIME or TIMESTAMP. If you run:

SELECT * FROM Orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

You might miss orders placed on January 31st after 00:00:00 (midnight). Because the string '2023-01-31' implicitly casts to '2023-01-31 00:00:00', the range effectively ends at the very start of the last day.

The Best Practice Solution

To capture the entire final day, you have two dependable strategies:

1. Explicit Time Stamps: Define the upper bound to the last second of the day.

   WHERE order_date BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
   

2. Half-Open Intervals (Recommended): Use >= for the start and < for the day after the end. This avoids precision issues with milliseconds or nanoseconds.

   WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01'
   

While the second approach technically avoids BETWEEN, understanding why BETWEEN fails here is crucial for writing bug-free date queries.

Filtering Text and String Data

BETWEEN is not limited to numbers and dates; it works on string columns using lexicographical (dictionary) ordering. This is useful for alphabetical ranges.

Here's one way to look at it: to find customers whose last names fall between 'G' and 'M':

SELECT first_name, last_name
FROM Customers
WHERE last_name BETWEEN 'G' AND 'M';

Important Nuance: This includes names starting with 'G' and 'M', but because of string comparison rules, it includes all names starting with 'M' (like 'Miller', 'Moore') but stops before 'N'. Still, be aware of collation and case sensitivity. In a case-insensitive collation (default in SQL Server, MySQL), 'g' equals 'G'. In a case-sensitive collation (common in PostgreSQL default), 'g' comes after 'M' (since lowercase ASCII values are higher than uppercase), potentially returning unexpected results. Always know your database collation settings when filtering text.

Combining with Logical Operators

Real-world queries rarely rely on a single condition. BETWEEN integrates naturally with AND, OR, and IN.

Multiple Range Conditions

To find products priced 10-20 OR 50-60:

SELECT * FROM Products
WHERE price BETWEEN 10 AND 20
   OR price BETWEEN 50 AND 60;

Mixing with IN Operator

To find orders in a specific date range for specific customers:

SELECT * FROM Orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
  AND customer_id IN (101, 102, 105);

Using NOT BETWEEN

The negation operator NOT BETWEEN selects values outside the range. It is logically equivalent to column < value1 OR column > value2.

-- Find products cheaper than 10 OR more expensive than 100
SELECT * FROM Products
WHERE price NOT BETWEEN 10 AND 100;

It's excellent for outlier detection or data quality checks, such as finding negative prices or impossibly high quantities That alone is useful..

Subqueries and Dynamic Ranges

Hardcoding values is fine for ad-hoc analysis, but production queries often need dynamic ranges derived from the data itself. You can use subqueries to define the boundaries.

Example: Employees Earning Above Average but Below Max

SELECT first_name, last_name, salary
FROM Employees
WHERE salary BETWEEN (SELECT AVG(salary) FROM Employees)
                 AND (SELECT MAX(salary) FROM Employees);

This dynamically calculates the range every time the query runs, ensuring the report stays accurate as salary data changes.

Performance Considerations and Indexing

From a database administration perspective, BETWEEN is generally SARGable (Search ARGument Able). This means the query optimizer can make use of an index on the filtered column to perform an Index Range Scan rather than a full Table Scan And it works..

When Indexes Work Well

If you have an index on price, the query WHERE price BETWEEN 50 AND 100 will seek the starting point (50) in the B-Tree index and scan forward until it hits 100. This is highly efficient.

When Performance Degrades

1. Functions on Columns: WHERE YEAR(order_date) BETWEEN 2022 AND 2023 prevents index usage on order_date because the function

...because the function is applied to the column rather than the raw value, forcing the database to evaluate every row instead of seeking directly in the index. To preserve index usage, apply the function to the literal value instead, or store precomputed values in a generated column.

Implicit Conversions and Data Types

Comparing incompatible types—such as a string column against an integer, or a VARCHAR against a DATE without explicit casting—can trigger implicit conversion. Depending on the engine, this may also render the index unusable or produce surprising sort orders. Always match the literal type to the column type, or cast explicitly:

-- Safe: explicit cast matches column type
WHERE order_date BETWEEN CAST('2023-01-01' AS DATE) AND CAST('2023-01-31' AS DATE)

NULL Handling

BETWEEN follows standard three-valued logic. In practice, if the column, lower bound, or upper bound is NULL, the expression evaluates to UNKNOWN, and the row is excluded. It does not match NULL.

-- This will not return rows where price is NULL
SELECT *