Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

3 / 5

BETWEEN: Elegant Range Queries

The Elegance of Range Expression

Range queries are ubiquitous in database applications. Find orders from the last 30 days. Show products priced between $50 and $100. List employees hired in Q3. The BETWEEN operator provides a syntactically clean way to express these range conditions, replacing verbose compound comparisons with a single, readable predicate.

But BETWEEN is more than syntactic sugar. Understanding its exact semantics—particularly its inclusive boundary behavior—is essential for writing correct queries. A single off-by-one error in a range query can mean missing critical data or including unwanted records.

What You Will Learn

By the end of this page, you will master the BETWEEN operator's syntax, boundary semantics, and type-specific behaviors. You'll learn when BETWEEN simplifies your code, when explicit comparisons are preferable, and how to avoid the common pitfalls that plague range queries.

BETWEEN Syntax and Semantics

The BETWEEN operator tests whether a value falls within an inclusive range. Its syntax is straightforward:

value BETWEEN low AND high

This is semantically equivalent to:

value >= low AND value <= high

The key characteristics are:

Inclusive on both ends: The boundary values themselves are included in the range
Order matters: The lower bound must come first
Single evaluation: The test value is evaluated once, not twice

between_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Basic BETWEEN usage
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- Equivalent to: price >= 50 AND price <= 100
-- Includes: 50, 75, 100
-- Excludes: 49.99, 100.01
 
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
-- All employees hired in 2023
 
SELECT * FROM inventory WHERE quantity BETWEEN 100 AND 500;
-- Stock levels from 100 to 500 inclusive
 
-- Column on the right side (less common but valid)
SELECT * FROM order_items WHERE 10 BETWEEN min_quantity AND max_quantity;
-- Items where 10 is within the allowed quantity range
 
-- Expression as the test value
SELECT * FROM products WHERE price * 1.1 BETWEEN 50 AND 100;
-- Products where price + 10% tax falls in range

BETWEEN Requires Correct Order

If you write BETWEEN high AND low (reversed bounds), no rows will match (assuming high > low). The expression x BETWEEN 100 AND 50 is equivalent to x >= 100 AND x <= 50, which is always false. SQL does not automatically swap the bounds.

between_order.sql
1
2
3
4
5
6
7
8
9
10
11
-- WRONG: Reversed bounds - returns no rows!
SELECT * FROM products WHERE price BETWEEN 100 AND 50;
-- Equivalent to: price >= 100 AND price <= 50 (impossible)
 
-- CORRECT: Lower bound first
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
 
-- Dynamic bounds: Ensure correct order with LEAST/GREATEST
SELECT * FROM measurements 
WHERE value BETWEEN LEAST(@param1, @param2) AND GREATEST(@param1, @param2);
-- Works regardless of which parameter is larger

NOT BETWEEN: Exclusion Ranges

The negated form NOT BETWEEN selects values outside the specified range. It excludes both boundary values and everything between them.

Semantics:

value NOT BETWEEN low AND high

is equivalent to:

value < low OR value > high

Note the use of OR (not AND) and the strict inequalities.

not_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic NOT BETWEEN usage
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;
-- Returns: products with price < 50 OR price > 100
-- Includes: 49.99, 100.01
-- Excludes: 50, 75, 100
 
-- Identify outliers
SELECT * FROM sensor_readings 
WHERE value NOT BETWEEN 20 AND 30;
-- Readings outside normal operating range
 
-- Outside a date range
SELECT * FROM orders 
WHERE order_date NOT BETWEEN '2023-01-01' AND '2023-12-31';
-- Orders from before 2023 or after 2023
 
-- Combined with other conditions
SELECT * FROM employees 
WHERE department = 'Engineering' 
  AND salary NOT BETWEEN 50000 AND 150000;
-- Engineering staff with unusual salaries (too low or too high)

NOT BETWEEN and NULL

Like all comparison operators, NOT BETWEEN returns UNKNOWN when the test value is NULL. If price is NULL, then price NOT BETWEEN 50 AND 100 is UNKNOWN, and the row is excluded from results. Handle NULLs explicitly if they should be included.

BETWEEN vs NOT BETWEEN Results
Value	`BETWEEN 50 AND 100`	`NOT BETWEEN 50 AND 100`
30	FALSE	TRUE
50 (boundary)	TRUE	FALSE
75	TRUE	FALSE
100 (boundary)	TRUE	FALSE
150	FALSE	TRUE
NULL	UNKNOWN	UNKNOWN

BETWEEN with Numeric Types

BETWEEN is most commonly used with numeric data for price ranges, quantity limits, score thresholds, and similar bounded conditions.

Integer Ranges:

numeric_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Integer range queries
SELECT * FROM orders WHERE quantity BETWEEN 1 AND 10;
-- Quantities 1, 2, 3, ..., 10
 
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
-- Ages 18, 19, 20, ..., 25
 
SELECT * FROM products WHERE stock_level BETWEEN 0 AND 5;
-- Low stock items (0 to 5 units)
 
-- Decimal/floating-point ranges
SELECT * FROM products WHERE price BETWEEN 9.99 AND 19.99;
-- Includes exact boundary values
 
SELECT * FROM measurements WHERE reading BETWEEN 98.6 AND 99.5;
-- Temperature readings in a specific range
 
-- Scientific notation (where supported)
SELECT * FROM particles WHERE energy BETWEEN 1e-6 AND 1e-3;
-- Energy levels in a micro-range

Floating-Point Precision Considerations:

With floating-point numbers (FLOAT, DOUBLE), boundary precision can be tricky:

float_precision.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Floating-point precision issue
-- A value stored as 99.9999999...8 might not match BETWEEN 50 AND 100 at boundary
 
-- For precise decimal work, use DECIMAL/NUMERIC types
CREATE TABLE prices (
    amount DECIMAL(10, 2)  -- Exact decimal arithmetic
);
 
-- If using FLOAT/DOUBLE, consider epsilon margins
SELECT * FROM measurements 
WHERE value BETWEEN 99.999 AND 100.001;
-- Margin accounts for floating-point imprecision
 
-- Alternative: Use explicit comparison with calculated epsilon
SELECT * FROM measurements 
WHERE value >= 100.0 - 0.0001 AND value <= 100.0 + 0.0001;

DECIMAL for Money

Always use DECIMAL/NUMERIC types for monetary values, not FLOAT/DOUBLE. Floating-point arithmetic can introduce small errors that accumulate. With DECIMAL, BETWEEN 99.99 AND 100.01 works exactly as expected.

BETWEEN with Dates and Times

Date range queries are among the most common uses of BETWEEN. However, they're also where the most bugs occur due to inclusive boundary semantics and timestamp precision.

DATE Type (Day Precision):

date_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Simple date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- All of Q1 2024 (January 1 through March 31 inclusive)
 
-- Full year
SELECT * FROM sales 
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
-- All sales in 2024
 
-- Month-specific
SELECT * FROM appointments 
WHERE appointment_date BETWEEN '2024-06-01' AND '2024-06-30';
-- All June 2024 appointments
 
-- Week-based (using date functions)
SELECT * FROM timesheets 
WHERE work_date BETWEEN DATE_TRUNC('week', CURRENT_DATE) 
                    AND DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '6 days';
-- Current week (PostgreSQL)

The Timestamp Boundary Trap

When using BETWEEN with TIMESTAMP columns, the end boundary includes only the exact midnight instant, not the entire final day. BETWEEN '2024-01-01' AND '2024-01-31' with timestamps includes '2024-01-31 00:00:00.000' but EXCLUDES '2024-01-31 23:59:59'. This is a major source of missing data bugs.

timestamp_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- TIMESTAMP column: The problem
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01' AND '2024-01-31';
-- MISSES events on January 31 after midnight!
-- '2024-01-31' is interpreted as '2024-01-31 00:00:00.000000'
-- So '2024-01-31 15:30:00' is EXCLUDED
 
-- WRONG: Attempting to fix with 23:59:59
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
-- Still misses events in the last second of the day!
-- '2024-01-31 23:59:59.500' would be excluded
 
-- CORRECT: Use explicit half-open interval
SELECT * FROM events 
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';
-- Includes everything up to, but not including, February 1 midnight
-- This is the RECOMMENDED pattern for timestamp ranges
 
-- CORRECT with BETWEEN: Use next-day start minus epsilon
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01 00:00:00' 
                     AND '2024-01-31 23:59:59.999999';
-- Include maximum precision (microseconds) - database-dependent

Best Practice: Half-Open Intervals for Timestamps

The industry-standard pattern for timestamp ranges is the half-open interval: >= start AND < end_exclusive. This avoids all boundary precision issues:

half_open_intervals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- January 2024 (half-open interval - RECOMMENDED)
SELECT * FROM events 
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';
 
-- Q1 2024
SELECT * FROM sales 
WHERE sale_time >= '2024-01-01' AND sale_time < '2024-04-01';
 
-- Last 24 hours
SELECT * FROM logs 
WHERE created_at >= NOW() - INTERVAL '24 hours' AND created_at < NOW();
 
-- Specific day
SELECT * FROM transactions 
WHERE txn_time >= '2024-06-15' AND txn_time < '2024-06-16';
 
-- Note: This pattern also works correctly across time zones and DST transitions

When to Use BETWEEN for Dates

BETWEEN is safe and convenient for DATE columns (day precision) and for TIMESTAMP queries where you explicitly include sub-second precision in the end boundary. For cleaner code and fool-proof semantics, prefer the half-open interval pattern with explicit >= and < operators for TIMESTAMP columns.

BETWEEN with Strings

BETWEEN works with strings using lexicographic (dictionary) ordering. This enables alphabetic range queries, but the ordering rules depend on the column's collation.

Basic String Ranges:

string_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Alphabetic range
SELECT * FROM products WHERE name BETWEEN 'A' AND 'M';
-- Products starting with A through L
-- Note: 'M' is included, so 'M' matches but 'Ma' does NOT
 
-- More precise range
SELECT * FROM products WHERE name BETWEEN 'A' AND 'Mzzzz';
-- Approximates "starts with A through M"
-- Better: Use LIKE or explicit bounds
 
-- Last name ranges (for sharding or partitioning)
SELECT * FROM customers WHERE last_name BETWEEN 'A' AND 'F';
-- Customers A-E (and exactly 'F')
 
-- Product codes
SELECT * FROM inventory WHERE sku BETWEEN 'WH-001' AND 'WH-999';
-- Warehouse items with sequential SKUs

String BETWEEN Gotcha

String BETWEEN 'A' AND 'M' includes exactly 'M' but excludes 'Ma', 'Mb', etc. because lexicographically 'M' < 'Ma'. This matches the inclusive-on-both-ends rule but often surprises users expecting 'all strings starting with A through M'.

Case Sensitivity and Collation:

String ordering varies dramatically based on collation:

String Ordering by Collation Type
Collation Type	Order of A, Z, a, z	Notes
ASCII/Binary	A < Z < a < z	Upper before lower based on ASCII codes
Case-Insensitive	A = a < Z = z	Case variants treated as equal
Dictionary (locale)	a < A < z < Z or interleaved	Varies by language rules

string_collation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Binary collation: uppercase before lowercase
SELECT * FROM items WHERE code BETWEEN 'A' AND 'Z';
-- In binary: includes A-Z but NOT a-z (since 'a' > 'Z' in ASCII)
 
-- Case-insensitive collation
SELECT * FROM items WHERE code BETWEEN 'A' AND 'Z';
-- May include both a-z and A-Z depending on exact collation
 
-- Explicit collation for predictable behavior (PostgreSQL)
SELECT * FROM items 
WHERE code COLLATE "C" BETWEEN 'A' AND 'Z';
-- "C" collation uses strict byte ordering
 
-- Explicit collation (SQL Server)
SELECT * FROM items 
WHERE code COLLATE Latin1_General_BIN BETWEEN 'A' AND 'Z';
 
-- For "all letters A-Z ignoring case":
SELECT * FROM items 
WHERE UPPER(code) BETWEEN 'A' AND 'Z';

Numeric Strings:

BEWARE: Strings containing numbers sort lexicographically, not numerically:

numeric_strings.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- WRONG: Numeric strings sort lexicographically!
SELECT * FROM items WHERE item_number BETWEEN '1' AND '100';
-- Returns: '1', '10', '100'
-- EXCLUDES: '2', '20', '3', '50', etc.
-- Because '2' > '100' lexicographically ('2' > '1')
 
-- Solution 1: Cast to numeric
SELECT * FROM items 
WHERE CAST(item_number AS INTEGER) BETWEEN 1 AND 100;
 
-- Solution 2: Zero-pad the values
SELECT * FROM items WHERE item_number BETWEEN '001' AND '100';
-- Works if all values are zero-padded to same length
 
-- Solution 3: Store as proper numeric type
-- Best practice: Don't store numbers as strings

BETWEEN vs Explicit Comparisons

When should you use BETWEEN versus explicit >= and <= comparisons? Both have their place, and the choice affects readability, correctness, and occasionally performance.

When BETWEEN Shines:

Use BETWEEN When

•Inclusive boundaries are desired — Both endpoints should be in the result
•Readability is priority — BETWEEN 1 AND 10 is clearer than >= 1 AND <= 10
•Static, well-defined ranges — Fixed categories like age groups, price tiers
•DATE column ranges — Day-precision dates work perfectly with BETWEEN
•Symmetric ranges — Same boundary treatment on both sides

Use Explicit Comparisons When

•Exclusive boundary needed — value > 0 AND value <= 100 (exclusive start)
•TIMESTAMP precision matters — Half-open intervals prevent boundary bugs
•Only one bound exists — Just value >= threshold with no upper limit
•Complex expressions — When bound calculations are involved
•Asymmetric conditions — Different operators on each side

between_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- BETWEEN excels: Age range (inclusive, both ends)
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
-- Clear, concise, correct
 
-- Explicit better: Exclusive lower bound
SELECT * FROM orders WHERE discount > 0 AND discount <= 50;
-- Discounted orders (discount must be positive)
 
-- BETWEEN attempt would be wrong:
SELECT * FROM orders WHERE discount BETWEEN 0 AND 50;
-- Includes discount = 0 (not actually discounted)
 
-- Explicit better: Open-ended range
SELECT * FROM employees WHERE salary >= 100000;
-- No upper limit, so BETWEEN doesn't apply
 
-- BETWEEN poor choice: Timestamp ranges
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- Misses 23+ hours of January 31!
 
-- Explicit preferred for timestamps:
SELECT * FROM logs 
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Performance Equivalence:

From a performance standpoint, BETWEEN and equivalent explicit comparisons are identical. The query optimizer transforms BETWEEN into the equivalent >= AND <= before execution. There is no performance reason to prefer one over the other.

The Readability Principle

Choose the form that makes the code's intent clearest. For natural ranges like ages, scores, and price tiers, BETWEEN often reads better. For timestamp ranges and conditional boundaries, explicit comparisons are more precise and less error-prone.

BETWEEN and NULL Values

BETWEEN follows standard SQL NULL semantics: any comparison involving NULL yields UNKNOWN, which is treated as FALSE for filtering purposes.

NULL in the Test Value:

between_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- NULL test value: always UNKNOWN
-- If price IS NULL:
-- price BETWEEN 50 AND 100 → UNKNOWN → excluded from results
 
-- Explicit NULL handling
SELECT * FROM products 
WHERE price BETWEEN 50 AND 100 OR price IS NULL;
-- Include NULLs explicitly
 
-- COALESCE alternative
SELECT * FROM products 
WHERE COALESCE(price, 0) BETWEEN 50 AND 100;
-- Treats NULL as 0 (may or may not be desired)

NULL in the Boundaries:

NULL boundaries make the entire BETWEEN expression UNKNOWN:

null_boundaries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- NULL lower bound
SELECT * FROM products WHERE price BETWEEN NULL AND 100;
-- Equivalent to: price >= NULL AND price <= 100
-- price >= NULL is UNKNOWN for all non-NULL prices
-- Result: No rows returned (UNKNOWN AND anything = UNKNOWN or FALSE)
 
-- NULL upper bound  
SELECT * FROM products WHERE price BETWEEN 50 AND NULL;
-- Equivalent to: price >= 50 AND price <= NULL
-- price <= NULL is UNKNOWN for all non-NULL prices
-- Result: No rows returned
 
-- Both bounds NULL
SELECT * FROM products WHERE price BETWEEN NULL AND NULL;
-- Result: No rows returned
 
-- Variable bounds: Guard against NULL
SELECT * FROM products 
WHERE @min_price IS NOT NULL 
  AND @max_price IS NOT NULL 
  AND price BETWEEN @min_price AND @max_price;
 
-- Or handle dynamically
SELECT * FROM products 
WHERE price >= COALESCE(@min_price, price) 
  AND price <= COALESCE(@max_price, price);
-- NULL bound = no constraint on that side

Dynamic Range Queries

When building range queries with user-provided bounds, always validate that bounds are not NULL before using BETWEEN. A NULL bound silently returns no results, which may appear as a bug to users. Either validate inputs or use conditional query building.

BETWEEN Performance Characteristics

BETWEEN is highly index-friendly. For B-tree indexes (the most common type), BETWEEN translates to an index range scan, which is one of the most efficient access patterns.

Index Usage:

between_indexing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Given index: CREATE INDEX idx_price ON products(price);
 
-- This query uses an index range scan
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- Execution: Seek to first entry >= 50, scan until > 100
 
-- EXPLAIN output (PostgreSQL example):
-- Index Scan using idx_price on products
--   Index Cond: ((price >= 50) AND (price <= 100))
 
-- Composite index usage
-- Given: CREATE INDEX idx_cat_price ON products(category, price);
 
-- Uses full index
SELECT * FROM products 
WHERE category = 'Electronics' AND price BETWEEN 100 AND 500;
 
-- Partial index usage (only category part)
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;
-- May not use idx_cat_price efficiently; category column is not constrained

Selectivity and Optimizer Decisions:

The optimizer estimates how many rows BETWEEN will return (selectivity) and decides whether an index scan is beneficial:

BETWEEN Selectivity Impact
Range Selectivity	% of Table	Likely Access Method
Very selective	<1%	Index seek + fetch
Moderately selective	1-10%	Index scan
Less selective	10-30%	Index scan or table scan
Non-selective	30%	Table scan (often faster)

The Crossover Point

When BETWEEN matches a large percentage of rows, table scans become faster than index scans. This is because sequential I/O (table scan) is faster than the random I/O pattern of fetching many rows via an index. The optimizer's statistics help it find this crossover point.

between_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Verify the execution plan
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
 
-- If not using expected index, check:
-- 1. Statistics freshness
ANALYZE orders;  -- PostgreSQL
UPDATE STATISTICS orders;  -- SQL Server
 
-- 2. Index existence
SELECT * FROM pg_indexes WHERE tablename = 'orders';
 
-- 3. Index condition sargability
-- Ensure no functions wrap the indexed column
 
-- Force index usage (if you know better than optimizer)
-- PostgreSQL:
SET enable_seqscan = off;
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
SET enable_seqscan = on;
 
-- MySQL:
SELECT * FROM orders FORCE INDEX (idx_order_date)
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Summary: Mastering BETWEEN

The BETWEEN operator is a powerful tool for range queries, but its simplicity masks important nuances that affect correctness and performance.

Key Takeaways

•BETWEEN is inclusive on both ends — Both boundary values are included in the result set
•Order of bounds matters — Lower bound must come first; reversed bounds return no rows
•Timestamp ranges require care — End boundary is midnight, not end of day; prefer half-open intervals
•String ranges use lexicographic order — Collation affects sort order; numeric strings sort lexically
•NULL yields UNKNOWN — NULL test values or bounds result in no matches
•BETWEEN is index-friendly — Translates to efficient range scans on B-tree indexes
•Choose clarity over brevity — Use BETWEEN for natural ranges, explicit comparisons for precision

What's Next:

With range queries mastered through BETWEEN, we move to the IN operator—SQL's mechanism for testing set membership. You'll learn how to efficiently filter against multiple values, understand IN's relationship to subqueries, and discover its performance characteristics compared to multiple OR conditions.

Page Complete

You now have complete command of the BETWEEN operator—its syntax, semantics, type-specific behaviors, and performance characteristics. Range queries will no longer hold surprises for you.

3 / 5

Loading learning content...

Database Management SystemsWHERE Clause

The WHERE Clause: Filtering Data with Precision

LevelBeginner

Duration55 mins

TopicWHERE Clause

3 / 5

BETWEEN: Elegant Range Queries

The Elegance of Range Expression

What You Will Learn

BETWEEN Syntax and Semantics

The BETWEEN operator tests whether a value falls within an inclusive range. Its syntax is straightforward:

value BETWEEN low AND high

This is semantically equivalent to:

value >= low AND value <= high

The key characteristics are:

Inclusive on both ends: The boundary values themselves are included in the range
Order matters: The lower bound must come first
Single evaluation: The test value is evaluated once, not twice

between_basics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Basic BETWEEN usage
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- Equivalent to: price >= 50 AND price <= 100
-- Includes: 50, 75, 100
-- Excludes: 49.99, 100.01
 
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';
-- All employees hired in 2023
 
SELECT * FROM inventory WHERE quantity BETWEEN 100 AND 500;
-- Stock levels from 100 to 500 inclusive
 
-- Column on the right side (less common but valid)
SELECT * FROM order_items WHERE 10 BETWEEN min_quantity AND max_quantity;
-- Items where 10 is within the allowed quantity range
 
-- Expression as the test value
SELECT * FROM products WHERE price * 1.1 BETWEEN 50 AND 100;
-- Products where price + 10% tax falls in range

BETWEEN Requires Correct Order

between_order.sql
1
2
3
4
5
6
7
8
9
10
11
-- WRONG: Reversed bounds - returns no rows!
SELECT * FROM products WHERE price BETWEEN 100 AND 50;
-- Equivalent to: price >= 100 AND price <= 50 (impossible)
 
-- CORRECT: Lower bound first
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
 
-- Dynamic bounds: Ensure correct order with LEAST/GREATEST
SELECT * FROM measurements 
WHERE value BETWEEN LEAST(@param1, @param2) AND GREATEST(@param1, @param2);
-- Works regardless of which parameter is larger

NOT BETWEEN: Exclusion Ranges

The negated form NOT BETWEEN selects values outside the specified range. It excludes both boundary values and everything between them.

Semantics:

value NOT BETWEEN low AND high

is equivalent to:

value < low OR value > high

Note the use of OR (not AND) and the strict inequalities.

not_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic NOT BETWEEN usage
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;
-- Returns: products with price < 50 OR price > 100
-- Includes: 49.99, 100.01
-- Excludes: 50, 75, 100
 
-- Identify outliers
SELECT * FROM sensor_readings 
WHERE value NOT BETWEEN 20 AND 30;
-- Readings outside normal operating range
 
-- Outside a date range
SELECT * FROM orders 
WHERE order_date NOT BETWEEN '2023-01-01' AND '2023-12-31';
-- Orders from before 2023 or after 2023
 
-- Combined with other conditions
SELECT * FROM employees 
WHERE department = 'Engineering' 
  AND salary NOT BETWEEN 50000 AND 150000;
-- Engineering staff with unusual salaries (too low or too high)

NOT BETWEEN and NULL

BETWEEN vs NOT BETWEEN Results
Value	`BETWEEN 50 AND 100`	`NOT BETWEEN 50 AND 100`
30	FALSE	TRUE
50 (boundary)	TRUE	FALSE
75	TRUE	FALSE
100 (boundary)	TRUE	FALSE
150	FALSE	TRUE
NULL	UNKNOWN	UNKNOWN

BETWEEN with Numeric Types

BETWEEN is most commonly used with numeric data for price ranges, quantity limits, score thresholds, and similar bounded conditions.

Integer Ranges:

numeric_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Integer range queries
SELECT * FROM orders WHERE quantity BETWEEN 1 AND 10;
-- Quantities 1, 2, 3, ..., 10
 
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
-- Ages 18, 19, 20, ..., 25
 
SELECT * FROM products WHERE stock_level BETWEEN 0 AND 5;
-- Low stock items (0 to 5 units)
 
-- Decimal/floating-point ranges
SELECT * FROM products WHERE price BETWEEN 9.99 AND 19.99;
-- Includes exact boundary values
 
SELECT * FROM measurements WHERE reading BETWEEN 98.6 AND 99.5;
-- Temperature readings in a specific range
 
-- Scientific notation (where supported)
SELECT * FROM particles WHERE energy BETWEEN 1e-6 AND 1e-3;
-- Energy levels in a micro-range

Floating-Point Precision Considerations:

With floating-point numbers (FLOAT, DOUBLE), boundary precision can be tricky:

float_precision.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Floating-point precision issue
-- A value stored as 99.9999999...8 might not match BETWEEN 50 AND 100 at boundary
 
-- For precise decimal work, use DECIMAL/NUMERIC types
CREATE TABLE prices (
    amount DECIMAL(10, 2)  -- Exact decimal arithmetic
);
 
-- If using FLOAT/DOUBLE, consider epsilon margins
SELECT * FROM measurements 
WHERE value BETWEEN 99.999 AND 100.001;
-- Margin accounts for floating-point imprecision
 
-- Alternative: Use explicit comparison with calculated epsilon
SELECT * FROM measurements 
WHERE value >= 100.0 - 0.0001 AND value <= 100.0 + 0.0001;

DECIMAL for Money

BETWEEN with Dates and Times

Date range queries are among the most common uses of BETWEEN. However, they're also where the most bugs occur due to inclusive boundary semantics and timestamp precision.

DATE Type (Day Precision):

date_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Simple date range
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- All of Q1 2024 (January 1 through March 31 inclusive)
 
-- Full year
SELECT * FROM sales 
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
-- All sales in 2024
 
-- Month-specific
SELECT * FROM appointments 
WHERE appointment_date BETWEEN '2024-06-01' AND '2024-06-30';
-- All June 2024 appointments
 
-- Week-based (using date functions)
SELECT * FROM timesheets 
WHERE work_date BETWEEN DATE_TRUNC('week', CURRENT_DATE) 
                    AND DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '6 days';
-- Current week (PostgreSQL)

The Timestamp Boundary Trap

timestamp_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- TIMESTAMP column: The problem
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01' AND '2024-01-31';
-- MISSES events on January 31 after midnight!
-- '2024-01-31' is interpreted as '2024-01-31 00:00:00.000000'
-- So '2024-01-31 15:30:00' is EXCLUDED
 
-- WRONG: Attempting to fix with 23:59:59
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
-- Still misses events in the last second of the day!
-- '2024-01-31 23:59:59.500' would be excluded
 
-- CORRECT: Use explicit half-open interval
SELECT * FROM events 
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';
-- Includes everything up to, but not including, February 1 midnight
-- This is the RECOMMENDED pattern for timestamp ranges
 
-- CORRECT with BETWEEN: Use next-day start minus epsilon
SELECT * FROM events 
WHERE event_time BETWEEN '2024-01-01 00:00:00' 
                     AND '2024-01-31 23:59:59.999999';
-- Include maximum precision (microseconds) - database-dependent

Best Practice: Half-Open Intervals for Timestamps

The industry-standard pattern for timestamp ranges is the half-open interval: >= start AND < end_exclusive. This avoids all boundary precision issues:

half_open_intervals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- January 2024 (half-open interval - RECOMMENDED)
SELECT * FROM events 
WHERE event_time >= '2024-01-01' AND event_time < '2024-02-01';
 
-- Q1 2024
SELECT * FROM sales 
WHERE sale_time >= '2024-01-01' AND sale_time < '2024-04-01';
 
-- Last 24 hours
SELECT * FROM logs 
WHERE created_at >= NOW() - INTERVAL '24 hours' AND created_at < NOW();
 
-- Specific day
SELECT * FROM transactions 
WHERE txn_time >= '2024-06-15' AND txn_time < '2024-06-16';
 
-- Note: This pattern also works correctly across time zones and DST transitions

When to Use BETWEEN for Dates

BETWEEN with Strings

BETWEEN works with strings using lexicographic (dictionary) ordering. This enables alphabetic range queries, but the ordering rules depend on the column's collation.

Basic String Ranges:

string_between.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Alphabetic range
SELECT * FROM products WHERE name BETWEEN 'A' AND 'M';
-- Products starting with A through L
-- Note: 'M' is included, so 'M' matches but 'Ma' does NOT
 
-- More precise range
SELECT * FROM products WHERE name BETWEEN 'A' AND 'Mzzzz';
-- Approximates "starts with A through M"
-- Better: Use LIKE or explicit bounds
 
-- Last name ranges (for sharding or partitioning)
SELECT * FROM customers WHERE last_name BETWEEN 'A' AND 'F';
-- Customers A-E (and exactly 'F')
 
-- Product codes
SELECT * FROM inventory WHERE sku BETWEEN 'WH-001' AND 'WH-999';
-- Warehouse items with sequential SKUs

String BETWEEN Gotcha

Case Sensitivity and Collation:

String ordering varies dramatically based on collation:

String Ordering by Collation Type
Collation Type	Order of A, Z, a, z	Notes
ASCII/Binary	A < Z < a < z	Upper before lower based on ASCII codes
Case-Insensitive	A = a < Z = z	Case variants treated as equal
Dictionary (locale)	a < A < z < Z or interleaved	Varies by language rules

string_collation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Binary collation: uppercase before lowercase
SELECT * FROM items WHERE code BETWEEN 'A' AND 'Z';
-- In binary: includes A-Z but NOT a-z (since 'a' > 'Z' in ASCII)
 
-- Case-insensitive collation
SELECT * FROM items WHERE code BETWEEN 'A' AND 'Z';
-- May include both a-z and A-Z depending on exact collation
 
-- Explicit collation for predictable behavior (PostgreSQL)
SELECT * FROM items 
WHERE code COLLATE "C" BETWEEN 'A' AND 'Z';
-- "C" collation uses strict byte ordering
 
-- Explicit collation (SQL Server)
SELECT * FROM items 
WHERE code COLLATE Latin1_General_BIN BETWEEN 'A' AND 'Z';
 
-- For "all letters A-Z ignoring case":
SELECT * FROM items 
WHERE UPPER(code) BETWEEN 'A' AND 'Z';

Numeric Strings:

BEWARE: Strings containing numbers sort lexicographically, not numerically:

numeric_strings.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- WRONG: Numeric strings sort lexicographically!
SELECT * FROM items WHERE item_number BETWEEN '1' AND '100';
-- Returns: '1', '10', '100'
-- EXCLUDES: '2', '20', '3', '50', etc.
-- Because '2' > '100' lexicographically ('2' > '1')
 
-- Solution 1: Cast to numeric
SELECT * FROM items 
WHERE CAST(item_number AS INTEGER) BETWEEN 1 AND 100;
 
-- Solution 2: Zero-pad the values
SELECT * FROM items WHERE item_number BETWEEN '001' AND '100';
-- Works if all values are zero-padded to same length
 
-- Solution 3: Store as proper numeric type
-- Best practice: Don't store numbers as strings

BETWEEN vs Explicit Comparisons

When should you use BETWEEN versus explicit >= and <= comparisons? Both have their place, and the choice affects readability, correctness, and occasionally performance.

When BETWEEN Shines:

Use BETWEEN When

•Inclusive boundaries are desired — Both endpoints should be in the result
•Readability is priority — BETWEEN 1 AND 10 is clearer than >= 1 AND <= 10
•Static, well-defined ranges — Fixed categories like age groups, price tiers
•DATE column ranges — Day-precision dates work perfectly with BETWEEN
•Symmetric ranges — Same boundary treatment on both sides

Use Explicit Comparisons When

•Exclusive boundary needed — value > 0 AND value <= 100 (exclusive start)
•TIMESTAMP precision matters — Half-open intervals prevent boundary bugs
•Only one bound exists — Just value >= threshold with no upper limit
•Complex expressions — When bound calculations are involved
•Asymmetric conditions — Different operators on each side

between_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- BETWEEN excels: Age range (inclusive, both ends)
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
-- Clear, concise, correct
 
-- Explicit better: Exclusive lower bound
SELECT * FROM orders WHERE discount > 0 AND discount <= 50;
-- Discounted orders (discount must be positive)
 
-- BETWEEN attempt would be wrong:
SELECT * FROM orders WHERE discount BETWEEN 0 AND 50;
-- Includes discount = 0 (not actually discounted)
 
-- Explicit better: Open-ended range
SELECT * FROM employees WHERE salary >= 100000;
-- No upper limit, so BETWEEN doesn't apply
 
-- BETWEEN poor choice: Timestamp ranges
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- Misses 23+ hours of January 31!
 
-- Explicit preferred for timestamps:
SELECT * FROM logs 
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

Performance Equivalence:

The Readability Principle

BETWEEN and NULL Values

BETWEEN follows standard SQL NULL semantics: any comparison involving NULL yields UNKNOWN, which is treated as FALSE for filtering purposes.

NULL in the Test Value:

between_null.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- NULL test value: always UNKNOWN
-- If price IS NULL:
-- price BETWEEN 50 AND 100 → UNKNOWN → excluded from results
 
-- Explicit NULL handling
SELECT * FROM products 
WHERE price BETWEEN 50 AND 100 OR price IS NULL;
-- Include NULLs explicitly
 
-- COALESCE alternative
SELECT * FROM products 
WHERE COALESCE(price, 0) BETWEEN 50 AND 100;
-- Treats NULL as 0 (may or may not be desired)

NULL in the Boundaries:

NULL boundaries make the entire BETWEEN expression UNKNOWN:

null_boundaries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- NULL lower bound
SELECT * FROM products WHERE price BETWEEN NULL AND 100;
-- Equivalent to: price >= NULL AND price <= 100
-- price >= NULL is UNKNOWN for all non-NULL prices
-- Result: No rows returned (UNKNOWN AND anything = UNKNOWN or FALSE)
 
-- NULL upper bound  
SELECT * FROM products WHERE price BETWEEN 50 AND NULL;
-- Equivalent to: price >= 50 AND price <= NULL
-- price <= NULL is UNKNOWN for all non-NULL prices
-- Result: No rows returned
 
-- Both bounds NULL
SELECT * FROM products WHERE price BETWEEN NULL AND NULL;
-- Result: No rows returned
 
-- Variable bounds: Guard against NULL
SELECT * FROM products 
WHERE @min_price IS NOT NULL 
  AND @max_price IS NOT NULL 
  AND price BETWEEN @min_price AND @max_price;
 
-- Or handle dynamically
SELECT * FROM products 
WHERE price >= COALESCE(@min_price, price) 
  AND price <= COALESCE(@max_price, price);
-- NULL bound = no constraint on that side

Dynamic Range Queries

BETWEEN Performance Characteristics

BETWEEN is highly index-friendly. For B-tree indexes (the most common type), BETWEEN translates to an index range scan, which is one of the most efficient access patterns.

Index Usage:

between_indexing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Given index: CREATE INDEX idx_price ON products(price);
 
-- This query uses an index range scan
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
-- Execution: Seek to first entry >= 50, scan until > 100
 
-- EXPLAIN output (PostgreSQL example):
-- Index Scan using idx_price on products
--   Index Cond: ((price >= 50) AND (price <= 100))
 
-- Composite index usage
-- Given: CREATE INDEX idx_cat_price ON products(category, price);
 
-- Uses full index
SELECT * FROM products 
WHERE category = 'Electronics' AND price BETWEEN 100 AND 500;
 
-- Partial index usage (only category part)
SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;
-- May not use idx_cat_price efficiently; category column is not constrained

Selectivity and Optimizer Decisions:

The optimizer estimates how many rows BETWEEN will return (selectivity) and decides whether an index scan is beneficial:

BETWEEN Selectivity Impact
Range Selectivity	% of Table	Likely Access Method
Very selective	<1%	Index seek + fetch
Moderately selective	1-10%	Index scan
Less selective	10-30%	Index scan or table scan
Non-selective	30%	Table scan (often faster)

The Crossover Point

between_optimization.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Verify the execution plan
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
 
-- If not using expected index, check:
-- 1. Statistics freshness
ANALYZE orders;  -- PostgreSQL
UPDATE STATISTICS orders;  -- SQL Server
 
-- 2. Index existence
SELECT * FROM pg_indexes WHERE tablename = 'orders';
 
-- 3. Index condition sargability
-- Ensure no functions wrap the indexed column
 
-- Force index usage (if you know better than optimizer)
-- PostgreSQL:
SET enable_seqscan = off;
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
SET enable_seqscan = on;
 
-- MySQL:
SELECT * FROM orders FORCE INDEX (idx_order_date)
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Summary: Mastering BETWEEN

The BETWEEN operator is a powerful tool for range queries, but its simplicity masks important nuances that affect correctness and performance.

Key Takeaways

•BETWEEN is inclusive on both ends — Both boundary values are included in the result set
•Order of bounds matters — Lower bound must come first; reversed bounds return no rows
•Timestamp ranges require care — End boundary is midnight, not end of day; prefer half-open intervals
•String ranges use lexicographic order — Collation affects sort order; numeric strings sort lexically
•NULL yields UNKNOWN — NULL test values or bounds result in no matches
•BETWEEN is index-friendly — Translates to efficient range scans on B-tree indexes
•Choose clarity over brevity — Use BETWEEN for natural ranges, explicit comparisons for precision

What's Next:

Page Complete

You now have complete command of the BETWEEN operator—its syntax, semantics, type-specific behaviors, and performance characteristics. Range queries will no longer hold surprises for you.

3 / 5