Filtering with IN, BETWEEN, and NOT Operators

By now you know how to filter data using comparison operators and LIKE patterns. But you have probably noticed that some filtering tasks feel verbose. Finding products in three specific categories requires chaining three OR conditions. Checking if a price falls within a range means writing two separate comparisons with AND. These patterns work, but SQL offers cleaner, more expressive alternatives.

The IN operator lets you match a column against a list of values in a single expression. The BETWEEN operator checks if a value falls within a range using one concise clause. Combined with NOT, these operators let you express complex filtering logic in a way that reads almost like plain English.

This guide covers IN, BETWEEN, and their NOT counterparts in depth, with practical examples, edge cases, common mistakes, and a preview of how IN works with subqueries. Every example uses the ShopSmart sample database with full outputs so you can follow along in your own environment (we defined it in a previous guide here).

The IN Operator

The IN operator checks whether a column's value matches any value in a specified list. It is a shorthand for multiple OR conditions on the same column.

SELECT columns
FROM table
WHERE column IN (value1, value2, value3);

The Problem IN Solves

Without IN, finding customers from New York, Chicago, or Seattle requires chaining OR:

-- Without IN: Verbose and repetitive
SELECT first_name, last_name, city
FROM customers
WHERE city = 'New York'
   OR city = 'Chicago'
   OR city = 'Seattle';

Output:

This works, but the column name city is repeated three times. With IN, the same query becomes cleaner:

-- With IN: Clean and readable
SELECT first_name, last_name, city
FROM customers
WHERE city IN ('New York', 'Chicago', 'Seattle');

Output:

Identical results, but the intent is immediately clear: "Give me customers whose city is in this list."

IN with Numbers

IN works with any data type, not just text. For numeric values, omit the quotes:

-- Find products in specific categories
SELECT name, price, category_id
FROM products
WHERE category_id IN (1, 4);

Output:

-- Find specific orders by their IDs
SELECT id, order_date, total_amount, status
FROM orders
WHERE id IN (1, 4, 7);

Output:

IN with Text Values

When using IN with text, each value must be enclosed in single quotes:

-- Find orders with specific statuses
SELECT id, order_date, total_amount, status
FROM orders
WHERE status IN ('pending', 'shipped');

Output:

IN with a Single Value

IN with a single value is technically valid and equivalent to =:

-- These two queries are identical
SELECT name FROM products WHERE category_id IN (2);
SELECT name FROM products WHERE category_id = 2;

Both return the same result, but = is clearer when matching a single value. Use IN when you have two or more values.

IN and NULL

An important detail: IN does not match NULL values. If your list contains NULL, rows with NULL in the column will still not be returned:

-- This does NOT find rows where city is NULL
SELECT first_name, city
FROM customers
WHERE city IN ('New York', NULL);

Output:

Grace, whose city is NULL, is not included. The NULL in the list is ignored because city = NULL evaluates to UNKNOWN, not true.

To include NULL values, add an explicit IS NULL check:

SELECT first_name, city
FROM customers
WHERE city IN ('New York') OR city IS NULL;

Output:

NOT IN: Excluding Values from a List

NOT IN does the opposite of IN: it returns rows where the column's value is not in the specified list.

SELECT columns
FROM table
WHERE column NOT IN (value1, value2, value3);

Basic NOT IN Examples

-- Customers NOT from New York or Chicago
SELECT first_name, last_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');

Output:

Notice that Grace (whose city is NULL) is missing. This is a critical behavior to understand.

-- Orders that are NOT completed
SELECT id, order_date, status
FROM orders
WHERE status NOT IN ('completed');

Output:

The NOT IN and NULL Trap

This is one of the most dangerous pitfalls in SQL. If any value in the NOT IN list is NULL, the entire expression returns no rows at all.

-- Dangerous: NULL in the NOT IN list
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', NULL);

Output:

(empty result set, zero rows)

This surprises almost every beginner. Here is why it happens:

For each row, the database evaluates: "Is this city NOT equal to 'New York' AND NOT equal to NULL?"

The NOT equal to NULL part always evaluates to UNKNOWN. And true AND UNKNOWN is UNKNOWN. Since UNKNOWN is not true, no row ever passes the filter.

The logic breaks down like this for Bob (city = 'Los Angeles'):

When using NOT IN on a column that itself might contain NULL, those rows will also be excluded:

-- Grace (city IS NULL) is excluded from NOT IN results
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago');
-- Grace is missing

Output:

-- To include NULL cities, handle them explicitly
SELECT first_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago') OR city IS NULL;
-- Grace is now included

Output:

The BETWEEN Operator

The BETWEEN operator checks if a value falls within a range, inclusive of both endpoints. It is a shorthand for a combined >= and <= condition.

SELECT columns
FROM table
WHERE column BETWEEN low_value AND high_value;

This is equivalent to:

WHERE column >= low_value AND column <= high_value

BETWEEN with Numbers

-- Products priced between $30 and $70 (inclusive)
SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 70;

Output:

Every product with a price from $30.00 to $70.00, including the exact boundary values, is included.

Let us verify the inclusive behavior:

-- Is the boundary value $45.00 included?
SELECT name, price
FROM products
WHERE price BETWEEN 45 AND 90;

Output:

Both $45.00 (the lower boundary) and $89.99 (within the range) are included. If the Mechanical Keyboard were exactly $90.00, it would also be included because BETWEEN is inclusive on both ends.

-- Products with stock between 50 and 150 units
SELECT name, stock_quantity
FROM products
WHERE stock_quantity BETWEEN 50 AND 150;

Output:

Both boundary values (50 and 150) are included in the results.

BETWEEN with Dates

BETWEEN is especially useful for date ranges, making temporal queries clean and readable:

-- Orders placed in January 2024
SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Output:

-- Orders placed in the first quarter of 2024 (Jan through Mar)
SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

Output:

-- Customers who signed up in the second half of 2023
SELECT first_name, last_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2023-07-01' AND '2023-12-31';

Output:

BETWEEN with Text

BETWEEN works with text values too, using alphabetical order:

-- Products whose name falls alphabetically between 'C' and 'S'
SELECT name, price
FROM products
WHERE name BETWEEN 'C' AND 'S'
ORDER BY name;

Output:

Names starting with "C" through names starting with "R" are included. "SQL for Beginners" and "Stainless Water Bottle" start with "S", but whether they are included depends on exact alphabetical comparison. "S" alone falls at the start of the "S" range, so "SQL for Beginners" (which is alphabetically after "S") is excluded.

While technically valid, using BETWEEN with text is uncommon in practice. Numeric and date ranges are by far the most common use cases.

The Order of Values Matters

The lower value must come first in BETWEEN. Reversing the values returns no results:

-- Wrong: Higher value first returns nothing
SELECT name, price
FROM products
WHERE price BETWEEN 70 AND 30;

Output:

(empty result set, zero rows)

No price can simultaneously be >= 70 AND <= 30, so no rows match.

-- Correct: Lower value first
SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 70;

Output:

This seems obvious, but it is a common source of confusion, especially when values come from variables in application code.

NOT BETWEEN: Excluding a Range

NOT BETWEEN returns rows where the value falls outside the specified range:

-- Products NOT priced between $30 and $70
SELECT name, price
FROM products
WHERE price NOT BETWEEN 30 AND 70;

Output:

These are products priced either below $30 or above $70. The boundary values ($30 and $70) are excluded because NOT BETWEEN is the inverse of BETWEEN.

-- Orders NOT placed in Q1 2024
SELECT id, order_date, total_amount
FROM orders
WHERE order_date NOT BETWEEN '2024-01-01' AND '2024-03-31';

Output:

Only orders from April 2024 onward remain, since all others fall within the Q1 range.

Combining IN, BETWEEN, and NOT

The real power of these operators emerges when you combine them with each other and with other SQL clauses.

IN + BETWEEN

-- Electronics and Sports products priced between $25 and $50
SELECT name, price, category_id
FROM products
WHERE category_id IN (1, 4)
  AND price BETWEEN 25 AND 50;

Output:

Both conditions must be satisfied: the product must be in category 1 or 4, and its price must fall between $25 and $50.

NOT IN + BETWEEN

-- Products NOT in Electronics (1) or Books (2), priced between $20 and $50
SELECT name, price, category_id
FROM products
WHERE category_id NOT IN (1, 2)
  AND price BETWEEN 20 AND 50;

Output:

IN + NOT BETWEEN

-- Pending or shipped orders with totals outside the $50-$100 range
SELECT id, order_date, total_amount, status
FROM orders
WHERE status IN ('pending', 'shipped')
  AND total_amount NOT BETWEEN 50 AND 100;

Output:

Order 7 ($63.00, pending) falls within the $50-$100 range, so it is excluded.

Complex Multi-Operator Query

-- Find products for a "Budget Picks" feature:
-- Categories: Electronics (1), Books (2), or Sports (4)
-- Price: Between $20 and $45
-- Must be in stock
-- Sorted by price, cheapest first
SELECT name, price, category_id, stock_quantity
FROM products
WHERE category_id IN (1, 2, 4)
  AND price BETWEEN 20 AND 45
  AND stock_quantity > 0
  AND is_available = true
ORDER BY price ASC;

Output:

This query combines IN, BETWEEN, comparison operators, and ORDER BY into a single, readable statement.

IN vs BETWEEN: When to Use Which

Both operators are filtering tools, but they solve different problems:

-- IN: Best for discrete, non-sequential values
WHERE category_id IN (1, 3, 7)

-- BETWEEN: Best for continuous ranges
WHERE price BETWEEN 10 AND 50

-- Don't use BETWEEN for non-sequential values
-- This is confusing and includes values you might not want:
WHERE category_id BETWEEN 1 AND 4
-- Includes category 2 and 3, which you might not intend

Preview: IN with Subqueries

One of the most powerful features of IN is its ability to use the results of another query as its value list. This is called a subquery, and it transforms IN from a simple list-matcher into a dynamic filtering tool.

The Concept

Instead of manually typing a list of values, you let SQL generate the list from another table:

-- Manual list
WHERE customer_id IN (1, 2, 3)

-- Dynamic list from a subquery
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York')

The subquery inside the parentheses runs first, produces a list of values, and then IN uses that list for filtering.

Practical Example: Products That Have Been Ordered

Find all products that appear in at least one order:

SELECT name, price
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items)
ORDER BY name;

Output:

The subquery SELECT DISTINCT product_id FROM order_items generates the list of product IDs that have been ordered. The outer query then finds products whose ID appears in that list.

Products That Have NEVER Been Ordered

SELECT name, price
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items)
ORDER BY name;

Output:

Only the Bluetooth Speaker and Data Science Handbook have never appeared in any order.

Customers Who Have Placed Orders

SELECT first_name, last_name, city
FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

Output:

Orders for Products in a Specific Category

Find all orders that include at least one Electronics product (category 1):

SELECT DISTINCT o.id, o.order_date, o.total_amount
FROM orders o
WHERE o.id IN (
    SELECT oi.order_id
    FROM order_items oi
    WHERE oi.product_id IN (
        SELECT p.id
        FROM products p
        WHERE p.category_id = 1
    )
)
ORDER BY o.order_date;

Output:

This example nests one subquery inside another. The innermost query finds Electronics product IDs, the middle query finds order IDs containing those products, and the outer query retrieves the full order details.

BETWEEN vs Comparison Operators: Which Is Clearer?

BETWEEN is syntactic sugar for a combined >= and <= condition. Both approaches produce identical results:

-- Using BETWEEN
SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 70;

-- Using comparison operators
SELECT name, price
FROM products
WHERE price >= 30 AND price <= 70;

So which should you use? It depends on the situation:

Use BETWEEN when:

• Both boundaries are inclusive (which is the common case for ranges)
• The range is the primary focus of the condition
• Readability is the priority

Use comparison operators when:

• You need an exclusive boundary (> or < instead of >= or <=)
• You only have one boundary (e.g., "prices above $50")
• You need asymmetric boundaries (inclusive on one end, exclusive on the other)

-- BETWEEN cannot express this: prices from $30 (inclusive) to $70 (exclusive)
-- You must use comparison operators:
SELECT name, price
FROM products
WHERE price >= 30 AND price < 70;

-- BETWEEN always includes both boundaries:
WHERE price BETWEEN 30 AND 70
-- Is equivalent to:
WHERE price >= 30 AND price <= 70

Practical Exercises

Exercise 1

Find all products in the Books (2) or Home & Kitchen (3) categories.

SELECT name, price, category_id
FROM products
WHERE category_id IN (2, 3);

Expected output:

Exercise 2

Find all orders placed between February 1 and March 31, 2024, sorted by date.

SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-03-31'
ORDER BY order_date;

Expected output:

Exercise 3

Find all products NOT in the Electronics category (1) with prices between $20 and $50.

SELECT name, price, category_id
FROM products
WHERE category_id NOT IN (1)
  AND price BETWEEN 20 AND 50;

Expected output:

Exercise 4

Find customers who are NOT from New York, Chicago, or Los Angeles (including those with unknown cities).

SELECT first_name, last_name, city
FROM customers
WHERE city NOT IN ('New York', 'Chicago', 'Los Angeles')
   OR city IS NULL;

Expected output:

Exercise 5

Find all products that have been ordered and are priced between $25 and $100, sorted by price descending.

SELECT name, price
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items)
  AND price BETWEEN 25 AND 100
ORDER BY price DESC;

Expected output:

Key Takeaways

IN, BETWEEN, and their NOT counterparts give you concise, expressive ways to filter data that would otherwise require verbose chains of OR and AND conditions. Here is what you should remember:

• IN matches a value against a list of specific values: WHERE status IN ('pending', 'shipped')
• NOT IN excludes rows matching any value in the list: WHERE city NOT IN ('New York', 'Chicago')
• Never put NULL in a NOT IN list. It silently returns zero rows without any error
• IN does not match NULL values in the column. Use OR column IS NULL if needed
• BETWEEN checks if a value falls within an inclusive range: WHERE price BETWEEN 20 AND 50
• BETWEEN is inclusive on both ends (equivalent to >= AND <=)
• NOT BETWEEN returns rows outside the specified range
• The lower value must come first in BETWEEN, or no rows will match
• Be cautious using BETWEEN with timestamps: the upper boundary might exclude records from the last day
• Use IN for discrete lists of values. Use BETWEEN for continuous ranges
• IN can accept a subquery instead of a manual list, making it dynamically powerful
• All these operators combine freely with AND, OR, NOT, LIKE, ORDER BY, LIMIT, and everything else you have learned

These operators round out your filtering toolkit. Combined with comparison operators, LIKE, logical operators, and the other clauses you have mastered, you can now express virtually any data filtering requirement in clean, readable SQL.