Learn Core SQL Data Query Language
Lesson 30

BETWEEN in SQL: Filter Values Within a Range

Use BETWEEN to filter rows within a numeric, date, or text range with inclusive boundaries in a single readable expression.

Filtering orders within a date range, finding products in a price band, or identifying employees whose salary falls within a pay grade are all everyday analyst tasks. You could write each one as two separate >= and <= conditions, but BETWEEN keeps the intent clearer in a single expression.

BETWEEN with Numbers

BETWEEN takes a lower bound and an upper bound separated by AND. Both boundaries are inclusive.

-- Products priced between 25 and 100
SELECT p.product_name, p.price, p.brand, p.stock_qty
FROM products p
WHERE p.price BETWEEN 25 AND 100
  AND p.is_active = 1
ORDER BY p.price;

-- Orders with total amount between 100 and 500
SELECT o.order_id, o.order_date, o.status, o.total_amount
FROM orders o
WHERE o.total_amount BETWEEN 100 AND 500
ORDER BY o.total_amount;

-- Order items where quantity ordered is between 2 and 5
SELECT oi.item_id, oi.order_id, oi.product_id, oi.quantity, oi.line_total
FROM order_items oi
WHERE oi.quantity BETWEEN 2 AND 5
ORDER BY oi.quantity DESC;

The lower bound must come first. If you put the higher number first, BETWEEN returns zero rows.

BETWEEN with Dates

Date strings in ISO format sort chronologically, so BETWEEN works correctly on them. Both boundary dates are included.

-- Orders placed in Q1 2024
SELECT o.order_id, o.order_date, o.status, o.total_amount
FROM orders o
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY o.order_date;

-- Employees hired between 2021 and 2023 inclusive
SELECT e.first_name, e.last_name, e.role, e.hire_date, e.salary
FROM employees e
WHERE e.hire_date BETWEEN '2021-01-01' AND '2023-12-31'
ORDER BY e.hire_date;
Key idea: When using BETWEEN on datetime columns that store both date and time, be careful with the upper boundary. BETWEEN '2024-03-31' AND '2024-03-31' only matches rows with exactly that date string. If your data contains timestamps like '2024-03-31 14:30:00', those rows are excluded because the string '2024-03-31 14:30:00' is greater than '2024-03-31'. Use o.order_date < '2024-04-01' with >= for datetime columns to be safe.
-- Safe date range for datetime columns
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.order_date >= '2024-01-01'
  AND o.order_date < '2024-04-01'
ORDER BY o.order_date;

NOT BETWEEN

NOT BETWEEN returns rows where the column value falls outside the specified range. It excludes both boundary values and everything in between.

-- Products outside the mid-range price band (below 25 or above 150)
SELECT p.product_name, p.price, p.brand
FROM products p
WHERE p.price NOT BETWEEN 25 AND 150
  AND p.is_active = 1
ORDER BY p.price;

-- Orders outside normal value range (possible outliers)
SELECT o.order_id, o.order_date, o.total_amount, o.status
FROM orders o
WHERE o.total_amount NOT BETWEEN 20 AND 1000
ORDER BY o.total_amount DESC;

-- Employees with salary outside the 40000 to 80000 band
SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees e
WHERE e.salary NOT BETWEEN 40000 AND 80000
ORDER BY e.salary DESC;

BETWEEN vs >= AND <=

Both approaches return identical results. BETWEEN is cleaner when you have a clear lower and upper bound. Explicit >= and <= give you more flexibility when the bounds are not symmetric or when you need to mix other conditions.

-- These two queries return the same rows
SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.total_amount BETWEEN 200 AND 600;

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.total_amount >= 200
  AND o.total_amount <= 600;

Use explicit >= and < for datetime ranges as shown above. For simple number and date ranges, BETWEEN is the cleaner choice.

Common Mistakes

-- wrong
SELECT p.product_name, p.price
FROM products p
WHERE p.price BETWEEN 100 AND 50;

-- correct
SELECT p.product_name, p.price
FROM products p
WHERE p.price BETWEEN 50 AND 100;

The lower bound must always come first in BETWEEN. Reversing the order returns zero rows because no value can be simultaneously greater than 100 and less than 50.

---

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

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

Date boundaries follow the same rule as numbers: earlier date first, later date second. The reversed version returns no rows.

---

-- wrong
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date BETWEEN '2024-06-01' AND '2024-06-30';
-- Misses rows with timestamps like '2024-06-30 15:45:00'

-- correct
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_date >= '2024-06-01'
  AND o.order_date < '2024-07-01';

When the column stores datetime values rather than date-only values, BETWEEN with a date-only upper bound misses rows that occur on the last day but after midnight. Use >= start AND < next_day for full-day coverage.

Quick Recap

  • BETWEEN lower AND upper is inclusive on both ends. It is exactly equivalent to >= lower AND <= upper.
  • The lower bound must always be the smaller value. Reversed bounds return zero rows without any error.
  • For datetime columns, avoid BETWEEN on the upper boundary. Use >= start AND < next_day to capture all timestamps within the range.
Exercise 1 Easy
Write a query that returns product_name, price, and stock_qty from the products table for all active products where price is between 30 and 80 inclusive. Sort by price ascending.
Hint: Use WHERE p.price BETWEEN 30 AND 80 AND p.is_active = 1.
Solution
SELECT p.product_name, p.price, p.stock_qty FROM products p WHERE p.price BETWEEN 30 AND 80 AND p.is_active = 1 ORDER BY p.price ASC;
Exercise 2 Medium
Write a query that returns employee_id, first_name, last_name, department, salary, and hire_date from the employees table for employees with a salary between 45000 and 75000 and a hire date between '2020-01-01' and '2023-12-31'. Sort by salary descending.
Hint: Use two BETWEEN conditions combined with AND. One for salary and one for hire_date.
Solution
SELECT e.employee_id, e.first_name, e.last_name, e.department, e.salary, e.hire_date FROM employees e WHERE e.salary BETWEEN 45000 AND 75000 AND e.hire_date BETWEEN '2020-01-01' AND '2023-12-31' ORDER BY e.salary DESC;
Exercise 3 Hard
Find all products whose total revenue across all order items falls between 500 and 5000. Show product_name, price, and total_revenue as the sum of line_total rounded to 2 decimal places. Join products and order_items. Sort by total_revenue descending.
Hint: Join on product_id. Use SUM(oi.line_total) grouped by product. Apply the BETWEEN filter in HAVING using the aggregate expression, not the alias.
Solution
SELECT p.product_name, p.price, ROUND(SUM(oi.line_total), 2) AS total_revenue FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_id, p.product_name, p.price HAVING SUM(oi.line_total) BETWEEN 500 AND 5000 ORDER BY total_revenue DESC;
Frequently Asked Questions

BETWEEN filters rows where a column value falls within a specified range, inclusive of both the lower and upper boundary values.

Yes. BETWEEN includes both boundary values. WHERE price BETWEEN 10 AND 50 returns rows where price equals 10, equals 50, or falls anywhere in between.

Yes. BETWEEN works on date strings in ISO format. The lower date goes first and the upper date second. Both boundary dates are included in the result.

They produce identical results. BETWEEN 10 AND 50 is exactly equivalent to >= 10 AND <= 50. BETWEEN is simply shorter and easier to read.