BETWEEN Operator

Learning Focus

Use this lesson to understand BETWEEN Operator with practical syntax and examples.

Concept Overview

BETWEEN checks whether a value lies within an inclusive range. It is a readable shorthand for a pair of comparison operators.

price BETWEEN 10 AND 20

is equivalent to:

price >= 10 AND price <= 20

Why is it important?

• Readability: makes range filters easy to scan (prices, ages, dates)
• Correctness: clarifies that endpoints are included
• Performance: range filters can be index-friendly when written on indexed columns

Where does it fit?

BETWEEN is used mainly in WHERE and sometimes in JOIN ... ON and HAVING. You will often pair it with ORDER BY and LIMIT.

---

Syntax & Rules

Core Syntax

SELECT select_list
FROM table_name
WHERE value_expression BETWEEN lower_bound AND upper_bound;

SELECT select_list
FROM table_name
WHERE value_expression NOT BETWEEN lower_bound AND upper_bound;

Available Options / Parameters

Part	Meaning	Notes
lower_bound	lower endpoint	included (>=)
upper_bound	upper endpoint	included (<=)
NOT BETWEEN	negation	equivalent to < lower OR > upper

Key Rules and Considerations

• BETWEEN is inclusive on both ends.
• The order of bounds matters: BETWEEN 20 AND 10 returns no rows.
• For timestamps and “bucketed” time windows, a half-open range is often safer: >= start AND < next_start.
• For text columns, BETWEEN uses the column's collation/order (alphabetical ranges can be surprising).

---

Step-by-Step Examples

Example 1: Numeric Range Filter (Beginner)

CREATE TABLE products (
  product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  price      numeric(10,2) NOT NULL CHECK (price >= 0)
);

INSERT INTO products (name, price) VALUES
  ('Notebook', 4.50),
  ('Desk Lamp', 25.00),
  ('Office Chair', 149.99),
  ('Monitor', 229.00);

SELECT product_id, name, price
FROM products
WHERE price BETWEEN 20 AND 200
ORDER BY product_id;

Expected output:

 product_id | name         | price
------------+--------------+--------
 2          | Desk Lamp    | 25.00
 3          | Office Chair | 149.99
(2 rows)

Explanation:

• Both endpoints (20 and 200) would be included if any row matched them.

---

Example 2: Date Range Filter (Intermediate)

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer    text NOT NULL,
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  order_date  date NOT NULL
);

INSERT INTO orders (customer, total_cents, order_date) VALUES
  ('Alice', 15000, '2025-06-01'),
  ('Bob', 8550, '2025-06-15'),
  ('Charlie', 25075, '2025-07-01'),
  ('Diana', 31010, '2025-07-10');

-- Orders in June 2025 (inclusive)
SELECT order_id, customer, order_date
FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30'
ORDER BY order_id;

Expected output:

 order_id | customer | order_date
----------+----------+------------
 1        | Alice    | 2025-06-01
 2        | Bob      | 2025-06-15
(2 rows)

---

Example 3: NOT BETWEEN to Exclude a Range (Intermediate)

SELECT order_id, customer, total_cents
FROM orders
WHERE total_cents NOT BETWEEN 10000 AND 30000
ORDER BY order_id;

Expected output:

 order_id | customer | total_cents
----------+----------+------------
 4        | Diana    | 31010
(1 row)

Explanation:

• NOT BETWEEN a AND b is equivalent to < a OR > b.

---

Example 4: Safer Timestamp Windows with Half-Open Ranges (Advanced)

For timestamps, BETWEEN start AND end includes the end boundary. Adjacent windows can overlap at the boundary.

CREATE TABLE events (
  event_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_name  text NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO events (event_name, created_at) VALUES
  ('signup', '2026-02-28 23:59:59+00'),
  ('signup', '2026-03-01 00:00:00+00'),
  ('signup', '2026-03-31 23:59:59+00'),
  ('signup', '2026-04-01 00:00:00+00');

-- March 2026 with a half-open interval: [start, next_start)
SELECT event_id, created_at
FROM events
WHERE created_at >= '2026-03-01 00:00:00+00'
  AND created_at <  '2026-04-01 00:00:00+00'
ORDER BY event_id;

Expected output:

 event_id | created_at
----------+------------------------
 2        | 2026-03-01 00:00:00+00
 3        | 2026-03-31 23:59:59+00
(2 rows)

Explanation:

• The event at exactly 2026-04-01 00:00:00+00 belongs to April, not March.

---

Practical Use Cases

1) Mid-range price filter

SELECT product_id, name, price
FROM products
WHERE price BETWEEN 50 AND 100;

2) HR salary band

SELECT employee_id, full_name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000;

3) Reporting by month (timestamps)

SELECT *
FROM events
WHERE created_at >= date_trunc('month', now())
  AND created_at <  date_trunc('month', now()) + interval '1 month';

4) Alphabetical slicing (use with caution)

SELECT customer_id, full_name
FROM customers
WHERE full_name BETWEEN 'L' AND 'S';

5) Excluding outliers

SELECT order_id, total_cents
FROM orders
WHERE total_cents NOT BETWEEN 10000 AND 30000;

---

Common Mistakes & Troubleshooting

1) Swapped bounds

Wrong SQL:

WHERE price BETWEEN 200 AND 20

Bad outcome:

• Returns 0 rows.

Fix:

WHERE price BETWEEN 20 AND 200

---

2) Expecting exclusive endpoints

Wrong mental model:

• Thinking BETWEEN excludes endpoints.

Fix:

-- Exclusive range
WHERE price > 20 AND price < 200

---

3) Using BETWEEN for timestamp buckets

Wrong SQL:

-- Includes the end timestamp; adjacent windows can overlap
WHERE created_at BETWEEN '2026-03-01' AND '2026-04-01'

Bad outcome:

• Events exactly at 2026-04-01 00:00:00 can be counted in both windows.

Fix:

WHERE created_at >= '2026-03-01'
  AND created_at <  '2026-04-01'

---

4) Surprises with text BETWEEN

Wrong assumption:

• Assuming text ranges behave the same across collations/locales.

Fix:

• Prefer explicit prefix searches (LIKE 'A%') or normalized keys for ordering.

---

Debugging checklist:

1. Confirm whether endpoints should be included (inclusive) or not.
2. Validate that lower_bound <= upper_bound.
3. For time windows, prefer half-open ranges (>= start AND < end).
4. Ensure literal values match the column type (date vs timestamp).
5. If the query is slow, check for an index on the ranged column and use EXPLAIN.

---

Best Practices

• ✅ Use BETWEEN when you want inclusive endpoints. ❌ Avoid BETWEEN if you actually need exclusive bounds.
• ✅ Prefer half-open intervals for timestamps (>= start AND < end). ❌ Avoid BETWEEN for adjacent time buckets.
• ✅ Keep range filters on the raw column for index use. ❌ Avoid wrapping the column in functions in the WHERE clause.
• ✅ Validate bound ordering (especially when bounds come from user input). ❌ Avoid silently returning empty result sets due to swapped bounds.
• ✅ Be cautious using BETWEEN on text. ❌ Avoid relying on collation-dependent alphabetical ranges for business logic.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total_amount numeric(10,2) NOT NULL CHECK (total_amount >= 0),
  order_date   date NOT NULL
);

INSERT INTO practice_orders (total_amount, order_date) VALUES
  (150.00, '2025-06-01'),
  (85.50, '2025-06-15'),
  (250.75, '2025-07-01'),
  (310.10, '2025-07-10');

Exercise 1 (Easy): Amount range

Task: Retrieve orders with total_amount between 100 and 300.

-- Your SQL here

Solution:

SELECT order_id, total_amount
FROM practice_orders
WHERE total_amount BETWEEN 100 AND 300
ORDER BY order_id;

---

Exercise 2 (Medium): Date range (June 2025)

Task: Retrieve orders placed in June 2025.

-- Your SQL here

Solution:

SELECT order_id, order_date
FROM practice_orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30'
ORDER BY order_id;

---

Exercise 3 (Advanced): Exclude a range

Task: Find orders whose total_amount was NOT between 100 and 300.

-- Your SQL here

Solution:

SELECT order_id, total_amount
FROM practice_orders
WHERE total_amount NOT BETWEEN 100 AND 300
ORDER BY order_id;

---

Connection to Other Concepts

Concept	Why it matters
Comparison operators	BETWEEN expands to >= and <=
IN Operator	list membership vs range membership
Date/time functions	constructing correct window boundaries
Indexes	ranged predicates can be index-friendly
NULL Values	NULLs do not match BETWEEN (comparisons become unknown)

---

Visual Learning Diagram

flowchart TD
  A[WHERE Filtering] --> B[BETWEEN]
  B --> C[Inclusive Endpoints]
  B --> D[NOT BETWEEN]
  B --> E[Numeric Ranges]
  B --> F[Date Ranges]
  B --> G[Timestamp Windows]
  G --> H[Half-Open: >= start AND < end]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using BETWEEN for time buckets	overlap at boundaries	use half-open intervals
Swapped endpoints	empty results	validate/swap inputs
Expecting exclusivity	off-by-one style bugs	use > and < explicitly
Text ranges with collation surprises	unexpected grouping	avoid text BETWEEN for core logic
Wrapping the column in a function	index not used	keep column on the left unmodified

---

Quick Reference

WHERE col BETWEEN 10 AND 20;
WHERE col NOT BETWEEN 10 AND 20;
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';
WHERE col > 10 AND col < 20;
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

What's Next

• Previous: IN Operator - Review the previous lesson to reinforce context.
• Next: Aliases (AS) - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.