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.

Practice

1. What does the SQL clause WHERE column BETWEEN 10 AND 20 do?

easy

A. Selects rows where the column value is greater than 10 but less than 20, excluding 10 and 20

B. Selects rows where the column value is between 10 and 20, including 10 and 20

C. Selects rows where the column value is exactly 10 or exactly 20 only

D. Selects rows where the column value is less than 10 or greater than 20

Solution

1. Step 1: Understand BETWEEN clause behavior

   The BETWEEN clause includes both the start and end values in the range.

2. Step 2: Apply to the given range 10 to 20

   This means values equal to 10, 20, or any number between them are selected.

3. Final Answer:

   Selects rows where the column value is between 10 and 20, including 10 and 20 -> Option B

4. Quick Check:

   BETWEEN includes boundaries = B [OK]

Hint: BETWEEN includes both start and end values [OK]

Common Mistakes:

• Thinking BETWEEN excludes boundary values
• Confusing BETWEEN with > and < operators
• Assuming BETWEEN works only with numbers

2. Which of the following is the correct syntax to select rows where age is between 18 and 30?

easy

A. SELECT * FROM users WHERE age BETWEEN (18 AND 30);

B. SELECT * FROM users WHERE age BETWEEN 18 TO 30;

C. SELECT * FROM users WHERE age BETWEEN 18, 30;

D. SELECT * FROM users WHERE age BETWEEN 18 AND 30;

Solution

1. Step 1: Recall correct BETWEEN syntax

   The correct syntax uses BETWEEN start AND end without commas or parentheses.

2. Step 2: Check each option

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; uses correct syntax: BETWEEN 18 AND 30. Others have syntax errors.

3. Final Answer:

   SELECT * FROM users WHERE age BETWEEN 18 AND 30; -> Option D

4. Quick Check:

   BETWEEN ... AND ... syntax = D [OK]

Hint: BETWEEN always uses AND, never TO or commas [OK]

Common Mistakes:

• Using TO instead of AND
• Adding commas between values
• Using parentheses around BETWEEN clause

3. Given the table products with a column price, what rows will this query return?

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

Assume the prices in the table are: 45, 50, 75, 100, 120.

medium

A. Rows with prices 50, 75, and 100

B. Rows with prices 45, 50, 75, 100, and 120

C. Rows with prices 75 and 100 only

D. Rows with prices 50 and 100 only

Solution

1. Step 1: Identify the range in BETWEEN clause

   The query filters prices between 50 and 100, including both 50 and 100.

2. Step 2: Check which prices fall in this range

   Prices 50, 75, and 100 are within the range; 45 and 120 are outside.

3. Final Answer:

   Rows with prices 50, 75, and 100 -> Option A

4. Quick Check:

   BETWEEN includes boundaries, so 50 and 100 included = A [OK]

Hint: BETWEEN includes start and end values in results [OK]

Common Mistakes:

• Excluding boundary values 50 and 100
• Including values outside the range like 45 or 120
• Confusing BETWEEN with > and < operators

4. Identify the error in this SQL query:

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

medium

A. Using TO instead of AND in BETWEEN clause

B. Dates should not be in quotes

C. BETWEEN cannot be used with dates

D. Missing semicolon at the end

Solution

1. Step 1: Check BETWEEN syntax

   BETWEEN requires AND between the two values, not TO.

2. Step 2: Validate other parts

   Dates in quotes are correct, BETWEEN works with dates, and semicolon is optional in many systems.

3. Final Answer:

   Using TO instead of AND in BETWEEN clause -> Option A

4. Quick Check:

   BETWEEN uses AND, not TO = A [OK]

Hint: BETWEEN always uses AND, never TO [OK]

Common Mistakes:

• Replacing AND with TO in BETWEEN
• Thinking dates can't be quoted
• Assuming semicolon is mandatory

5. You want to select employees hired between January 1, 2020, and December 31, 2022, inclusive. Which query correctly uses BETWEEN and also excludes employees hired on weekends?

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31' AND EXTRACT(DOW FROM hire_date) NOT IN (0,6);

What does this query do?

hard

A. Selects employees hired between the dates including weekends

B. Selects employees hired only on weekends between the dates

C. Selects employees hired between the dates excluding weekends

D. Syntax error due to EXTRACT usage

Solution

1. Step 1: Understand BETWEEN usage

   The query selects employees hired from 2020-01-01 to 2022-12-31 inclusive.

2. Step 2: Analyze EXTRACT(DOW FROM hire_date) condition

   EXTRACT(DOW) returns day of week (0=Sunday, 6=Saturday). NOT IN (0,6) excludes weekends.

3. Step 3: Combine conditions

   Only employees hired on weekdays within the date range are selected.

4. Final Answer:

   Selects employees hired between the dates excluding weekends -> Option C

5. Quick Check:

   BETWEEN + weekday filter = C [OK]

Hint: Use BETWEEN for range and filter weekdays with EXTRACT [OK]

Common Mistakes:

• Assuming BETWEEN excludes boundaries
• Confusing day numbers for weekend days
• Thinking EXTRACT causes syntax error