TrickySQL#307 min readJul 11, 2026
BETWEEN
practice
Practice modeTest yourself instead of reading straight through
Hook: BETWEEN is one of those SQL words that looks harmless, but interviewers love it because one tiny assumption can quietly change your result set.
Question: What does BETWEEN do in SQL?
Answer: BETWEEN filters rows where a value falls inside a range. It is inclusive, which means the start and end values are both included. So salary BETWEEN 50000 AND 80000 is the same as salary >= 50000 AND salary <= 80000 for normal non-NULL values.
Interview-Ready Answer: I use BETWEEN to match values inside a range, and the key detail is that it includes both endpoints. For example, BETWEEN 10 AND 20 includes 10 and 20, so it is logically equivalent to >= 10 AND <= 20. One important gotcha is that rows with NULL do not match the range, because SQL treats that comparison as unknown rather than true.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3EBETWEEN%3C%2Ftext%3E%3C%2Fsvg%3E)
What BETWEEN really means
Think of BETWEEN as a range check. SQL evaluates whether one value sits between two bounds, and it does this using the data type’s normal ordering rules. For numbers, that is obvious. For dates, it compares chronological order. For text, it compares sort order, which can surprise people because string ordering is not the same as human ordering unless the values are carefully formatted.
- SQL reads the expression, such as
price BETWEEN 10 AND 20. - It checks whether
priceis greater than or equal to the lower bound. - It also checks whether
priceis less than or equal to the upper bound. - Both conditions must be true for the row to pass the filter.
- If either side is
NULL, the result is usuallyUNKNOWN, and the row is filtered out byWHERE.
Why interviewers ask it
They want to see whether you know three things: BETWEEN is inclusive, it is just shorthand for two comparisons, and NULL behaves differently from a normal value. Those are small details, but in production they can change counts, revenue reports, and date filters.
Comparison with alternatives
| Form | Meaning | Common use |
|---|---|---|
BETWEEN a AND b | Inclusive range | Clear, readable filters |
>= a AND <= b | Same logic | When you want it spelled out |
NOT BETWEEN a AND b | Outside range | Exclude middle values |
< a OR > b | Outside range | More explicit for some readers |
When to use it
Use BETWEEN when you want a readable closed interval, which is the formal term for a range that includes both ends. It is especially nice for ages, scores, IDs, dates, and prices. If your business rule says “from 100 to 200, including both,” BETWEEN is a great fit.
Important edge cases
- Inclusive endpoints:
10 BETWEEN 10 AND 20is true, and20 BETWEEN 10 AND 20is also true. - NULL values:
NULL BETWEEN 10 AND 20is not true, so it is not returned byWHERE. - Reversed bounds:
BETWEEN 20 AND 10usually returns no rows because the lower bound is higher than the upper bound. - Dates and times: with timestamps,
BETWEENcan miss rows if you use midnight as the upper bound. A safer pattern is often a half-open range like>= start AND < next_day. - Text ranges: string comparison follows collation and sort rules, so results can be surprising unless the text format is normalized.
Performance note: On an indexed column, BETWEEN is usually optimized like two comparisons, so it can use a range scan. In practical terms, that means the database may jump to the first matching key and read forward until the end bound. On a large table, that is much faster than scanning every row. The exact cost depends on the index and data distribution, but the important interview idea is that BETWEEN is generally index-friendly when the filtered column is the leftmost indexed column.
Memory hook: Picture BETWEEN as a fence with two posts: if the value stands on either post or anywhere inside, it counts.
Real-World Example: Imagine a checkout service for an online store. The finance team wants a report for orders between $50 and $100 to study average basket size. The developer uses BETWEEN 50 AND 100 and gets the right totals for normal values, but later notices that some orders with missing amounts never appear. That happens because NULL is not a number, so it does not pass a range test.
If someone misunderstands the boundary rule and writes > 50 AND < 100, the report silently excludes exact $50 and $100 orders. In production, this can show up as a small but stubborn mismatch between SQL reports and the accounting system. The symptoms are often “off by a few rows,” suspicious totals, and support tickets asking why edge-value orders disappeared.
In a chat app, the same idea appears when fetching messages from a time window. A team might use BETWEEN '2026-07-01 00:00:00' AND '2026-07-01 23:59:59', but that can miss messages with fractional seconds after 23:59:59. A safer range would use the next day as the exclusive upper bound. So the bug is not that BETWEEN is wrong; it is that the business rule was a half-open time window, not a closed one.
SQL
-- Self-contained demo of BETWEEN, inclusive boundaries, NOT BETWEEN, and a NULL edge case.
-- This script uses only standard SQL features that are broadly supported.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
amount INTEGER,
order_date TEXT NOT NULL
);
INSERT INTO orders (order_id, customer, amount, order_date) VALUES
(1, 'Ava', 49, '2026-07-01'),
(2, 'Ben', 50, '2026-07-01'),
(3, 'Chen', 75, '2026-07-02'),
(4, 'Dina', 100, '2026-07-03'),
(5, 'Eli', NULL, '2026-07-04');
-- 1) BETWEEN is inclusive: 50 and 100 are both included.
SELECT order_id, customer, amount
FROM orders
WHERE amount BETWEEN 50 AND 100
ORDER BY order_id;
-- 2) Equivalent spelling using two comparisons.
SELECT order_id, customer, amount
FROM orders
WHERE amount >= 50 AND amount <= 100
ORDER BY order_id;
-- 3) NOT BETWEEN means outside the range.
SELECT order_id, customer, amount
FROM orders
WHERE amount NOT BETWEEN 50 AND 100
ORDER BY order_id;
-- 4) Edge case: NULL does not match either BETWEEN or NOT BETWEEN.
-- This query shows that the NULL row is excluded unless we handle it explicitly.
SELECT order_id, customer, amount,
CASE
WHEN amount BETWEEN 50 AND 100 THEN 'in range'
WHEN amount NOT BETWEEN 50 AND 100 THEN 'out of range'
ELSE 'unknown because NULL'
END AS range_status
FROM orders
ORDER BY order_id;
-- 5) If you really want to include NULLs in a report, handle them explicitly.
SELECT order_id, customer, amount
FROM orders
WHERE amount IS NULL OR amount BETWEEN 50 AND 100
ORDER BY order_id;
Follow-up & Tricky Questions:
- Q: Is
BETWEENinclusive or exclusive? A: It is inclusive on both ends, so the boundary values are part of the match. That is whyBETWEEN 10 AND 20includes both 10 and 20. - Q: Is
BETWEENthe same as>=and<=? A: Yes, for normal non-NULL values it is logically equivalent. The main value ofBETWEENis readability, not different behavior. - Q: How does
BETWEENbehave with dates and timestamps? A: It compares according to the data type. With timestamps, you must be careful about the upper boundary, because a closed range ending at midnight or 23:59:59 can miss fractional seconds. - Q: What happens if one side of the comparison is
NULL? A: The result becomes unknown, so the row does not pass theWHEREfilter. If you need NULLs, you must handle them separately withIS NULL. - Q: Can I use
BETWEENfor text? A: Yes, but it uses string sorting rules, which depend on collation and format. That makes it easy to get unexpected results unless the strings are normalized. - Tricky: What if I write
BETWEEN 100 AND 50? A: Most of the time that matches nothing because the lower bound is greater than the upper bound. Candidates often assume SQL swaps them automatically, but it usually does not. - Tricky: Does
NOT BETWEENincludeNULLrows? A: No.NOT BETWEENis still affected byNULL, so the row is not automatically included; you needIS NULLif you want those rows. - Tricky: Is
BETWEENsafe for money ranges with decimals? A: It can be, but you must use the correct numeric type and bounds. Floating-point types can introduce precision issues, so fixed-point decimal types are usually better for money.
Common Mistakes:
- Forgetting inclusiveness: People write
> 10 AND < 20when the business rule includes 10 and 20. Correction: useBETWEEN 10 AND 20or spell out inclusive comparisons. - Ignoring
NULL: Candidates expect missing values to be “outside” the range. Correction: SQL treats them as unknown, so addIS NULLif needed. - Using timestamps like dates: A closed end like
BETWEEN start AND end_of_daycan miss rows with sub-second precision. Correction: prefer a half-open range such as>= start AND < next_day. - Assuming swapped bounds are fixed: Writing the larger value first usually returns no rows. Correction: always verify the lower bound is first.
Memory Hook: “BETWEEN is a fence with two posts.” If the value touches either post or stands between them, it counts.
Cheat Sheet:
BETWEENmeans inclusive range.- Equivalent to
>= low AND <= high. NULLdoes not match the range.- Great for readable filters on numbers, dates, and IDs.
- Be careful with timestamps; half-open ranges are often safer.
- On indexed columns, it is often range-scan friendly.
Practice Tasks:
- Write a query that returns products priced between 25 and 75, including both endpoints.
- Modify it so rows with missing prices are also returned.
- Rewrite a date filter for a single day using a half-open range instead of
BETWEEN.