Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 125 ยท Querying Data

IN and BETWEEN

Filter SQL rows by a list of values with IN and by a range with BETWEEN. Learn inclusive bounds, date and text ranges, and why NOT IN fails with NULL.

Two filters come up so often that SQL has a shortcut for each. "Is this value one of these?" is the IN operator: country IN ('France', 'Germany', 'Poland'). "Is this value between two limits?" is BETWEEN: price BETWEEN 20 AND 30. Both read almost like English, and both replace longer chains of OR and AND that you met in the previous lesson.

They come with sharp edges, though. BETWEEN includes both ends, which is a trap with date-times and text. And NOT IN has a famous problem with NULL that can make a query return nothing at all. This lesson covers the shortcuts and the traps. The examples use the shop and company databases.

IN: matching a list of values

IN checks whether a value equals any item in a list. The list goes in parentheses, separated by commas:

SQL Customers in three countries ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

That's exactly the same as writing three equality tests joined with OR:

SQL
WHERE country = 'France'
   OR country = 'Germany'
   OR country = 'Poland'

The IN version is shorter, easier to extend, and avoids the precedence trap when you add more conditions. Here are the Audio or Fitness products that cost under 60:

SQL IN combines safely with AND ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

With OR, you'd need parentheses: (category_id = 4 OR category_id = 6) AND price < 60. With IN, the list is one condition, so there's nothing to group.

A few details worth knowing:

  • The list can hold numbers, text or dates, but the values should match the column's type. Text goes in single quotes, as always.
  • Order and duplicates in the list don't matter: IN (6, 4, 4) means the same as IN (4, 6).
  • A list with one item works (IN ('France')), and is the same as = 'France'.
  • Lists can be long. Databases handle hundreds of values fine, though for thousands it's usually better to put the values in a table and join it.

NOT IN: excluding a list

NOT IN keeps rows whose value matches none of the items:

SQL Orders still in progress ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

24 pending plus 22 shipped orders: the ones still on their way. (GROUP BY counts rows per status; it gets its own lesson.) NOT IN (a, b) is the same as <> a AND <> b, which matches De Morgan's law from the previous lesson.

IN with a subquery

The list for IN doesn't have to be typed by hand. It can come from another query, called a subquery, written in parentheses. This finds every customer who has at least one cancelled order:

SQL A list from another query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The database runs the inner query to get a list of customer ids, then keeps the customers whose id is in that list. A customer with two cancelled orders still appears once, because IN only asks "is it in the list?". Subqueries covers this pattern in depth. It's also where the most dangerous NULL trap lives, as you'll see at the end of this lesson.

BETWEEN: matching a range

BETWEEN low AND high checks whether a value lies in a range. Both ends are included:

SQL Salaries in a range ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Kofi Dubois earns exactly 60,000 and three people earn exactly 63,000, and all of them are included. BETWEEN is shorthand for two comparisons:

SQL
WHERE salary >= 60000
  AND salary <= 63000

NOT BETWEEN keeps values outside the range, and excludes both boundary values:

SQL NOT BETWEEN ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

That's the very cheap products (under 15) and the very expensive ones (over 100).

Order matters

The smaller value must come first. BETWEEN 63000 AND 60000 doesn't swap the bounds for you; it means >= 63000 AND <= 60000, which no number satisfies:

SQL Reversed bounds match nothing ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

No error, no rows. That's the standard behavior and what SQLite, MySQL and SQL Server do. (PostgreSQL also offers BETWEEN SYMMETRIC, which accepts the bounds in either order; it's not available in SQLite.)

BETWEEN with dates

With date-only values in ISO format, BETWEEN works nicely, because both ends are whole days:

SQL Hired during 2024 ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Four employees joined in 2024. But when a column also stores a time, the upper bound is a trap. The shop's ordered_at column holds values like '2025-05-07 20:56'. Try to get the orders from the first week of May:

SQL BETWEEN misses the last day ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The BETWEEN query finds only two orders. The half-open range finds four. The two orders placed on May 7 are missing from the first result because '2025-05-07 20:56' is greater than '2025-05-07', so it falls outside the range. The same happens with real TIMESTAMP columns in PostgreSQL, MySQL and SQL Server, where '2025-05-07' means midnight at the start of that day.

Don't use BETWEEN for date-time ranges. Write column >= start AND column < next_start instead. It catches every moment of the last day, whatever the precision of the times, and works the same in every database.

BETWEEN with text

BETWEEN also works on text, comparing alphabetically, but the upper bound surprises people. You might expect last_name BETWEEN 'A' AND 'C' to include names starting with C:

SQL Where did the C names go? ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Only names starting with A and B appear. 'Costa' is greater than 'C', just as '2025-05-07 20:56' is greater than '2025-05-07': a longer string that starts with the same characters sorts after the shorter one. To get names from A through C, use a half-open range again: last_name >= 'A' AND last_name < 'D'.

The NOT IN and NULL trap

Here's the most important section of this lesson. Recall from AND, OR and NOT that any comparison with NULL is unknown. Now look at what that does to a list:

SQL IN and NOT IN with NULL in the list

Press Run (or Ctrl + Enter) to see the result here.

  • 1 IN (1, 2, NULL) is true (1): 1 equals the first item, so the answer is certain.
  • 3 IN (1, 2, NULL) is NULL: 3 isn't 1 or 2, but is it equal to the unknown value? Nobody knows.
  • 3 NOT IN (1, 2, NULL) is NULL too. It means 3 <> 1 AND 3 <> 2 AND 3 <> NULL, and the last part is unknown.

So if the list contains a NULL, NOT IN can never be true, and a WHERE clause with it returns no rows at all. With a typed list you'd notice the NULL. With a subquery, you usually don't. In the company database, let's find the employees who don't manage anyone, that is, whose id isn't anyone's manager_id:

SQL NOT IN returns nothing ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Zero. The query looks right, raises no error, and is completely wrong. The CEO has no manager, so the subquery's list includes a NULL, and that single NULL makes NOT IN unknown for every row. The fix is to keep NULL out of the list:

SQL Filter out NULL first ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Now the count is 29. Only seven employees (the CEO and six department heads) manage someone.

An even safer pattern is NOT EXISTS, which isn't affected by NULL in the inner query at all. You'll learn it in EXISTS. Until then, remember the rule: whenever you write NOT IN (subquery), make sure the subquery can't return NULL.

IN has a milder version of the same issue: a NULL in the list never matches anything, including a NULL in the column. email IN ('[email protected]', NULL) won't find employees with no email. Use IS NULL for that, as explained in Working with NULL.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which salaries does salary BETWEEN 60000 AND 63000 include?

    BETWEEN includes both bounds; it is shorthand for >= 60000 AND <= 63000.
  2. 2. What does SELECT 5 NOT IN (1, 2, NULL); return?

    It means 5 <> 1 AND 5 <> 2 AND 5 <> NULL. The last comparison is unknown, so the whole result is unknown.
  3. 3. ordered_at holds values like '2025-05-07 20:56'. Which condition finds all orders from May 1 to May 7, 2025?

    BETWEEN would miss orders on May 7 after midnight. The half-open range includes every moment of the last day.
  4. 4. What does WHERE price BETWEEN 100 AND 20 return?

    It means price >= 100 AND price <= 20, which no value satisfies. Standard BETWEEN never swaps the bounds.
  5. 5. A NOT IN (subquery) filter returns zero rows, but you're sure some rows should match. What's the most likely cause?

    A single NULL in the list makes NOT IN unknown for every row. Filter it out with WHERE column IS NOT NULL or use NOT EXISTS.

Summary

  • x IN (a, b, c) is shorthand for x = a OR x = b OR x = c; it reads clearly and combines safely with AND.
  • The list can come from a subquery: WHERE id IN (SELECT customer_id FROM orders ...).
  • x BETWEEN low AND high includes both bounds; the low value must come first, or nothing matches.
  • With date-times and text, BETWEEN's upper bound misses values that "start with" it. Use half-open ranges: >= start AND < next_start.
  • If the list contains NULL, NOT IN is never true and returns no rows. Exclude NULL from subqueries, or use NOT EXISTS.

Next: Pattern Matching with LIKE, where you'll search text with wildcards.