0Pricing
SQL Interview Prep · Lesson

BETWEEN, IN, and Inclusive Boundaries

Boundary edge cases and how BETWEEN treats endpoints.

BETWEEN, IN, and Inclusive Boundaries is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Boundaries Cost Candidates Points

Range and set filters look trivial, so interviewers weaponize the edge cases. BETWEEN includes both endpoints, IN hides a subtle NULL trap, and date ranges are where off-by-one errors silently corrupt reports.

This lesson covers exactly how BETWEEN treats its endpoints, when IN is cleaner than chained ORs, and the half-open range pattern that professionals use for dates.

BETWEEN Is Inclusive on Both Ends

col BETWEEN a AND b is shorthand for col >= a AND col <= b. Both endpoints are included.

So price BETWEEN 10 AND 20 returns rows where price is exactly 10 or exactly 20 as well as everything in between. The most common wrong answer in interviews is claiming the upper bound is excluded.

SELECT *
FROM products
WHERE price BETWEEN 10 AND 20;
-- equivalent to: price >= 10 AND price <= 20

Order of Arguments Matters

BETWEEN requires the lower value first. col BETWEEN 20 AND 10 expands to col >= 20 AND col <= 10, which can never be true, so it returns zero rows rather than an error.

This is a favorite trap: the query runs cleanly, returns nothing, and the candidate assumes the data is empty. Always put the smaller bound first.

SELECT *
FROM products
WHERE price BETWEEN 20 AND 10;
-- returns NOTHING, not an error

The Date Range Off-By-One

Ask for all of January and many candidates write order_date BETWEEN '2024-01-01' AND '2024-01-31'. If order_date is a timestamp, this drops everything after midnight on the 31st, because 2024-01-31 means 2024-01-31 00:00:00.

An order at 2pm on January 31 is excluded. With pure DATE columns it works, but you cannot assume the type.

SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- silently excludes Jan 31 afternoon if order_date is a timestamp

The Half-Open Range Fix

The professional pattern for dates is a half-open interval: greater-or-equal the start, strictly less-than the next period. It is correct for both DATE and TIMESTAMP and needs no knowledge of the column's time resolution.

Notice the upper bound is the first day of February, not the last day of January. This captures every January instant.

SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-02-01';

NOT BETWEEN

col NOT BETWEEN a AND b expands to col < a OR col > b. It excludes both endpoints and everything between them.

Watch out: if col is NULL, NOT BETWEEN evaluates to UNKNOWN, so NULL rows are excluded just like with plain BETWEEN. NULLs never satisfy a range test in either direction.

SELECT *
FROM products
WHERE price NOT BETWEEN 10 AND 20;
-- price < 10 OR price > 20

IN as Set Membership

col IN (a, b, c) is shorthand for col = a OR col = b OR col = c. It is the clean way to test membership in a small fixed set.

It reads better than a chain of ORs and avoids the precedence parenthesization problem entirely, since the whole set test is one predicate.

SELECT *
FROM orders
WHERE status IN ('pending', 'shipped', 'delivered');

The NOT IN With NULL Trap

This is the most feared IN question. If the list (or subquery) behind NOT IN contains a single NULL, the entire predicate can evaluate to UNKNOWN and return zero rows.

Reason: x NOT IN (1, NULL) becomes x <> 1 AND x <> NULL, and x <> NULL is never true, it is UNKNOWN. The whole AND can never be true.

SELECT *
FROM employees
WHERE manager_id NOT IN (SELECT id FROM managers);
-- returns NOTHING if any managers.id is NULL

Fixing NOT IN

Two robust fixes for the NOT IN NULL trap:

  • Filter NULLs out of the subquery with WHERE id IS NOT NULL
  • Better, rewrite it as NOT EXISTS, which handles NULLs correctly by design

Interviewers consider NOT EXISTS the senior answer because it sidesteps the trap entirely and often plans better.

SELECT e.*
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM managers m WHERE m.id = e.manager_id
);

IN With a Subquery

IN accepts a subquery that returns one column. WHERE customer_id IN (SELECT customer_id FROM vip) keeps rows whose customer is in the VIP set.

Plain IN (not NOT IN) is safe with NULLs in the subquery: a NULL in the list simply never matches, but it does not poison the rows that do match. The trap is specific to NOT IN.

SELECT *
FROM orders
WHERE customer_id IN (SELECT customer_id FROM vip_customers);

Multi-Column IN With Row Constructors

A common follow-up: how do you match on more than one column at once? Pass a tuple to IN using a row constructor — it compares the columns positionally and is far cleaner than chaining OR (a = .. AND b = ..).

  • Reads clearly and scales to long allow-lists.
  • Each tuple must list the columns in the same order.
SELECT *
FROM orders
WHERE (customer_id, status) IN ((101, 'paid'), (102, 'shipped'));

Quick Check

Recall how BETWEEN treats its endpoints.

Recap

Key takeaways:

  • BETWEEN a AND b is inclusive of both endpoints; lower bound must come first or you get zero rows
  • For timestamp ranges use a half-open interval: >= start AND < next_period
  • IN is clean set membership; safe with NULLs
  • NOT IN with any NULL in the list returns nothing, rewrite as NOT EXISTS

The recurring theme: a filter can run perfectly and still quietly return the wrong rows.

Frequently asked questions

Is the “BETWEEN, IN, and Inclusive Boundaries” lesson free?

Yes — the full text of “BETWEEN, IN, and Inclusive Boundaries” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “BETWEEN, IN, and Inclusive Boundaries”?

Boundary edge cases and how BETWEEN treats endpoints. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “BETWEEN, IN, and Inclusive Boundaries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. AND/OR Precedence and Parenthesization
  2. BETWEEN, IN, and Inclusive Boundaries
  3. LIKE, Wildcards and Escaping
  4. Filtering on Calculated Values
← Back to SQL Interview Prep