IN and NOT IN in SQL: Filter by a List of Values

Replace long chains of OR conditions with IN and NOT IN to filter rows against a list of values cleanly and accurately.

You need to pull orders with a status of 'Shipped', 'Delivered', or 'Processing'. You could chain three OR conditions, but that gets messy fast. Now imagine you need to exclude five specific product categories, or filter by a dynamic list of customer IDs from another query. IN and NOT IN handle all of these cases in a single, readable expression.

IN with a Literal List

IN checks whether a column value matches any item in a comma-separated list wrapped in parentheses.

-- Orders in three specific statuses
SELECT o.order_id, o.order_date, o.status, o.total_amount
FROM orders o
WHERE o.status IN ('Shipped', 'Delivered', 'Processing')
ORDER BY o.order_date DESC;

-- Customers from a specific set of countries
SELECT c.first_name, c.last_name, c.country, c.loyalty_tier
FROM customers c
WHERE c.country IN ('India', 'Australia', 'Canada', 'UK')
ORDER BY c.country, c.last_name;

-- Payments made by specific methods
SELECT pay.payment_id, pay.order_id, pay.amount, pay.method, pay.status
FROM payments pay
WHERE pay.method IN ('Credit Card', 'PayPal', 'Bank Transfer')
  AND pay.status = 'Completed'
ORDER BY pay.amount DESC;

The list can contain numbers, strings, or dates. String values must be quoted. The whole list goes inside one pair of parentheses.

NOT IN with a Literal List

NOT IN returns rows where the column value matches none of the items in the list. It is the inverse of IN.

-- Exclude orders with terminal statuses
SELECT o.order_id, o.customer_id, o.order_date, o.status
FROM orders o
WHERE o.status NOT IN ('Delivered', 'Cancelled')
ORDER BY o.order_date DESC;

-- Products not in certain categories
SELECT p.product_name, p.price, p.category_id, p.brand
FROM products p
WHERE p.category_id NOT IN (1, 2, 5)
  AND p.is_active = 1
ORDER BY p.price;

-- Employees not in specific departments
SELECT e.first_name, e.last_name, e.department, e.role
FROM employees e
WHERE e.department NOT IN ('Finance', 'HR')
ORDER BY e.department, e.last_name;

Key idea: Never put a NULL inside a NOT IN list. If any value in the list is NULL, the entire NOT IN expression evaluates to NULL for every row, returning zero results. This is one of the most common silent bugs in SQL filtering. Always use a literal list with no NULL values, or handle NULL separately.

IN with a Subquery

Instead of a hardcoded list, you can pass a subquery inside IN. The subquery must return a single column.

-- Orders placed by customers from India
SELECT o.order_id, o.order_date, o.total_amount, o.status
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'India'
)
ORDER BY o.order_date DESC;

-- Products that have appeared in at least one order
SELECT p.product_id, p.product_name, p.price
FROM products p
WHERE p.product_id IN (
    SELECT DISTINCT oi.product_id
    FROM order_items oi
)
ORDER BY p.product_name;

-- Orders that have at least one completed payment
SELECT o.order_id, o.order_date, o.status, o.total_amount
FROM orders o
WHERE o.order_id IN (
    SELECT pay.order_id
    FROM payments pay
    WHERE pay.status = 'Completed'
)
ORDER BY o.total_amount DESC
LIMIT 15;

NOT IN with a Subquery

NOT IN with a subquery finds rows that have no match in the subquery result. This is useful for finding orphaned or unmatched records.

-- Products that have never been ordered
SELECT p.product_id, p.product_name, p.price, p.stock_qty
FROM products p
WHERE p.product_id NOT IN (
    SELECT DISTINCT oi.product_id
    FROM order_items oi
)
AND p.is_active = 1
ORDER BY p.product_name;

-- Customers who have never placed an order
SELECT c.customer_id, c.first_name, c.last_name, c.email, c.created_at
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT DISTINCT o.customer_id
    FROM orders o
    WHERE o.customer_id IS NOT NULL
)
ORDER BY c.created_at DESC;

Note the WHERE o.customer_id IS NOT NULL inside the subquery. This is the safeguard against the NULL problem. If customer_id in orders can ever be NULL, and you do not filter it out, NOT IN will return zero rows.

Common Mistakes

-- wrong
SELECT o.order_id, o.status
FROM orders o
WHERE o.status IN ('Delivered', NULL);

-- correct
SELECT o.order_id, o.status
FROM orders o
WHERE o.status = 'Delivered' OR o.status IS NULL;

NULL inside an IN list does not match NULL rows. Use IS NULL separately when you need to include rows where the column has no value.

---

-- wrong
SELECT p.product_name
FROM products p
WHERE p.product_id NOT IN (
    SELECT oi.product_id FROM order_items oi
);
-- Returns zero rows if any product_id in order_items is NULL

-- correct
SELECT p.product_name
FROM products p
WHERE p.product_id NOT IN (
    SELECT oi.product_id
    FROM order_items oi
    WHERE oi.product_id IS NOT NULL
);

A NULL in the subquery result causes NOT IN to return no rows at all. Always add WHERE col IS NOT NULL inside the subquery when using NOT IN.

---

-- wrong
SELECT o.order_id
FROM orders o
WHERE o.status IN 'Delivered';

-- correct
SELECT o.order_id
FROM orders o
WHERE o.status IN ('Delivered');

The parentheses around the list are required even when there is only one value. Without them, the syntax is invalid and the query fails.

Quick Recap

IN matches a column against any value in a list. It is a cleaner alternative to multiple OR conditions on the same column.
NOT IN excludes rows matching any value in the list. Always add WHERE col IS NOT NULL inside a NOT IN subquery to avoid getting zero results due to a NULL in the list.
Both IN and NOT IN accept subqueries that return a single column, making them useful for dynamic filtering based on related table data.

Exercise 1 Easy

Write a query that returns order_id, order_date, status, and total_amount from the orders table for orders where status is one of 'Pending', 'Processing', or 'Shipped'. Sort by order_date descending.

Hint: Use WHERE o.status IN (...) with the three values listed inside parentheses.

Solution

SELECT o.order_id, o.order_date, o.status, o.total_amount FROM orders o WHERE o.status IN ('Pending', 'Processing', 'Shipped') ORDER BY o.order_date DESC;

Exercise 2 Medium

Write a query that returns product_name, price, brand, and category_id from the products table for active products whose brand is in the list 'Nike', 'Adidas', 'Puma' but whose category_id is NOT in the list 1, 3. Sort by brand ascending then price ascending.

Hint: Use IN for brand and NOT IN for category_id. Combine with AND and include the is_active = 1 filter.

Solution

SELECT p.product_name, p.price, p.brand, p.category_id FROM products p WHERE p.brand IN ('Nike', 'Adidas', 'Puma') AND p.category_id NOT IN (1, 3) AND p.is_active = 1 ORDER BY p.brand ASC, p.price ASC;

Exercise 3 Hard

Find all customers who have NOT placed any order with a status of 'Delivered'. Show customer_id, first_name, last_name, and country. Use a NOT IN subquery against the orders table. Exclude customers where customer_id is NULL in the subquery. Sort by country ascending then last_name ascending.

Hint: Use

WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o WHERE o.status = 'Delivered' AND o.customer_id IS NOT NULL)

Solution

SELECT c.customer_id, c.first_name, c.last_name, c.country FROM customers c WHERE c.customer_id NOT IN ( SELECT o.customer_id FROM orders o WHERE o.status = 'Delivered' AND o.customer_id IS NOT NULL ) ORDER BY c.country ASC, c.last_name ASC;

Frequently Asked Questions

What does IN do in SQL?

IN checks whether a column value matches any value in a specified list. It is a cleaner alternative to writing multiple OR conditions for the same column.

What is the difference between IN and OR in SQL?

They produce the same result for equality checks on a single column. IN is shorter and easier to read when you have more than two values to match.

How does NOT IN work in SQL?

NOT IN returns rows where the column value does not match any value in the list. Be careful with NULL values in the list, as they cause NOT IN to return no rows.

Can I use IN with a subquery in SQL?

Yes. IN accepts a subquery that returns a single column. This lets you filter rows based on the results of another query rather than a hardcoded list.