BETWEEN range filtering in MySQL - Deep Dive

Overview - BETWEEN range filtering

What is it?

BETWEEN range filtering is a way to select rows in a database where a column's value falls within a specific range. It checks if a value is greater than or equal to a lower limit and less than or equal to an upper limit. This makes it easy to find data within boundaries like dates, numbers, or other ordered values. It is a simple and readable way to filter data based on ranges.

Why it matters

Without BETWEEN, filtering data by ranges would require writing multiple conditions with AND, which can be confusing and error-prone. BETWEEN makes queries shorter and clearer, helping people quickly find data like sales in a date range or prices within a budget. This improves productivity and reduces mistakes when working with databases.

Where it fits

Before learning BETWEEN, you should understand basic SQL SELECT queries and WHERE clauses. After mastering BETWEEN, you can learn about other filtering techniques like IN, LIKE, and advanced range queries using functions or subqueries.

Mental Model

Core Idea

BETWEEN checks if a value lies inside a closed range defined by two limits, including both ends.

Think of it like...

Imagine a hallway with two doors labeled with numbers. BETWEEN is like asking if a person’s badge number is between the two door numbers, including the doors themselves.

Value Range Check:
┌───────────────┐
│ Lower Limit   │
│     [x]       │
│               │
│   Value [v]   │
│               │
│ Upper Limit   │
│     [y]       │
└───────────────┘
Condition: x ≤ v ≤ y

Build-Up - 7 Steps

1

Foundation

Basic WHERE clause filtering

Concept: Learn how to filter rows using simple conditions in WHERE.

In SQL, the WHERE clause lets you pick rows that meet a condition. For example, to find products with price 100, you write: SELECT * FROM products WHERE price = 100; This returns only rows where price equals 100.

Result

Only rows with price exactly 100 are returned.

Understanding WHERE is essential because BETWEEN is a special kind of condition inside WHERE.

2

Foundation

Using AND for range filtering

Concept: Filter rows by combining two conditions with AND to create a range.

To find products priced between 50 and 100, you can write: SELECT * FROM products WHERE price >= 50 AND price <= 100; This checks both conditions and returns rows where price is in that range.

Result

Rows with price from 50 up to 100 inclusive are returned.

Combining conditions with AND works but can be verbose and repetitive.

3

Intermediate

Introduction to BETWEEN syntax

🤔Before reading on: do you think BETWEEN includes both the start and end values, or excludes one? Commit to your answer.

Concept: BETWEEN simplifies range filtering by combining two conditions into one readable statement.

BETWEEN syntax: SELECT * FROM products WHERE price BETWEEN 50 AND 100; This means price >= 50 AND price <= 100. It includes both limits.

Result

Same rows as the AND example are returned, but query is shorter and clearer.

BETWEEN improves readability and reduces errors by expressing range checks in one phrase.

4

Intermediate

BETWEEN with dates and strings

🤔Do you think BETWEEN works only with numbers, or can it also filter dates and text? Commit to your answer.

Concept: BETWEEN works with any data type that has an order, like dates and strings.

Example with dates: SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; This returns orders placed in January 2023. Example with strings: SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'M'; This returns users whose last names start with letters from A to M.

Result

Rows matching the date or string range are returned.

BETWEEN is versatile and works beyond numbers, as long as the data type can be compared in order.

5

Intermediate

BETWEEN is inclusive of boundaries

🤔If a value equals the lower or upper limit in BETWEEN, is it included or excluded? Commit to your answer.

Concept: BETWEEN includes both the start and end values in the range check.

For example: SELECT * FROM products WHERE price BETWEEN 50 AND 100; This includes products priced exactly 50 and exactly 100. If you want to exclude boundaries, you must use > and < operators instead.

Result

Rows with price equal to 50 or 100 are included.

Knowing BETWEEN is inclusive prevents bugs when exact boundary values matter.

6

Advanced

BETWEEN with NOT for exclusion

🤔Does NOT BETWEEN exclude values inside or outside the range? Commit to your answer.

Concept: NOT BETWEEN filters rows where the value is outside the specified range.

Example: SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100; This returns products priced less than 50 or greater than 100. It is equivalent to: SELECT * FROM products WHERE price < 50 OR price > 100;

Result

Rows outside the 50 to 100 range are returned.

Using NOT BETWEEN helps quickly find values outside a range without writing complex OR conditions.

7

Expert

BETWEEN behavior with NULL and data types

🤔If a column value is NULL, does BETWEEN include or exclude it? Commit to your answer.

Concept: BETWEEN returns false if the value is NULL, and behavior depends on data type collation and comparison rules.

If a value is NULL, BETWEEN returns no rows because NULL means unknown. Also, for strings, collation affects order, so BETWEEN may behave differently depending on database settings. Example: SELECT * FROM users WHERE last_name BETWEEN 'A' AND 'M'; May vary results if collation treats uppercase and lowercase differently.

Result

NULL values are excluded; string comparisons depend on collation.

Understanding NULL and collation effects prevents unexpected missing rows and subtle bugs in production.

Under the Hood

BETWEEN is syntactic sugar for two comparisons joined by AND: value >= low AND value <= high. The database engine evaluates these comparisons using the column's data type rules and indexes if available. Internally, it uses comparison operators that rely on the data type's ordering logic. For NULL values, comparisons return unknown, so BETWEEN excludes them. For strings, collation rules define how order is determined, affecting BETWEEN results.

Why designed this way?

BETWEEN was designed to simplify common range queries that otherwise require writing two conditions with AND. It improves query readability and reduces errors. The inclusive nature matches common expectations for ranges in everyday language. Alternatives like separate conditions were more verbose and error-prone, so BETWEEN became a standard SQL feature.

BETWEEN Evaluation Flow:
┌───────────────┐
│ Input Value v │
└──────┬────────┘
       │
       ▼
┌───────────────┐     ┌───────────────┐
│ v >= Lower x? │ --> │ True or False │
└──────┬────────┘     └──────┬────────┘
       │                   │
       ▼                   ▼
┌───────────────┐     ┌───────────────┐
│ v <= Upper y? │ --> │ True or False │
└──────┬────────┘     └──────┬────────┘
       │                   │
       ▼                   ▼
    True AND True = True  Otherwise False
       │
       ▼
  Row included in result

Myth Busters - 4 Common Misconceptions

Quick: Does BETWEEN exclude the boundary values or include them? Commit to your answer.

Common Belief: BETWEEN excludes the start and end values, only selecting values strictly inside the range.

Reality: BETWEEN includes both the start and end values in the range check (it is inclusive).

Why it matters: Assuming exclusivity causes missing rows exactly on the boundaries, leading to incomplete or incorrect query results.

Quick: Does BETWEEN work with strings and dates just like numbers? Commit to your answer.

Common Belief: BETWEEN only works with numeric values, not with strings or dates.

Reality: BETWEEN works with any data type that has a defined order, including strings and dates.

Why it matters: Limiting BETWEEN to numbers prevents using a simple, readable syntax for common date or text range queries.

Quick: If a column value is NULL, does BETWEEN return true or false? Commit to your answer.

Common Belief: BETWEEN treats NULL values as matching the range if the limits are correct.

Reality: BETWEEN returns false for NULL values because NULL means unknown, so those rows are excluded.

Why it matters: Ignoring NULL behavior can cause confusion when expected rows with NULL values do not appear in results.

Quick: Does NOT BETWEEN select values inside or outside the range? Commit to your answer.

Common Belief: NOT BETWEEN selects values inside the range, just like BETWEEN but negated.

Reality: NOT BETWEEN selects values outside the range, meaning less than the lower limit or greater than the upper limit.

Why it matters: Misunderstanding NOT BETWEEN leads to wrong filtering logic and unexpected query results.

Expert Zone

1

BETWEEN uses the column's collation and data type comparison rules, so string ranges can behave differently depending on database settings.

2

Indexes on columns can speed up BETWEEN queries, but the database optimizer may rewrite BETWEEN as two comparisons internally.

3

NULL values are always excluded from BETWEEN results because comparisons with NULL return unknown, which is treated as false in WHERE.

When NOT to use

BETWEEN is not suitable when you need exclusive ranges (excluding boundaries), or when filtering complex conditions that depend on multiple columns. In such cases, use explicit comparison operators with AND/OR. Also, for non-continuous ranges, use IN or multiple OR conditions instead.

Production Patterns

BETWEEN is widely used in production for filtering date ranges in reports, price ranges in e-commerce, and numeric thresholds in monitoring systems. It is often combined with indexes for performance and used in stored procedures and dynamic queries for flexible filtering.

Connections

Inequality operators (>, <, >=, <=)

BETWEEN is a shorthand for combining two inequality operators with AND.

Understanding BETWEEN as a combination of inequalities helps grasp its inclusiveness and how to write equivalent queries.

Date and time filtering

BETWEEN is commonly used to filter rows within date or time ranges.

Knowing how BETWEEN works with dates helps in building reports and analytics that depend on time intervals.

Range queries in search engines

BETWEEN in SQL is similar to range queries in search engines that filter documents by numeric or date ranges.

Recognizing this connection helps understand how different systems implement range filtering efficiently.

Common Pitfalls

#1 Assuming BETWEEN excludes boundary values

Wrong approach:

SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- expecting prices > 50 and < 100 only

Correct approach:

SELECT * FROM products WHERE price > 50 AND price < 100;

Root cause:

Misunderstanding that BETWEEN is inclusive, leading to wrong assumptions about which rows are returned.

#2 Using BETWEEN with NULL values expecting matches

Wrong approach:

SELECT * FROM orders WHERE delivery_date BETWEEN '2023-01-01' AND '2023-01-31';
-- expecting rows with NULL delivery_date included

Correct approach:

SELECT * FROM orders WHERE delivery_date BETWEEN '2023-01-01' AND '2023-01-31' OR delivery_date IS NULL;

Root cause:

Not knowing that NULL values are excluded by BETWEEN because comparisons with NULL return unknown.

#3 Using NOT BETWEEN but expecting it to select inside range

Wrong approach:

SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;
-- expecting prices between 50 and 100

Correct approach:

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

Root cause:

Confusing NOT BETWEEN logic, which actually selects values outside the range.

Key Takeaways

BETWEEN is a simple, readable way to filter rows where a value lies within a range, including both boundaries.

It works with numbers, dates, and strings as long as the data type has an order.

BETWEEN is equivalent to combining two conditions with AND but is shorter and less error-prone.

NULL values are excluded from BETWEEN results because comparisons with NULL return unknown.

NOT BETWEEN selects values outside the range, which is useful for exclusion filters.