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:

first_name	last_name	city
Alice	Johnson	New York
Carol	Singh	Chicago
David	Chen	New York
Eva	Brown	Seattle
Frank	Wilson	Chicago

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:

first_name	last_name	city
Alice	Johnson	New York
Carol	Singh	Chicago
David	Chen	New York
Eva	Brown	Seattle
Frank	Wilson	Chicago

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

When to Use IN Instead of OR

Whenever you find yourself writing two or more OR conditions on the same column, replace them with IN. It is more readable, less error-prone, and easier to modify.

-- Hard to maintain: Adding a new city means adding another OR line
WHERE city = 'New York' OR city = 'Chicago' OR city = 'Seattle' OR city = 'Boston'

-- Easy to maintain: Just add to the list
WHERE city IN ('New York', 'Chicago', 'Seattle', 'Boston')

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:

name	price	category_id
Wireless Mouse	29.99	1
Mechanical Keyboard	89.99	1
USB-C Hub	45.00	1
Yoga Mat Premium	38.00	4
Running Shoes X1	110.00	4
Bluetooth Speaker	65.00	1
Stainless Water Bottle	24.99	4

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

Output:

id	order_date	total_amount	status
1	2024-01-10	119.98	completed
4	2024-03-05	129.99	shipped
7	2024-04-01	63.00	pending

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:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending

Common Mistake: Missing Quotes Around Text Values

-- Wrong: Text values without quotes cause an error
SELECT * FROM orders WHERE status IN (pending, shipped);
-- ERROR: column "pending" does not exist

-- Correct: Each text value must be in single quotes
SELECT * FROM orders WHERE status IN ('pending', 'shipped');

Without quotes, the database interprets pending and shipped as column names rather than text values.

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:

first_name	city
Alice	New York
David	New York

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:

first_name	city
Alice	New York
David	New York
Grace	NULL

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:

first_name	last_name	city
Bob	Martinez	Los Angeles
Eva	Brown	Seattle

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:

id	order_date	status
4	2024-03-05	shipped
5	2024-03-12	shipped
6	2024-03-20	pending
7	2024-04-01	pending
8	2024-04-10	pending

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'):

Check	Expression	Result
1	'Los Angeles' != 'New York'	true
2	'Los Angeles' != NULL	UNKNOWN
Combined	true AND UNKNOWN	UNKNOWN
Row included?		No

Golden Rule for NOT IN

Never include NULL in a NOT IN list. It silently returns zero rows without raising any error. If the list comes from a subquery that might produce NULL values, filter them out explicitly:

-- Dangerous: If the subquery returns any NULL, you get zero results
WHERE city NOT IN (SELECT city FROM some_table)

-- Safe: Filter NULLs from the subquery
WHERE city NOT IN (SELECT city FROM some_table WHERE city IS NOT NULL)

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:

first_name	city
Bob	Los Angeles
Eva	Seattle

-- 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:

first_name	city
Bob	Los Angeles
Eva	Seattle
Grace	NULL

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:

name	price
USB-C Hub	45.00
SQL for Beginners	34.99
Data Science Handbook	42.50
Yoga Mat Premium	38.00
Bluetooth Speaker	65.00

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:

name	price
Mechanical Keyboard	89.99
USB-C Hub	45.00
Bluetooth Speaker	65.00

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:

name	stock_quantity
Wireless Mouse	150
Mechanical Keyboard	75
SQL for Beginners	50
Yoga Mat Premium	100
Running Shoes X1	60

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:

id	order_date	total_amount	status
1	2024-01-10	119.98	completed
2	2024-01-15	89.99	completed

-- 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:

id	order_date	total_amount	status
1	2024-01-10	119.98	completed
2	2024-01-15	89.99	completed
3	2024-02-20	77.49	completed
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending

-- 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:

first_name	last_name	signup_date
David	Chen	2023-08-05

BETWEEN with Timestamps: Watch the Upper Boundary

When using BETWEEN with TIMESTAMP columns (which include time), the inclusive upper boundary can miss records from the last day.

-- Problem: This misses orders placed on Jan 31 at any time after midnight
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- '2024-01-31' is interpreted as '2024-01-31 00:00:00'
-- An order at '2024-01-31 14:30:00' is AFTER '2024-01-31 00:00:00'
-- So it gets EXCLUDED

-- Solution 1: Use the next day as the upper boundary with <
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

-- Solution 2: Include the full last day
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'

This issue only applies to TIMESTAMP/DATETIME columns. With DATE columns (no time component), BETWEEN works perfectly for date ranges.

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:

name	price
Coffee Maker Pro	129.99
Data Science Handbook	42.50
Mechanical Keyboard	89.99
Running Shoes X1	110.00

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:

name	price
USB-C Hub	45
SQL for Beginners	34.99
Data Science Handbook	42.5
Yoga Mat Premium	38
Bluetooth Speaker	65

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:

name	price
Wireless Mouse	29.99
Mechanical Keyboard	89.99
Coffee Maker Pro	129.99
Running Shoes X1	110.00
Stainless Water Bottle	24.99

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:

id	order_date	total_amount
7	2024-04-01	63.00
8	2024-04-10	199.98

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:

name	price	category_id
Wireless Mouse	29.99	1
USB-C Hub	45.00	1
Yoga Mat Premium	38.00	4

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:

name	price	category_id
Yoga Mat Premium	38.00	4
Stainless Water Bottle	24.99	4

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:

id	order_date	total_amount	status
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending
8	2024-04-10	199.98	pending

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:

name	price	category_id	stock_quantity
Stainless Water Bottle	24.99	4	180
Wireless Mouse	29.99	1	150
SQL for Beginners	34.99	2	50
Yoga Mat Premium	38.00	4	100
Data Science Handbook	42.50	2	30
USB-C Hub	45.00	1	200

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:

Scenario	Use	Example
Matching against a list of specific values	IN	WHERE status IN ('pending', 'shipped')
Checking if a value falls within a continuous range	BETWEEN	WHERE price BETWEEN 20 AND 50
Matching non-sequential values	IN	WHERE id IN (3, 7, 15, 22)
Filtering a date range	BETWEEN	WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
Matching against values from another query	IN (subquery)	WHERE id IN (SELECT product_id FROM order_items)

-- 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

Rule of Thumb

Use IN when you have a specific list of acceptable values. Use BETWEEN when you have a lower and upper boundary of a continuous range. If the values form a sequence and you want all of them, either operator works, but BETWEEN is typically cleaner for ranges.

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:

name	price
Coffee Maker Pro	129.99
Mechanical Keyboard	89.99
Running Shoes X1	110.00
SQL for Beginners	34.99
Stainless Water Bottle	24.99
USB-C Hub	45.00
Wireless Mouse	29.99
Yoga Mat Premium	38.00

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:

name	price
Bluetooth Speaker	65.00
Data Science Handbook	42.50

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:

first_name	last_name	city
Alice	Johnson	New York
Bob	Martinez	Los Angeles
Carol	Singh	Chicago
David	Chen	New York
Eva	Brown	Seattle
Frank	Wilson	Chicago

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:

id	order_date	total_amount
1	2024-01-10	119.98
2	2024-01-15	89.99
5	2024-03-12	155.00
8	2024-04-10	199.98

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.

Subqueries Are a Deep Topic

This section provides a preview of what subqueries can do with IN. Subqueries are a major SQL topic with their own patterns, performance considerations, and alternatives (like JOIN and EXISTS). They will be covered in detail in a dedicated guide. For now, understanding that IN can accept a query instead of a manual list gives you an idea of how powerful this operator becomes.

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:

name	price	category_id
SQL for Beginners	34.99	2
Data Science Handbook	42.50	2
Coffee Maker Pro	129.99	3

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:

id	order_date	total_amount	status
3	2024-02-20	77.49	completed
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending

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:

name	price	category_id
SQL for Beginners	34.99	2
Data Science Handbook	42.50	2
Yoga Mat Premium	38.00	4
Stainless Water Bottle	24.99	4

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:

first_name	last_name	city
Eva	Brown	Seattle
Grace	Taylor	NULL

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:

name	price
Mechanical Keyboard	89.99
USB-C Hub	45.00
Yoga Mat Premium	38.00
SQL for Beginners	34.99
Wireless Mouse	29.99

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.