Mastering Range Filtering with the SQL BETWEEN Operator

When you want everything within a range — prices from $20 to $50, orders placed last quarter, scores between 80 and 90 — BETWEEN says it in one clean condition. It lives in the WHERE clause and is inclusive: both endpoints count. It’s the readable shorthand for a pair of comparisons.

Every example runs live in your browser against a small sample database. Edit the range, press Run, and watch which rows fall inside it.

Quick answer: what does BETWEEN do?

BETWEEN keeps rows whose value falls within a range, endpoints included:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN low AND high;

It is exactly equivalent to column_name >= low AND column_name <= high — just shorter and clearer. Note the order matters: the low value comes first.

Inclusive by design

The endpoints are part of the range. price BETWEEN 20 AND 50 returns products priced at 20 and at 50, not just the ones strictly between. Run the range next to its >=/<= twin and you’ll get identical results:

If you need to exclude the endpoints, BETWEEN can’t do it — switch to > and <.

Filtering date ranges

BETWEEN shines for dates. Because dates stored as YYYY-MM-DD sort chronologically, a date range reads naturally:

SELECT product, order_date
FROM orders
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

The date-boundary trap

Here’s the gotcha that quietly loses rows. If your column stores a date and time (a DATETIME / TIMESTAMP), then BETWEEN '2023-06-01' AND '2023-06-30' really means up to 2023-06-30 00:00:00 — so an order placed at 2023-06-30 14:00 is excluded. BETWEEN’s inclusive upper bound works against you here.

The safe pattern for datetime columns is a half-open range — greater-or-equal the start, strictly-less-than the next day:

-- Catches every moment in June, regardless of time component
WHERE order_date >= '2023-06-01' AND order_date < '2023-07-01'

For pure DATE columns (like this playground’s), BETWEEN is perfectly safe. Reach for the half-open pattern the moment a time component enters the picture.

NOT BETWEEN — everything outside the range

Put NOT in front to keep the rows outside the range:

SELECT name, price
FROM products
WHERE price NOT BETWEEN 20 AND 50;

NOT BETWEEN low AND high is the same as column < low OR column > high — the complement of the inclusive range.

Practice

Frequently asked questions

Is the SQL BETWEEN operator inclusive? Yes. BETWEEN low AND high includes both endpoints — it’s equivalent to >= low AND <= high.

How do I write a date range with BETWEEN? WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31', low date first. For columns that store a time component, prefer the half-open range >= '2023-01-01' AND < '2024-01-01' so you don’t drop rows on the last day.

What is NOT BETWEEN? The complement: column NOT BETWEEN low AND high keeps rows outside the range, the same as column < low OR column > high.

Does BETWEEN work on text? Yes — it compares alphabetically — but it’s most useful for numbers and dates. For text you usually want LIKE or IN instead.

From a range to a pipeline

Range filtering is one condition among many. When the filtering has to run continuously across sources, ET1 turns it into a visual Filter node — the same range check, pointed at a CSV, a Postgres table, or an API, with a live preview of the rows inside the range.

Keep building your filtering toolkit: match a list of exact values with the IN operator, match text patterns with LIKE, or combine several conditions with AND, OR, and NOT.