SQL 18 🛢️ Range Filtering with BETWEEN and NOT BETWEEN

A comparison operator tests one boundary. total > 100 tests whether a value exceeds 100. total < 200 tests whether a value is below 200. To test whether a value falls within a range, you need two comparisons joined by AND. The BETWEEN operator is a shorthand for that pair of comparisons. It tests whether a value falls within an inclusive range. The NOT BETWEEN operator is its negation.

The previous chapters covered the comparison operators and the logical operators. This chapter covers BETWEEN, which is the range filter. It is part of the WHERE clause, and it is used wherever a value must be tested against a lower and an upper bound. Prices, dates, ages, scores, timestamps — the BETWEEN operator handles them all with a cleaner syntax than two separate comparisons.

Key point: BETWEEN is inclusive. value BETWEEN a AND b is equivalent to value >= a AND value <= b. The endpoints are included. This is different from some programming languages, where a range is often half-open. The inclusive behavior is standard SQL, and it matters when the exact boundary values are important.

---

Why the BETWEEN operator matters

A range filter is one of the most common filtering patterns. “Which orders were placed in the last 30 days?” “Which products cost between $50 and $200?” “Which employees are between the ages of 25 and 40?” The BETWEEN operator expresses all of these.

The syntax problem. Two comparisons joined by AND work: total >= 50 AND total <= 200. But the pattern is verbose. The value total is repeated. The BETWEEN operator removes the repetition: total BETWEEN 50 AND 200. The intent is clearer, and the query is shorter.

The boundary problem. The BETWEEN operator includes the endpoints. total BETWEEN 50 AND 200 includes orders with a total of exactly 50 and exactly 200. A half-open range would exclude one of the endpoints. The inclusive behavior is standard SQL, and it must be remembered when the boundary matters.

The NULL problem. A value that is NULL does not fall in any range. NULL BETWEEN 50 AND 200 returns NULL, not TRUE or FALSE. Rows with a null value in the column are excluded from the result.

The type problem. The BETWEEN operator works with any type that supports ordering: numbers, strings, dates, timestamps. The lower bound must be less than or equal to the upper bound. If the lower bound is greater than the upper bound, the range is empty and no rows are returned.

The trade-off. The BETWEEN operator is a syntactic convenience. It does not add new functionality. The optimizer treats BETWEEN and the equivalent AND expression the same way. The benefit is readability, not performance.

---

a. The BETWEEN Operator

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

value BETWEEN lower_bound AND upper_bound

The expression is equivalent to:

value >= lower_bound AND value <= upper_bound

The query selects orders with a total between 50 and 200:

SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

The query includes orders with a total of exactly 50 and exactly 200. The endpoints are included.

The BETWEEN operator can be used with any ordered type:

SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31';
SELECT * FROM customers WHERE last_name BETWEEN 'A' AND 'M';
SELECT * FROM employees WHERE age BETWEEN 25 AND 40;
SELECT * FROM events WHERE start_time BETWEEN '2026-10-01 09:00' AND '2026-10-01 17:00';

The first query selects orders placed in the year 2026. The second selects customers whose last name starts with a letter from A to M. The third selects employees between the ages of 25 and 40. The fourth selects events that start during business hours on October 1, 2026.

---

b. The NOT BETWEEN Operator

The NOT BETWEEN operator is the negation of BETWEEN. It tests whether a value falls outside the range.

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;

The query selects orders with a total less than 50 or greater than 200. The endpoints are excluded. The expression is equivalent to:

value < lower_bound OR value > upper_bound

The NOT BETWEEN operator uses OR, not AND, because the condition is the negation of a range. A value is outside the range if it is below the lower bound or above the upper bound.

The query excludes orders with a total of exactly 50 and exactly 200:

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;

An order with a total of exactly 50 is not included. An order with a total of exactly 200 is not included. The endpoints belong to the range, and the NOT BETWEEN operator excludes the range.

---

c. The NULL Behavior and the Boundary Trap

The BETWEEN operator handles NULL in the same way as the comparison operators. A NULL value does not fall in any range. The expression NULL BETWEEN 50 AND 200 returns NULL, not TRUE or FALSE.

-- The row with a null total is excluded
SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

If the total column contains a NULL, the row is not included in the result. This is consistent with the comparison operators. A row must satisfy the condition for the row to be included. A NULL result does not satisfy the condition.

A common trap is to write WHERE total NOT BETWEEN 50 AND 200 and expect it to include rows where total is NULL. It does not. The expression NULL NOT BETWEEN 50 AND 200 is NULL, not TRUE. The row with the null total is excluded from both the BETWEEN and the NOT BETWEEN queries.

To include null rows, the condition must be explicit:

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200 OR total IS NULL;

The OR total IS NULL clause adds the null rows to the result. Without it, the null rows are excluded from both the positive and the negative query.

The boundary trap is the second common pitfall. The BETWEEN operator is inclusive. total BETWEEN 50 AND 200 includes 50 and 200. If the intent is to exclude one or both of the endpoints, the BETWEEN operator is the wrong tool. Use the explicit comparison operators instead.

-- Inclusive on both ends
WHERE total BETWEEN 50 AND 200

-- Exclusive on both ends
WHERE total > 50 AND total < 200

-- Inclusive on the lower, exclusive on the upper
WHERE total >= 50 AND total < 200

The choice depends on the business rule. The BETWEEN operator is the right tool only when the range is inclusive on both ends.

---

Complete Example Session

This session demonstrates the BETWEEN and NOT BETWEEN operators on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL,
    total       DECIMAL(10, 2)
);

INSERT INTO orders VALUES
    (1001, 1, '2026-01-15', 49.99),
    (1002, 1, '2026-02-20', 149.99),
    (1003, 2, '2026-03-10', 50.00),
    (1004, 3, '2026-04-05', 200.00),
    (1005, 4, '2026-05-12', 299.99),
    (1006, 5, '2026-06-18', NULL);

-- ============================================
-- PART 2: BETWEEN WITH NUMBERS
-- ============================================

SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

-- Output:
-- 1002 (149.99), 1003 (50.00), 1004 (200.00)
-- The endpoints 50.00 and 200.00 are included.
-- The null row (1006) is excluded.

-- ============================================
-- PART 3: BETWEEN WITH DATES
-- ============================================

SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

-- Output:
-- 1001 (2026-01-15), 1002 (2026-02-20), 1003 (2026-03-10)

-- ============================================
-- PART 4: BETWEEN WITH STRINGS
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    last_name   VARCHAR(50)
);

INSERT INTO customers VALUES
    (1, 'Anderson'), (2, 'Brown'), (3, 'Miller'), (4, 'Smith'), (5, 'Wilson');

SELECT * FROM customers WHERE last_name BETWEEN 'B' AND 'M';

-- Output:
-- Brown, Miller
-- 'B' is included, 'M' is included.

-- ============================================
-- PART 5: NOT BETWEEN
-- ============================================

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;

-- Output:
-- 1001 (49.99), 1005 (299.99)
-- The endpoints are excluded.
-- The null row (1006) is also excluded.

-- ============================================
-- PART 6: THE NULL TRAP
-- ============================================

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200 OR total IS NULL;

-- Output:
-- 1001 (49.99), 1005 (299.99), 1006 (null)
-- The OR clause adds the null row.

-- ============================================
-- PART 7: THE EQUIVALENT EXPRESSIONS
-- ============================================

-- total BETWEEN 50 AND 200
-- is equivalent to
-- total >= 50 AND total <= 200

-- total NOT BETWEEN 50 AND 200
-- is equivalent to
-- total < 50 OR total > 200

-- ============================================
-- PART 8: THE BOUNDARY CHOICE
-- ============================================

-- Inclusive on both ends
SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

-- Exclusive on both ends
SELECT * FROM orders WHERE total > 50 AND total < 200;

-- Inclusive on the lower, exclusive on the upper
SELECT * FROM orders WHERE total >= 50 AND total < 200;

-- ============================================
-- PART 9: THE DATE RANGE
-- ============================================

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

-- Includes orders placed on either endpoint.
-- The date literal uses the ISO format.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- BETWEEN : inclusive range
-- NOT BETWEEN : exclusive range
-- NULL : excluded from both
-- Endpoints : included in BETWEEN
-- Equivalent to : >= AND <=

The ten parts cover the table, BETWEEN with numbers, BETWEEN with dates, BETWEEN with strings, NOT BETWEEN, the null trap, the equivalent expressions, the boundary choice, the date range, and the summary.

---

Quick Reference

The BETWEEN Operators

Operator	Purpose
BETWEEN a AND b	Value is in the inclusive range [a, b]
NOT BETWEEN a AND b	Value is outside the range [a, b]

The Equivalent Expressions

BETWEEN	Equivalent
x BETWEEN a AND b	x >= a AND x <= b
x NOT BETWEEN a AND b	x < a OR x > b

The NULL Behavior

Expression	Result
NULL BETWEEN 50 AND 200	NULL
NULL NOT BETWEEN 50 AND 200	NULL
50 BETWEEN NULL AND 200	NULL
50 BETWEEN 50 AND NULL	NULL

The Boundary Choice

Intent	Expression
Inclusive both ends	BETWEEN a AND b
Exclusive both ends	> a AND < b
Inclusive lower, exclusive upper	>= a AND < b
Exclusive lower, inclusive upper	> a AND <= b

---

Best Practices

✅ Do This:

-- Use BETWEEN for inclusive ranges
WHERE total BETWEEN 50 AND 200                                 -- ✅

-- Use NOT BETWEEN with OR IS NULL to include nulls
WHERE total NOT BETWEEN 50 AND 200 OR total IS NULL            -- ✅

-- Use ISO format for dates
WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'          -- ✅

-- Use explicit comparisons when the range is not inclusive
WHERE total > 50 AND total < 200                               -- ✅

❌ Don’t Do This:

-- Don't expect NOT BETWEEN to include nulls
WHERE total NOT BETWEEN 50 AND 200  -- excludes null            -- ❌

-- Don't reverse the bounds
WHERE total BETWEEN 200 AND 50  -- empty range                  -- ❌

-- Don't use BETWEEN with an exclusive range
WHERE total BETWEEN 50 AND 200  -- endpoints included           -- ⚠️

-- Don't mix date formats
WHERE order_date BETWEEN '01/01/2026' AND '12/31/2026'          -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Null rows missing	NULL excluded from BETWEEN	Add OR col IS NULL
Endpoint included	BETWEEN is inclusive	Use > and <
Empty result	Reversed bounds	Swap the bounds
Wrong dates	Ambiguous format	Use ISO format
Index not used	Function on column	Use the column directly

---

Real-World Examples

1. Price Range

SELECT * FROM products WHERE price BETWEEN 50 AND 200;

2. Date Range

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

3. Age Range

SELECT * FROM employees WHERE age BETWEEN 25 AND 40;

4. String Range

SELECT * FROM customers WHERE last_name BETWEEN 'A' AND 'M';

5. NOT BETWEEN

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200;

6. With NULL

SELECT * FROM orders WHERE total NOT BETWEEN 50 AND 200 OR total IS NULL;

7. Exclusive Range

SELECT * FROM orders WHERE total > 50 AND total < 200;

8. Half-Open Range

SELECT * FROM orders WHERE total >= 50 AND total < 200;

9. Timestamp Range

SELECT * FROM events WHERE start_time BETWEEN '2026-10-01 09:00' AND '2026-10-01 17:00';

10. BETWEEN with NOT

SELECT * FROM customers WHERE NOT (last_name BETWEEN 'A' AND 'M');

---

Visual

The BETWEEN Operator

┌──────────────────────────────────────────────┐
│  BETWEEN a AND b                             │
│                                              │
│  ├── a ─────────── b ──┤                     │
│  ↑                    ↑                      │
│  Included           Included                │
│                                              │
│  Inclusive on both ends.                     │
│  Equivalent to >= a AND <= b.                │
│                                              │
└──────────────────────────────────────────────┘

The NOT BETWEEN Operator

┌──────────────────────────────────────────────┐
│  NOT BETWEEN a AND b                         │
│                                              │
│  ├─ ──┤      ├── a ───── b ──┤               │
│  ↑                                    ↑      │
│  Below a                            Above b  │
│                                              │
│  Excluded on both ends.                      │
│  Equivalent to < a OR > b.                   │
│                                              │
└──────────────────────────────────────────────┘

The NULL Behavior

┌──────────────────────────────────────────────┐
│  NULL BETWEEN 50 AND 200 → NULL              │
│  NULL NOT BETWEEN 50 AND 200 → NULL          │
│                                              │
│  WHERE includes only TRUE.                   │
│  FALSE and NULL are excluded.                │
│                                              │
│  The null row is excluded from BOTH          │
│  the BETWEEN and the NOT BETWEEN queries.    │
│                                              │
└──────────────────────────────────────────────┘

The Boundary Choice

┌──────────────────────────────────────────────┐
│  BOUNDARY OPTIONS                            │
│                                              │
│  Inclusive both:                             │
│    BETWEEN 50 AND 200                        │
│    → includes 50 and 200                     │
│                                              │
│  Exclusive both:                             │
│    > 50 AND < 200                            │
│    → excludes 50 and 200                     │
│                                              │
│  Half-open:                                  │
│    >= 50 AND < 200                           │
│    → includes 50, excludes 200               │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
BETWEEN a AND b	Inclusive range [a, b]
NOT BETWEEN a AND b	Outside [a, b]
BETWEEN equivalent	>= a AND <= b
NOT BETWEEN equivalent	< a OR > b
Endpoints	Included in BETWEEN
NULL	Excluded from both
Date format	ISO 8601 (YYYY-MM-DD)
Reversed bounds	Empty result
Index usage	Use the column directly

Key takeaways:

• The BETWEEN operator tests whether a value falls within an inclusive range. The expression value BETWEEN a AND b is equivalent to value >= a AND value <= b. The endpoints are included .
• The NOT BETWEEN operator tests whether a value falls outside the range. The expression value NOT BETWEEN a AND b is equivalent to value < a OR value > b. The endpoints are excluded from the NOT BETWEEN result because they are inside the range.
• The endpoints are included in BETWEEN. An order with a total of exactly 50 is included in total BETWEEN 50 AND 200. If the intent is to exclude the endpoints, use the explicit comparison operators instead .
• A NULL value is excluded from both BETWEEN and NOT BETWEEN. The expression NULL BETWEEN 50 AND 200 returns NULL, not TRUE or FALSE. The WHERE clause includes only rows where the condition is TRUE. To include null rows, add OR column IS NULL .
• The BETWEEN operator works with any ordered type. Numbers, strings, dates, and timestamps are all supported. The lower bound must be less than or equal to the upper bound. Reversing the bounds produces an empty range and no rows .
• The date format should be ISO 8601. The format YYYY-MM-DD is unambiguous and is the standard for SQL date literals. Ambiguous formats like MM/DD/YYYY produce wrong results or errors .
• The BETWEEN operator is a syntactic convenience. It does not add functionality. The query optimizer treats BETWEEN and the equivalent AND expression the same way. The benefit is readability, not performance.

Remember: BETWEEN is the range filter. It is inclusive on both ends. It is equivalent to >= a AND <= b. NOT BETWEEN is the negation. It is equivalent to < a OR > b. NULL is excluded from both. The endpoints are included in BETWEEN. Use the explicit comparisons when the range is not inclusive. Use OR IS NULL to include null rows. Use ISO dates. The BETWEEN operator is the tool for the range, and the range is inclusive.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!