BETWEEN and IN

Last Updated: June 12, 2026 • 14 min read

1. Introduction

BETWEEN and IN are shorthand operators for range checks and set membership tests. They make queries more readable and can be optimized better by the query planner than equivalent expressions.

2. Why It Matters

BETWEEN and IN are tested to evaluate whether candidates understand inclusive/exclusive ranges, date boundary handling, and the NOT IN NULL trap — one of the most dangerous SQL pitfalls that silently returns zero rows.

3. Real-World Analogy

BETWEEN is like setting a price range filter on Amazon: "$25 to $100" includes items at exactly $25 and exactly $100. IN is like selecting multiple checkboxes in a filter: "show me active, pending, or suspended users" — a set of allowed values.

4. How It Works

BETWEEN a AND b: Inclusive range. Equivalent to >= a AND <= b. Works with numbers, dates, strings.
IN (list): Set membership. Equivalent to multiple OR conditions. Optimized with hash lookup.
IN (subquery): Matches values from another query. Converted to semi-join by the optimizer.
NOT BETWEEN / NOT IN: Inverts the check.

5. Internal Architecture

The NOT IN NULL trap: if the subquery in NOT IN returns even one NULL, the entire query returns zero rows. This is because NOT IN with NULL always evaluates to UNKNOWN for every row.

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Common Pitfall

Using NOT IN with a subquery that might return NULL. WHERE id NOT IN (SELECT manager_id FROM departments) returns NO rows if any manager_id is NULL. Always use NOT EXISTS instead.

Interview Insight

The NOT IN trap is a classic interview question. If the subquery contains even one NULL, NOT IN returns zero rows. Use NOT EXISTS for safe NULL handling.

BETWEEN with timestamps: Misses records after midnight on the end date. Use >= start AND < next_period instead.
Reversed BETWEEN: BETWEEN 100 AND 10 returns zero rows — first value must be <= second.

9. Quick Quiz

Q1: Is BETWEEN 10 AND 20 inclusive or exclusive?

Inclusive on both ends. It includes 10, 20, and everything between. Equivalent to >= 10 AND <= 20.

Q2: Why is NOT IN dangerous with subqueries?

If the subquery returns any NULL values, NOT IN evaluates to UNKNOWN for every row, returning zero results. Use NOT EXISTS instead.

10. Scenario-Based Challenge

Scenario

A report should show all orders from January 2026 using BETWEEN '2026-01-01' AND '2026-01-31'. The report is missing orders placed on January 31 after midnight. How do you fix it? What if the column is TIMESTAMP vs DATE?

11. Debugging Exercise

This query returns zero rows, even though there are non-manager employees:

Fix: Some department.manager_id values are NULL, causing NOT IN to return zero rows. Use NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = employees.id)

12. Interview Questions

Q: What's the difference between IN and EXISTS?

IN checks if a value matches any item in a list/subquery. EXISTS checks if a subquery returns any rows. EXISTS handles NULL safely and can be faster for correlated subqueries.

Q: When should you avoid BETWEEN for dates?

Always avoid BETWEEN with TIMESTAMP columns. Use >= start AND < next_period to capture the full time range including fractional seconds at the boundary.

Q: Can you use IN with multiple columns?

Yes, PostgreSQL supports tuple IN: WHERE (col1, col2) IN ((1, 'a'), (2, 'b')). This matches pairs of values simultaneously.

13. Production Considerations

IN list size: Very large IN lists (1000+ values) should use a temporary table or VALUES clause instead for better query plan optimization.
Date range patterns: Standardize on the >= AND < pattern across your team. Add it to your SQL style guide to prevent BETWEEN date bugs.
IN with subquery optimization: PostgreSQL converts IN (SELECT ...) to a semi-join. For correlated subqueries, EXISTS is often faster.
Partial indexes for IN: If you frequently filter with IN ('active', 'pending'), create a partial index: CREATE INDEX idx ON users(id) WHERE status IN ('active', 'pending');