WHERE with BETWEEN range in SQL - Deep Dive

Overview - WHERE with BETWEEN range

What is it?

The WHERE clause in SQL is used to filter rows based on conditions. The BETWEEN operator allows you to specify a range of values to filter data inclusively. It checks if a value falls between two given values, including the boundaries. This helps select rows where a column's value lies within a specific range.

Why it matters

Filtering data by ranges is a common need in databases, such as finding sales between two dates or ages within a range. Without BETWEEN, you would need multiple conditions combined with AND, making queries longer and harder to read. BETWEEN simplifies this and makes queries clearer and less error-prone.

Where it fits

Before learning WHERE with BETWEEN, you should understand basic SQL SELECT statements and the WHERE clause for filtering. After mastering BETWEEN, you can explore more complex filtering with other operators like IN, LIKE, and advanced range queries using functions or subqueries.

Mental Model

Core Idea

WHERE with BETWEEN checks if a value lies inclusively between two boundaries to filter rows easily.

Think of it like...

It's like checking if a number is between two fence posts on a path, including the posts themselves, to decide if you can walk through that section.

┌───────────────┐
│   Table Rows  │
└──────┬────────┘
       │ Apply WHERE
       ▼
┌─────────────────────────────┐
│ WHERE column BETWEEN low AND high │
└─────────────┬───────────────┘
              │
     Rows with column values
     ≥ low and ≤ high pass
              ▼
       ┌─────────────┐
       │ Filtered Set│
       └─────────────┘

Build-Up - 7 Steps

1

Foundation

Basic WHERE Clause Filtering

Concept: Learn how the WHERE clause filters rows based on a simple condition.

The WHERE clause lets you pick rows where a column matches a condition. For example, SELECT * FROM employees WHERE age = 30; returns employees aged exactly 30.

Result

Only rows where age is 30 are shown.

Understanding WHERE is essential because it lets you narrow down data to what you need.

2

Foundation

Using AND for Range Filtering

Concept: Learn how to filter rows within a range using two conditions combined with AND.

To find rows where a value is between two numbers, you can write: SELECT * FROM employees WHERE age >= 25 AND age <= 35; This returns employees aged between 25 and 35 inclusive.

Result

Rows with age from 25 to 35 are returned.

Using AND with two comparisons is the basic way to filter ranges before BETWEEN existed.

3

Intermediate

Introducing BETWEEN for Range Queries

🤔Before reading on: do you think BETWEEN includes the boundary values or excludes them? Commit to your answer.

Concept: BETWEEN simplifies range filtering by checking if a value lies between two boundaries inclusively.

Instead of writing age >= 25 AND age <= 35, you can write: SELECT * FROM employees WHERE age BETWEEN 25 AND 35; This returns the same result but is shorter and easier to read.

Result

Rows with age from 25 to 35 inclusive are returned.

Knowing BETWEEN reduces query length and improves readability for range filters.

4

Intermediate

BETWEEN with Dates and Text

🤔Before reading on: can BETWEEN be used with dates and text, or only numbers? Commit to your answer.

Concept: BETWEEN works with numbers, dates, and text because SQL compares values based on their order.

You can filter dates: SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; or text: SELECT * FROM products WHERE name BETWEEN 'A' AND 'M'; This selects rows where order_date or name falls in the specified range.

Result

Rows with dates in January 2023 or names starting from A to M are returned.

Understanding that BETWEEN works on ordered data types expands its usefulness beyond numbers.

5

Intermediate

BETWEEN is Inclusive of Boundaries

🤔Before reading on: does BETWEEN include the boundary values or exclude them? Commit to your answer.

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

For example, SELECT * FROM employees WHERE age BETWEEN 25 AND 35; includes employees aged exactly 25 and exactly 35. This is different from some programming languages where ranges might exclude the end.

Result

Rows with age equal to 25 or 35 are included.

Knowing BETWEEN is inclusive prevents off-by-one errors in queries.

6

Advanced

BETWEEN with NOT for Exclusion

🤔Before reading on: does NOT BETWEEN exclude values inside or outside the range? Commit to your answer.

Concept: You can use NOT BETWEEN to exclude rows with values inside a range.

For example, SELECT * FROM employees WHERE age NOT BETWEEN 25 AND 35; returns employees younger than 25 or older than 35, excluding those in the range.

Result

Rows with age less than 25 or greater than 35 are returned.

Understanding NOT BETWEEN lets you filter out ranges easily without complex conditions.

7

Expert

BETWEEN and Index Optimization

🤔Before reading on: do you think BETWEEN queries can use indexes efficiently or not? Commit to your answer.

Concept: BETWEEN queries can use indexes on columns to speed up data retrieval because they translate to range scans.

When a column has an index, a query like SELECT * FROM employees WHERE age BETWEEN 25 AND 35; can quickly find matching rows by scanning the index range instead of scanning the whole table.

Result

Query runs faster on large tables with indexes.

Knowing how BETWEEN interacts with indexes helps write performant queries in production.

Under the Hood

BETWEEN is syntactic sugar for two comparisons joined by AND: value >= low AND value <= high. The database engine parses BETWEEN and converts it internally to these comparisons. When an index exists on the column, the engine performs a range scan on the index tree, quickly locating the start and end points and retrieving matching rows without scanning the entire table.

Why designed this way?

BETWEEN was designed to simplify common range queries, making SQL easier to write and read. Internally, using two comparisons joined by AND was already supported, so BETWEEN was added as a readable shortcut. This design balances simplicity for users and efficiency for engines, leveraging existing comparison logic and index structures.

┌───────────────┐
│ SQL Query     │
│ WHERE col BETWEEN low AND high │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Parser converts BETWEEN to   │
│ (col >= low) AND (col <= high) │
└─────────────┬───────────────┘
              │
       ┌──────┴───────┐
       │              │
┌─────────────┐  ┌─────────────┐
│ Index Range │  │ Table Scan  │
│ Scan Start  │  │ if no index  │
│ to End      │  └─────────────┘
└─────────────┘
       │
       ▼
┌─────────────┐
│ Filtered Rows│
└─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does BETWEEN exclude the boundary values? Commit to yes or no.

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

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

Why it matters: If you assume boundaries are excluded, you might miss rows with values exactly equal to the range limits, causing incomplete results.

Quick: Can BETWEEN be used with text columns? Commit to yes or no.

Common Belief: BETWEEN only works with numbers and dates, not with text or strings.

Reality: BETWEEN works with any data type that has a defined order, including text, because SQL compares strings lexicographically.

Why it matters: Limiting BETWEEN to numbers prevents you from using it for useful text range queries, like filtering names alphabetically.

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

Common Belief: NOT BETWEEN includes values inside the range, just negated.

Reality: NOT BETWEEN excludes values inside the range and includes those outside it.

Why it matters: Misunderstanding NOT BETWEEN can cause you to select the wrong rows, leading to incorrect data analysis.

Quick: Does BETWEEN always use indexes for faster queries? Commit to yes or no.

Common Belief: BETWEEN queries never use indexes and always scan the whole table.

Reality: BETWEEN queries can use indexes efficiently if the column is indexed, performing fast range scans.

Why it matters: Assuming no index use might lead to writing inefficient queries or missing optimization opportunities.

Expert Zone

1

BETWEEN's inclusiveness can cause subtle bugs when filtering floating-point numbers due to precision issues.

2

Using BETWEEN on non-indexed columns in large tables can cause slow queries, so indexing strategy matters.

3

BETWEEN with text depends on collation settings, which affect how strings are ordered and compared.

When NOT to use

BETWEEN is not suitable when you need exclusive ranges (excluding boundaries), or when filtering complex conditions that involve multiple columns or non-contiguous ranges. In such cases, use explicit AND/OR conditions or advanced SQL features like window functions or subqueries.

Production Patterns

BETWEEN is commonly used in production for date range filters (e.g., sales in a month), numeric ranges (e.g., age groups), and alphabetical filters (e.g., names starting with certain letters). It is often combined with indexes for performance and used in reporting dashboards and data validation queries.

Connections

Inequality Operators (>=, <=)

BETWEEN is syntactic sugar for combining >= and <= operators with AND.

Understanding BETWEEN as a shortcut for inequalities helps grasp its behavior and limitations.

Index Range Scans

BETWEEN queries often translate to index range scans in databases.

Knowing how BETWEEN uses indexes helps optimize query performance in large datasets.

Mathematical Intervals

BETWEEN corresponds to closed intervals [a, b] in math, including endpoints.

Recognizing this connection clarifies why BETWEEN includes boundaries and how it relates to range concepts in math.

Common Pitfalls

#1 Assuming BETWEEN excludes boundary values.

Wrong approach:

SELECT * FROM employees WHERE age BETWEEN 25 AND 35; -- expecting ages 26 to 34 only

Correct approach:

SELECT * FROM employees WHERE age BETWEEN 25 AND 35; -- includes 25 and 35

Root cause:

Misunderstanding that BETWEEN is inclusive leads to wrong expectations about which rows are returned.

#2 Using BETWEEN with reversed boundaries.

Wrong approach:

SELECT * FROM orders WHERE order_date BETWEEN '2023-12-31' AND '2023-01-01';

Correct approach:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Root cause:

BETWEEN expects the lower boundary first and the higher second; reversing them returns no rows.

#3 Using BETWEEN on non-indexed large tables without considering performance.

Wrong approach:

SELECT * FROM big_table WHERE value BETWEEN 1000 AND 2000;

Correct approach:

CREATE INDEX idx_value ON big_table(value);
SELECT * FROM big_table WHERE value BETWEEN 1000 AND 2000;

Root cause:

Ignoring indexing causes full table scans, making queries slow on large datasets.

Key Takeaways

WHERE with BETWEEN filters rows by checking if a value lies inclusively between two boundaries.

BETWEEN is a concise and readable shortcut for combining two conditions with AND.

BETWEEN works with numbers, dates, and text because it relies on value ordering.

BETWEEN includes both boundary values, which is important to avoid off-by-one errors.

BETWEEN queries can use indexes efficiently, improving performance on large tables.