SQL BETWEEN Statement

1. Overview

The BETWEEN operator simplifies range-based filtering by checking if a value lies within an inclusive lower and upper bound. It works with numeric, text, and date/time data types.

expression BETWEEN low_bound AND high_bound

Equivalent to expression >= low_bound AND expression <= high_bound.

2. Basics

2.1 Numeric Ranges

SELECT id, amount
FROM transactions
WHERE amount BETWEEN 100 AND 500;

Explanation: Returns transactions whose amount is at least 100 and at most 500.

2.2 Date Ranges

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

Explanation: Fetches orders placed in the first quarter of 2025 (inclusive of boundaries).

2.3 Text Ranges (Lexicographical)

SELECT product_name
FROM products
WHERE product_name BETWEEN 'A' AND 'M';

Explanation: Selects products whose names start with letters A through M, based on lexicographical ordering.

3. Intermediate Usage

3.1 NOT BETWEEN

SELECT id, amount
FROM transactions
WHERE amount NOT BETWEEN 100 AND 500;

Explanation: Finds transactions with amount less than 100 or greater than 500.

3.2 Combining with AND/OR

SELECT *
FROM employees
WHERE salary BETWEEN 60000 AND 90000
  AND hire_date BETWEEN '2024-01-01' AND '2024-12-31';

Explanation: Filters for employees earning between 60k–90k who were hired in 2024.

3.3 Subquery with BETWEEN

SELECT account_id, balance
FROM accounts
WHERE balance BETWEEN (
  SELECT MIN(balance) FROM accounts WHERE branch='001'
) AND (
  SELECT MAX(balance) FROM accounts WHERE branch='001'
);

Explanation: Selects accounts with balances between the minimum and maximum values for branch 001, dynamically determined via subqueries.

4. Advanced Techniques

4.1 Dynamic Boundaries via CTEs

WITH bounds AS (
  SELECT MIN(amount) AS low, MAX(amount) AS high
  FROM transactions
  WHERE type='refund'
)
SELECT id, amount
FROM transactions, bounds
WHERE amount BETWEEN bounds.low AND bounds.high;

Explanation: The CTE bounds computes dynamic low/high values. The main query filters accordingly.

4.2 Window Functions with BETWEEN

SELECT id, order_date,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
  ) AS rolling_week_total
FROM orders;

Explanation: Calculates a 7-day rolling sum of amount for each order_date, using the RANGE BETWEEN syntax.

4.3 Handling NULLs

Since BETWEEN returns unknown if any operand is NULL, explicitly guard against NULL if needed:

SELECT *
FROM data_points
WHERE value IS NOT NULL
  AND value BETWEEN lower_bound AND upper_bound;

4.4 Performance Considerations

5. Combined Example

WITH date_bounds AS (
  SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '3' MONTH AS start_date,
         DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1' DAY   AS end_date
)
SELECT o.order_id, o.order_date, o.amount
FROM orders o, date_bounds db
WHERE o.order_date BETWEEN db.start_date AND db.end_date
  AND o.amount BETWEEN 50 AND 500
ORDER BY o.order_date;

Explanation: This query retrieves orders placed in the last three full months, with amounts between 50 and 500, sorted by date. The CTE calculates dynamic date bounds.

6. Summary

The BETWEEN operator, though simple in syntax, offers great flexibility when applied to static values, dynamic subqueries, window frames, and partitioned data. Mastering its variants and guard patterns equips you to write clear, concise, and performant range-based queries in SQL.