Chapter 29: PostgreSQL BETWEEN Operator
The BETWEEN operator.
You already know =, LIKE, IN, >, < … Now comes BETWEEN — which is basically a shorthand for two comparison operators combined with AND.
Let’s go step-by-step like we’re sitting together debugging your first real report query.
1. What does BETWEEN actually do?
BETWEEN checks whether a value lies within a range (inclusive — both ends are included).
Syntax:
|
0 1 2 3 4 5 6 |
column_name BETWEEN lower_value AND upper_value |
Equivalent long version (exactly what PostgreSQL turns it into internally):
|
0 1 2 3 4 5 6 7 |
column_name >= lower_value AND column_name <= upper_value |
So:
- price BETWEEN 200 AND 500 means price >= 200 AND price <= 500
Very important: both boundaries are inclusive → 200 and 500 are included, not excluded.
2. Two main forms you’ll see every day
| Form | Syntax example | Typical real-world use case (2026 apps) |
|---|---|---|
| BETWEEN … AND … | age BETWEEN 18 AND 35 | Age groups, salary bands, dates, prices |
| NOT BETWEEN … AND … | order_date NOT BETWEEN ‘2025-01-01’ AND ‘2025-03-31’ | Exclude a date range, exclude low/high values |
3. Real, practical examples (using our ongoing tables)
Let’s use the students and books tables we’ve been building.
students table (partial)
| id | first_name | gpa | enrollment_year | date_of_birth |
|---|---|---|---|---|
| 1 | Aarav | 8.95 | 2025 | 2003-07-14 |
| 2 | Priya | 9.60 | 2024 | 2002-11-03 |
| 3 | Rahul | 7.85 | 2025 | 2004-02-28 |
| 4 | Sneha | 9.20 | 2025 | 2003-05-19 |
| 5 | Meera | 10.00 | 2025 | NULL |
books table (partial)
| isbn | title | price | publication_year | stock_quantity |
|---|---|---|---|---|
| 978-0143414216 | The God of Small Things | 399.00 | 1997 | 45 |
| 978-9382563754 | The Immortals of Meluha | 250.00 | 2010 | 72 |
| 978-1408855683 | Harry Potter … Stone | 450.00 | 1997 | 120 |
Example 1: Numeric range – most common use
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Students with GPA between 8.5 and 9.5 (inclusive!) SELECT first_name, last_name, gpa FROM students WHERE gpa BETWEEN 8.5 AND 9.5 ORDER BY gpa DESC; |
→ Returns: Aarav (8.95), Sneha (9.20) → Does NOT return Meera (10.00) or Rahul (7.85)
Example 2: Date range (very frequent in reports)
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Students born between 2003 and 2004 SELECT first_name, date_of_birth FROM students WHERE date_of_birth BETWEEN '2003-01-01' AND '2004-12-31' ORDER BY date_of_birth; |
→ Returns: Aarav, Sneha, Rahul → Meera is excluded (NULL does not match)
Example 3: NOT BETWEEN – exclude a range
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Books NOT in the medium price range (i.e. cheap or expensive) SELECT title, price FROM books WHERE price NOT BETWEEN 300 AND 400 ORDER BY price; |
→ Returns: The Immortals… (250), Harry Potter… (450)
Example 4: Year range with integers
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Books published in the 1990s or 2010s SELECT title, publication_year FROM books WHERE publication_year BETWEEN 1990 AND 1999 OR publication_year BETWEEN 2010 AND 2019; |
→ Cleaner with IN sometimes, but BETWEEN is perfect for continuous ranges
Example 5: Combining with other operators
|
0 1 2 3 4 5 6 7 8 9 10 11 |
-- Active students with GPA 8.0–9.5 AND enrolled in 2025 SELECT first_name, gpa, enrollment_year FROM students WHERE gpa BETWEEN 8.0 AND 9.5 AND enrollment_year = 2025 AND status = 'active'; |
4. Important rules & gotchas (real student mistakes)
| Situation | What happens / Surprise | Correct / Best practice |
|---|---|---|
| BETWEEN 1 AND 10 | Includes 1 and 10 | Remember: inclusive — no need to do >=1 AND <=10 |
| date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’ | Works correctly for dates | Use DATE or TIMESTAMPTZ literals properly |
| value BETWEEN a AND b where a > b | Always false | PostgreSQL does not swap them — order matters |
| column BETWEEN NULL AND 100 | Always false (NULL comparison) | Never use NULL in BETWEEN |
| NOT BETWEEN 1 AND 10 on NULL value | Result is NULL (false) | Add AND column IS NOT NULL if needed |
| Floating point precision | 8.5 to 9.5 includes 9.499999… | Use numeric type for money/gpa when precision matters |
Very common date trap:
|
0 1 2 3 4 5 6 |
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31' |
→ This includes orders up to 2025-01-31 23:59:59 → If you want only full days → better to use:
|
0 1 2 3 4 5 6 7 |
WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01' |
5. Quick cheat-sheet (keep in your notes)
| Goal | Best BETWEEN syntax | Alternative (long form) |
|---|---|---|
| Number range inclusive | price BETWEEN 200 AND 500 | price >= 200 AND price <= 500 |
| Date range (full days) | date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’ | — |
| Exclude range | age NOT BETWEEN 18 AND 35 | age < 18 OR age > 35 |
| Year in 2000s | year BETWEEN 2000 AND 2009 | — |
| Safe date range (avoid time issue) | order_date >= ‘2025-01-01’ AND order_date < ‘2025-02-01’ | — |
Your mini practice right now (try in your database)
- Find students with GPA between 8.0 and 9.5
- Find books priced between ₹200 and ₹400 inclusive
- Find students born between 2002-01-01 and 2004-12-31
- Find students NOT enrolled in 2024 or 2025 (use NOT BETWEEN)
Next class?
Tell me:
- Want BETWEEN vs >= AND <= performance & readability comparison?
- How BETWEEN works with TIMESTAMP, INTERVAL, NUMERIC?
- Combine BETWEEN + IN + LIKE in real report queries?
- Or move to EXISTS, ANY/ALL, OVERLAPS operators?
Your guru is ready — what’s the next topic? 🚀
