IN and NOT IN Operators in SQL: Matching Against a List of Values

Checking a column against several acceptable values — a status of 'shipped', 'delivered', or 'out_for_delivery' — could be written as a long chain of OR conditions, but SQL offers a cleaner, more direct way: IN. Its inverse, NOT IN, excludes a list of values instead. This lesson covers both, along with an important NULL-related gotcha.

What Are IN and NOT IN?

IN is an operator that checks whether a column's value matches any value in a specified list, returning true if at least one match is found. NOT IN checks the opposite — that a column's value matches none of the values in the list. Both are essentially more readable shorthand for a chain of equality checks combined with OR (for IN) or AND (for NOT IN).

What You'll Learn

• Use IN to match a column against a list of acceptable values.
• Use NOT IN to exclude a list of values from results.
• Understand how IN compares to an equivalent chain of OR conditions.
• Recognize the important NULL-related gotcha with NOT IN.

Key Terms to Know

• IN: An operator matching a column's value against any value in a specified list.
• NOT IN: An operator excluding rows whose column value matches any value in a specified list.
• Subquery: A SELECT query nested inside another query, which can be used as the list source for IN.

IN: Matching Any Value in a List

WHERE status IN ('shipped', 'delivered', 'out_for_delivery'); returns every row whose status matches any one of those three values. This is logically identical to WHERE status = 'shipped' OR status = 'delivered' OR status = 'out_for_delivery';, but considerably easier to read and write, especially as the list grows longer.

NOT IN: Excluding a List of Values

WHERE status NOT IN ('cancelled', 'refunded'); returns every row whose status is neither 'cancelled' nor 'refunded'. This is the direct inverse of IN, equivalent to status != 'cancelled' AND status != 'refunded'.

The NULL Gotcha with NOT IN

If the list used in NOT IN contains a NULL value — including indirectly, such as when the list comes from a subquery that happens to return a NULL — the entire NOT IN condition can unexpectedly return no rows at all, because comparing anything to NULL evaluates to unknown rather than true or false, which poisons the whole NOT IN check.

This is a genuinely common, hard-to-spot bug: WHERE customer_id NOT IN (SELECT customer_id FROM banned_customers); will silently return zero rows if even one row in banned_customers has a NULL customer_id. Filtering out NULLs from the subquery explicitly (WHERE customer_id IS NOT NULL) avoids this trap entirely.

Visual Summary

Picture a guest list at the door of an event. IN is the host checking a name against a short approved list and letting the person in in if it matches any name on it. NOT IN is the host checking a name against a banned list and turning the person away only if it matches. But if even one entry on the banned list is illegibly blank (NULL), the host gets confused and stops letting anyone in at all — exactly the kind of silent failure NOT IN can produce with NULLs in its list.

IN vs NOT IN vs Manual OR/AND Chains

Using IN/NOT IN	Equivalent Manual Form
status IN ('a', 'b', 'c')	status = 'a' OR status = 'b' OR status = 'c'
status NOT IN ('a', 'b')	status != 'a' AND status != 'b'

SQL Example

-- IN: orders in any of three active-ish statuses
SELECT order_id, status
FROM orders
WHERE status IN ('shipped', 'delivered', 'out_for_delivery');

-- NOT IN: orders excluding cancelled and refunded
SELECT order_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

-- IN with a subquery: customers who have placed at least one order
SELECT customer_id, full_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

-- Safer NOT IN: explicitly filtering NULLs out of the subquery first
SELECT customer_id, full_name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM banned_customers WHERE customer_id IS NOT NULL
);

The first two queries show straightforward IN and NOT IN usage against a fixed list of literal values. The third demonstrates IN working with a subquery instead of a hardcoded list, returning customers who appear in the orders table at all. The fourth applies the NULL-safety pattern, explicitly excluding NULL customer_id values from the banned_customers subquery before using it in NOT IN, avoiding the silent zero-rows bug.

Real-World Examples

• E-commerce dashboards use WHERE status IN ('shipped', 'delivered') to quickly summarize fulfilled orders without writing a long OR chain.
• Access control systems use WHERE user_role NOT IN ('banned', 'suspended') to filter active users efficiently.
• Reporting queries use IN with a subquery to find customers who do or don't belong to a specific dynamic segment, like recent purchasers.
• Data cleanup scripts use NOT IN carefully (with the NULL-safety pattern) to identify records that don't appear in a reference or lookup table.

Best Practices and Pro Tips

• Reach for IN whenever a WHERE condition would otherwise require three or more chained OR conditions on the same column — it's almost always more readable.
• Whenever NOT IN uses a subquery as its list, explicitly filter out NULLs from that subquery first to avoid the well-known silent zero-rows bug.
• For very large lists or lists sourced from another table, consider whether a JOIN-based approach might be clearer or more efficient than a large IN list, especially as the list grows into the thousands of values.

Common Mistakes to Avoid

• Using NOT IN with a subquery that can return NULL values, causing the entire query to silently return zero rows with no obvious error.
• Writing long OR chains instead of IN, making the query harder to read and maintain than necessary.
• Forgetting that IN and NOT IN compare against an exact list of values, not a range — BETWEEN is the right tool for a continuous range instead.
• Assuming IN can be used to check for a pattern or partial match — for that, LIKE (covered in the next lesson) is the correct tool.

Interview Questions

Q1. What is the IN operator, and what is it equivalent to?

IN checks whether a column's value matches any value in a specified list, and is functionally equivalent to chaining multiple equality checks together with OR, but is more concise and readable, especially for longer lists.

Q2. What is the well-known gotcha with NOT IN and NULL values?

If the list used in NOT IN contains a NULL value, the entire condition can unexpectedly evaluate to return zero rows, since comparing against NULL produces an unknown result that effectively breaks the NOT IN check for every row.

Q3. Can the list used with IN come from a subquery instead of literal values?

Yes, IN (and NOT IN) can use a subquery's result set as the list of values to match against, such as WHERE customer_id IN (SELECT customer_id FROM orders);, which is extremely common in real-world queries.

Q4. How would you safely rewrite a NOT IN subquery to avoid the NULL gotcha?

By explicitly filtering out NULL values from the subquery itself, such as adding WHERE column IS NOT NULL inside the subquery, ensuring the NOT IN list never contains a NULL that could silently break the comparison.

Practice MCQs

1. Which of these is equivalent to WHERE status IN ('a', 'b', 'c')?

1. WHERE status = 'a' AND status = 'b' AND status = 'c'
2. WHERE status = 'a' OR status = 'b' OR status = 'c'
3. WHERE status BETWEEN 'a' AND 'c'
4. WHERE status != 'a' AND status != 'b'

Answer: B. WHERE status = 'a' OR status = 'b' OR status = 'c'

Explanation: IN matches if the column equals any value in the list, exactly equivalent to chaining those equality checks together with OR.

2. What can cause NOT IN to silently return zero rows unexpectedly?

1. An empty list
2. A NULL value present in the comparison list
3. Using NOT IN with a subquery
4. Using NOT IN on a numeric column

Answer: B. A NULL value present in the comparison list

Explanation: If the list (including from a subquery) contains a NULL, the NOT IN comparison evaluates to unknown for every row, causing the query to return no rows at all.

3. Which operator is best suited for checking if a column's value matches one of several specific values?

1. BETWEEN
2. LIKE
3. IN
4. IS NULL

Answer: C. IN

Explanation: IN is specifically designed for matching a column against a defined list of specific acceptable values.

Quick Revision Points

• IN matches a column against any value in a list; NOT IN excludes all values in a list.
• IN is equivalent to chained OR; NOT IN is equivalent to chained AND with !=.
• NOT IN with a NULL in its list (including from a subquery) can silently return zero rows.
• IN/NOT IN lists can be literal values or the result of a subquery.

Conclusion

• IN and NOT IN make multi-value matching dramatically more readable than long OR/AND chains.
• The NULL gotcha with NOT IN is one of the most practically important traps to remember in everyday SQL.
• Subqueries inside IN/NOT IN are a powerful, frequently used pattern worth being comfortable with.

IN and NOT IN provide clean, readable shorthand for matching a column against a list of acceptable or unacceptable values, whether that list is written literally or produced by a subquery. The well-known NULL gotcha with NOT IN — where a single NULL in the list can silently zero out the entire result — is one of the most important practical lessons in this entire module to internalize. The next lesson covers LIKE and wildcards, for matching text patterns rather than exact values.