IN

Why interviewers love this: IN looks simple, but it quietly tests filtering logic, readability, and the classic NULL trap.

Question: What does IN do in SQL?

Answer: IN checks whether a value matches any item in a list or in the result of a subquery. It is a compact way to write several equality checks at once, like col = 'A' OR col = 'B' OR col = 'C'. The main gotcha is that NOT IN behaves unexpectedly when NULL appears in the list or subquery result.

Interview-Ready Answer: I use IN when I want to filter rows by membership in a set, either a literal list or a subquery. It makes the query shorter and easier to read than many OR conditions, and the optimizer can often turn it into an efficient membership check or semi-join. The detail I always remember is that NOT IN plus NULL can eliminate every row, so when nulls are possible I consider NOT EXISTS instead.

What IN really means

IN is a membership test: it asks, “Is this value one of these values?” For a single column, the database compares the left-hand value against each candidate and keeps the row if any candidate matches. In SQL, matching is based on three-valued logic, which means a comparison can be TRUE, FALSE, or UNKNOWN. That third state matters because WHERE only keeps rows where the result is TRUE.

How it works under the hood

1. The parser reads the IN list or the subquery and builds a membership test.
2. If the list is small and constant, the engine may store it in a fast lookup structure, like a hash set, so membership checks are quick.
3. If the right side is a subquery, the optimizer may rewrite the query into a semi-join, which means “return rows from the left side only if a match exists on the right side.”
4. For each candidate row, the engine checks whether at least one comparison is true.
5. If you write NOT IN, the engine must also account for NULL, because a single null can turn the whole result into UNKNOWN.

When to use it

Use IN when the set of allowed values is small, clear, and stable: statuses, categories, country codes, or IDs from a subquery. It is especially nice when you would otherwise write several equality checks. If the list becomes very large, a table or join is usually cleaner and easier to maintain.

IN vs alternatives

Performance notes

There is no single fixed complexity for SQL because the optimizer matters. Conceptually, a naive membership check is O(n * k) for n rows and k list items, but real engines often do better by hashing the list or using an index. If the filtered column is indexed, the database may do multiple index lookups instead of scanning the whole table. Small lists of a few values are usually fine; hundreds or thousands of values can increase parse time, plan size, and network payload. Also, many databases have parameter limits, such as SQL Server’s 2100-parameter limit, so a giant IN list may need a temp table or staging table instead.

Important edge cases

• IN (NULL) does not match normal values, because value = NULL is UNKNOWN, not TRUE.
• NOT IN with any NULL in the right side can return no rows at all.
• IN with a subquery ignores duplicate values; duplicates do not make the answer more true.
• An empty subquery is fine and matches nothing, but IN () is invalid syntax in most databases.

Memory model: think of IN as a bouncer with a guest list. If your name is on the list, you get in; if the list contains a blank name (NULL), the bouncer gets confused, and NOT IN can shut the door for everyone.

Real-world story

Imagine a checkout service for an e-commerce app. The team keeps a list of allowed order statuses and uses WHERE status IN ('PAID', 'PACKING', 'SHIPPED') to drive the warehouse dashboard. That works well because the business rule is small, readable, and changes rarely.

Now the bug story: a nightly job used NOT IN to exclude blocked customers from a marketing email list. One bad data import inserted a NULL into the block table, and suddenly the query returned zero customers. The symptom was a silent outage: no email campaign, empty logs except for a normal-looking query, and a support ticket from marketing asking why the send count dropped to zero. The fix was to replace NOT IN with NOT EXISTS and add a data-quality check to prevent nulls in the block list.

Follow-up & Tricky Questions:

• How is IN different from EXISTS? IN asks whether a value is in a set; EXISTS asks whether at least one related row exists. They often return the same result, but EXISTS is usually safer when nulls are involved.
• Can the optimizer rewrite IN? Yes. Many engines rewrite IN (subquery) into a semi-join or a hashed membership test, which can be much faster than evaluating the subquery repeatedly.
• Does IN use indexes? It can. If the filtered column is indexed, the engine may probe the index for each candidate value instead of scanning the whole table.
• Does IN remove duplicates? It does not change the source table’s duplicates by itself; it only decides which rows pass the filter. If the left table has duplicate rows, both can still be returned.
• What happens if the subquery returns many rows? The database still treats it as a membership set, but performance and memory use may get worse, so a temp table or join can be a better design.
• Why does NOT IN with NULL return nothing? Because one comparison becomes UNKNOWN, and in SQL a row is kept only when the WHERE condition is TRUE. UNKNOWN is not true, so the row is filtered out.
• Is IN (NULL) ever true? No for normal scalar comparisons. A null comparison does not produce true; it produces unknown, so the row is not selected.
• Can I write an empty IN list? Usually no. IN () is invalid syntax in most databases, so if your list may be empty you need a different pattern.

Common Mistakes:

• Using NOT IN with possible NULLs — correction: use NOT EXISTS or clean the nulls first.
• Replacing one simple check with a giant IN list — correction: if the list is large, load the values into a table and join.
• Assuming IN and JOIN are always interchangeable — correction: JOIN can duplicate rows and bring back extra columns, so it is not the same question.
• Forgetting that WHERE keeps only TRUE — correction: FALSE and UNKNOWN both get filtered out.

Memory Hook: IN is a guest list. If your name is on the list, you enter; if the list has a blank name, NOT IN can lock the whole door.

Cheat Sheet:

• IN = membership test.
• Works with literal lists and subqueries.
• Cleaner than many OR conditions.
• NOT IN + NULL is the classic trap.
• Use NOT EXISTS when nulls are possible.
• Large lists usually belong in a table, not in SQL text.

Practice Tasks:

• Write a query that returns all products whose category is in a list of three allowed categories.
• Rewrite that query twice: once with OR, once with IN, and compare readability.
• Create a small table with a NULL value and test how NOT IN and NOT EXISTS differ.