BETWEEN for range filtering in PostgreSQL - Deep Dive

Overview - BETWEEN for range filtering

What is it?

BETWEEN is a SQL keyword used to filter data within a specific range. It checks if a value falls between two boundary values, including those boundaries. This makes it easier to write queries that select rows where a column's value lies within a range. It is commonly used for numbers, dates, and other ordered data types.

Why it matters

Without BETWEEN, filtering data within a range would require multiple conditions combined with AND, making queries longer and harder to read. BETWEEN simplifies this, making queries clearer and less error-prone. This helps users quickly find data in ranges, like dates in a month or prices within a budget, improving data analysis and decision-making.

Where it fits

Learners should first understand basic SQL SELECT queries and WHERE clauses. After mastering BETWEEN, they can learn about other filtering techniques like IN, LIKE, and advanced range queries using functions or operators. This knowledge fits into the broader journey of mastering SQL data retrieval and filtering.

Mental Model

Core Idea

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

Think of it like...

BETWEEN is like checking if a number is inside a fence with two gates; if the number is anywhere between the gates or exactly at a gate, it passes.

Value Range Check:

┌───────────────┐
│   Lower Bound │
│       │       │
│       ▼       │
│  [value]      │
│       ▲       │
│   Upper Bound │
└───────────────┘

BETWEEN includes both Lower Bound and Upper Bound.

Build-Up - 7 Steps

1

Foundation

Basic WHERE clause filtering

Concept: Filtering rows based on a single condition using WHERE.

In SQL, the WHERE clause lets you select rows that meet a condition. For example, to find rows where age is 30, you write: SELECT * FROM people WHERE age = 30;

Result

Only rows where age equals 30 are returned.

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

2

Foundation

Using AND for range filtering

Concept: Filtering data within a range by combining two conditions with AND.

To find rows where age is between 20 and 30, you can write: SELECT * FROM people WHERE age >= 20 AND age <= 30; This checks both boundaries explicitly.

Result

Rows with age 20, 21, ..., 30 are returned.

Knowing how to filter ranges manually helps appreciate how BETWEEN simplifies this.

3

Intermediate

Introducing BETWEEN syntax

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

Concept: BETWEEN provides a concise way to filter values within a range, including boundaries.

BETWEEN syntax: column BETWEEN low AND high. For example: SELECT * FROM people WHERE age BETWEEN 20 AND 30; This returns rows where age is 20, 21, ..., 30.

Result

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

Understanding that BETWEEN includes boundaries prevents mistakes in filtering ranges.

4

Intermediate

BETWEEN with dates and strings

🤔Before reading on: do you think BETWEEN works only with numbers or also with dates and text? Commit to your answer.

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

Example with dates: SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; Example with strings: SELECT * FROM products WHERE name BETWEEN 'A' AND 'M'; BETWEEN compares values based on their order.

Result

Rows with order_date in January 2023 or product names starting with letters A to M are returned.

Knowing BETWEEN works beyond numbers expands its usefulness in real queries.

5

Intermediate

BETWEEN is inclusive of boundaries

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

Concept: BETWEEN includes both the lower and upper boundary values in the result set.

If you write: SELECT * FROM people WHERE age BETWEEN 20 AND 30; rows where age is exactly 20 or 30 are included. This is different from some programming languages where ranges exclude the end.

Result

Rows with age 20 and 30 appear in the output.

Knowing inclusivity avoids off-by-one errors in filtering data.

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: NOT BETWEEN filters rows where the value is outside the specified range.

Example: SELECT * FROM people WHERE age NOT BETWEEN 20 AND 30; returns rows where age is less than 20 or greater than 30. This is a quick way to exclude a range.

Result

Rows with age less than 20 or greater than 30 are returned.

Understanding NOT BETWEEN helps write queries that exclude ranges cleanly.

7

Expert

BETWEEN behavior with NULL and data types

🤔Before reading on: do you think BETWEEN returns rows where the value is NULL? Commit to your answer.

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

If a column value is NULL, BETWEEN returns no rows for that value because NULL means unknown. Also, for strings, collation rules affect ordering, so BETWEEN 'A' AND 'Z' depends on locale settings. For dates and numbers, ordering is straightforward.

Result

Rows with NULL values in the filtered column are excluded. String range results may vary by collation.

Knowing how NULL and collation affect BETWEEN prevents unexpected missing or included rows.

Under the Hood

BETWEEN is syntactic sugar for two comparisons combined with AND: value >= low AND value <= high. The database engine evaluates these comparisons using the column's data type ordering rules. For strings, collation settings determine order. NULL values cause the condition to evaluate to UNKNOWN, which filters out those rows. Internally, the optimizer may rewrite BETWEEN to these comparisons for execution.

Why designed this way?

BETWEEN was designed to simplify common range queries, making SQL easier to write and read. Instead of repeating two conditions, users write one concise expression. The inclusive nature matches common human expectations for ranges. Alternatives like separate comparisons were more verbose and error-prone.

BETWEEN Evaluation Flow:

┌─────────────┐
│   Input     │
│  (value)    │
└─────┬───────┘
      │
      ▼
┌─────────────┐
│ Compare >=  │
│  Lower Bound│
└─────┬───────┘
      │True?
      ▼
┌─────────────┐
│ Compare <=  │
│  Upper Bound│
└─────┬───────┘
      │True?
      ▼
┌─────────────┐
│  Result:    │
│   TRUE      │
└─────────────┘

If any comparison is false or value is NULL, result is FALSE.

Myth Busters - 4 Common Misconceptions

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

Common Belief: BETWEEN excludes the boundary values, like some programming ranges do.

Reality: BETWEEN includes both the lower and upper boundaries in the result set.

Why it matters: Assuming boundaries are excluded causes missing data in queries, leading to incorrect results.

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

Common Belief: BETWEEN only works with numbers, 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 restricts query design and misses simpler solutions for date or text filtering.

Quick: Does BETWEEN return rows where the column value is NULL? Commit to your answer.

Common Belief: BETWEEN includes rows where the value is NULL if NULL is between the range.

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

Why it matters: Expecting NULLs to appear can cause confusion when rows are missing from results.

Quick: Is BETWEEN always faster than using AND conditions? Commit to your answer.

Common Belief: BETWEEN is always faster than using separate AND conditions.

Reality: BETWEEN is syntactic sugar and usually compiles to the same execution plan as AND conditions, so performance is similar.

Why it matters: Choosing BETWEEN only for performance reasons can mislead; clarity and correctness matter more.

Expert Zone

1

BETWEEN's inclusivity can cause subtle bugs when filtering exclusive ranges; experts often combine BETWEEN with additional conditions to handle edge cases.

2

Collation and locale settings affect string comparisons in BETWEEN, which can change results unexpectedly in internationalized databases.

3

NULL handling in BETWEEN means that missing data silently excludes rows, so experts explicitly check for NULLs when needed.

When NOT to use

BETWEEN is not suitable when you need exclusive ranges (e.g., greater than but not equal to boundaries). In such cases, use explicit comparisons like > and <. Also, for complex range logic involving multiple columns or non-contiguous ranges, use combined conditions or specialized functions.

Production Patterns

In production, BETWEEN is widely used for date range filtering in reports, price filtering in e-commerce, and numeric range checks. Experts combine BETWEEN with indexes on columns for efficient queries. They also carefully handle NULLs and collation to avoid subtle bugs in multi-language systems.

Connections

Inequality Operators (>, <, >=, <=)

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

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

Collation and Locale in Databases

BETWEEN relies on data ordering, which depends on collation rules for strings.

Knowing collation helps predict how BETWEEN filters string ranges in different languages.

Set Theory in Mathematics

BETWEEN represents membership in a closed interval set.

Recognizing BETWEEN as a set membership test connects database filtering to fundamental math concepts.

Common Pitfalls

#1 Assuming BETWEEN excludes boundary values.

Wrong approach:

SELECT * FROM people WHERE age BETWEEN 21 AND 29; -- expecting to exclude 21 and 29

Correct approach:

SELECT * FROM people WHERE age > 21 AND age < 29; -- excludes boundaries explicitly

Root cause:

Misunderstanding that BETWEEN includes boundaries leads to off-by-one errors.

#2 Using BETWEEN with NULL values expecting them to be included.

Wrong approach:

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'; -- expecting NULL dates included

Correct approach:

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

Root cause:

Not knowing NULL means unknown causes unexpected row exclusion.

#3 Using BETWEEN for exclusive ranges.

Wrong approach:

SELECT * FROM products WHERE price BETWEEN 10 AND 20; -- expecting prices strictly greater than 10 and less than 20

Correct approach:

SELECT * FROM products WHERE price > 10 AND price < 20; -- exclusive range

Root cause:

Confusing inclusive BETWEEN with exclusive range requirements.

Key Takeaways

BETWEEN is a simple and readable way to filter values within an inclusive range in SQL.

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

BETWEEN includes both boundary values, so be careful to avoid off-by-one errors.

NULL values are excluded by BETWEEN because NULL means unknown, not a value inside the range.

BETWEEN is syntactic sugar for combining two comparisons with AND, so performance is similar.