SR

BETWEEN

Filter values within a range — inclusive on both ends

Beginner 8 minBETWEENrange

Range Filtering

BETWEEN checks if a value falls within a range. It's inclusive on both ends — BETWEEN 10 AND 50 includes both 10 and 50 themselves. Here's what to know:

Equivalent to AND — WHERE price BETWEEN 20 AND 100 behaves identically to WHERE price >= 20 AND price <= 100. BETWEEN is simply more readable, especially for date ranges and numeric bands in business queries.
Works with dates — For dates stored in ISO format (YYYY-MM-DD), BETWEEN handles date ranges correctly because alphabetical order matches chronological order. Example: BETWEEN '2024-01-01' AND '2024-12-31' covers the full year.
NOT BETWEEN — Excludes the range and returns values below the lower bound or above the upper bound. Useful for finding outliers or records that fall outside a normal expected range.
Timestamp pitfall — For timestamp columns, BETWEEN '2024-01-01' AND '2024-12-31' is interpreted as BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 00:00:00', which misses every time on Dec 31 after midnight. Use a half-open interval instead: ts >= '2024-01-01' AND ts < '2025-01-01'.
Knowledge Canvas

How BETWEEN Works

Filter within a range (inclusive)

  • BETWEEN includes both endpoints — it's [low, high]
  • Equivalent to: col >= low AND col <= high
  • Works with numbers, dates, and strings
  • NOT BETWEEN excludes the range

BETWEEN Gotchas

Inclusivity traps

  • BETWEEN is inclusive on BOTH ends — off-by-one risk with dates
  • Date BETWEEN '2024-01-01' AND '2024-01-31' misses Jan 31 timestamps
  • Reversed order: BETWEEN 100 AND 10 → always empty
  • NULLs: NULL BETWEEN 1 AND 10 → NULL (excluded)

Date Range Patterns

Safe date filtering

  • Use >= and < for dates: date >= '2024-01-01' AND date < '2024-02-01'
  • BETWEEN works cleanly for date-only columns
  • For timestamps, exclusive end avoids midnight issues
  • Combine with date functions for dynamic ranges

BETWEEN Rules

Keep these in mind

  • Always low value first: BETWEEN small AND large
  • Both bounds are included in results
  • Works with any comparable type
  • NOT BETWEEN = outside the range (exclusive)
Syntax
Syntax Template
1WHERE column BETWEEN low
2 AND high;
3
4-- Exclude a range
5-- WHERE column NOT BETWEEN low
6-- AND high;
BETWEEN a AND bInclusive range: a <= column <= b
NOT BETWEENOutside the range: column < a OR column > b
Sample Data
products
10 rows
nameprice
Wireless Mouse24.99
Office Desk189
LED Desk Lamp45.5
USB-C Cable12.99
Bluetooth Speaker79
Office Chair95
Coffee Maker89.99
Notebook Set8.5
Wireless Headphones159
Standing Desk425
orders
12 rows
idorder_datetotal
50012024-01-1559.98
50022024-02-08449
50032024-02-2012.5
50042024-03-0589.99
50052024-03-15245
50062024-03-22159
50072024-04-0245.5
50082024-04-1279
50092024-04-22425
50102024-05-0518.99
50112024-05-18134
50122024-06-0865.5

Worked Example

Find products priced between 20 and 100.

SQL
1SELECT
2 name,
3 price
4FROM products
5WHERE price BETWEEN 20
6 AND 100
7ORDER BY
8 price;
BETWEEN 20 AND 100 includes both endpoints. Products at exactly 20 or 100 would be included. USB Hub (19.99) is just below the range, so it is excluded.
Output
5 rows
nameprice
Wireless Mouse24.99
LED Desk Lamp45.5
Bluetooth Speaker79
Coffee Maker89.99
Office Chair95

Key Concepts

1Both boundaries are included: BETWEEN 10 AND 50 includes 10 and 50
2Date ranges work in ISO format: BETWEEN '2024-01-01' AND '2024-12-31'
3BETWEEN is shorthand for >= AND <= — identical behavior
4NOT BETWEEN returns values outside the range

Pro Tip

Range queries are everywhere in business analysis — date ranges, price bands, age groups, score brackets.

When to Use

Orders within a date range, products in a price band, employees within an age bracket, scores within a grade range.

Challenge

Solve the problem below

Find all orders from the orders table placed between '2024-03-01' and '2024-04-30'. Show id, order_date, and total.

Your Query

Previous

IN Operator

Next

LIKE & Pattern Matching