The trick to "multiple things for one AND in SQL" is that a single row can't equal two different values at once, so WHERE status = 'open' AND status = 'closed' returns zero rows on every major database. You read multiple values for one criterion with IN, and you read multiple criteria for one record by joining or filtering against a grouped subquery. The database engine decides which path runs fast, and picking the wrong one turns a millisecond lookup into a full scan.

That distinction drives everything below. Whether you're on SQL Server, MySQL, PostgreSQL, or SQLite, the same three tools β€” IN, EXISTS, and GROUP BY ... HAVING β€” solve the problem, but each one behaves differently under indexes, NULLs, and duplicate rows. Read on for the exact syntax, the failure cases that bite in production, and the performance numbers you can expect when you pick correctly.

Why One Column Can't Satisfy Two Separate AND Conditions

The core reason column = 'a' AND column = 'b' returns nothing is that SQL evaluates the two conditions row by row, and a single row holds a single value in that column. If status is 'open', then status = 'closed' is false, so the whole AND predicate fails for that row. No row can pass both tests simultaneously unless the column itself somehow held both strings, which it can't in a scalar column.

This trips people up because natural language hides the loop. When someone says "show me orders that are open and closed," they usually mean "orders in either state" or they mean "customers who have both an open order and a closed order." Those are two different questions, and SQL answers each with different tooling. The first is a list search on one column; the second is a grouped search across rows. Getting the reading right is half the battle.

A quick test confirms the behavior in any client. Run SELECT COUNT(*) FROM orders WHERE status = 'open' AND status = 'closed' on a table that definitely contains both values. The count comes back as exactly 0 every time. Swap the AND for OR and you get the combined total. That single experiment is the fastest way to show a junior developer why their filter returns an empty grid.

Watch the NULL edge case too. If a row's status is NULL, then status = 'open' is UNKNOWN, not true or false, and AND propagates that UNKNOWN so the row drops out of a WHERE clause entirely. That's why a filter on an optional column can silently discard rows you expected to keep. Test your query against a row with a NULL in the filtered column before you ship it.

The IN Operator: Reading Multiple Values for a Single Column

IN is the direct, index-friendly way to ask "does this column match any value in my list?" The syntax is WHERE status IN ('open','pending','shipped'), and the optimizer treats it as a chain of equality tests joined by OR. Compared with writing three OR clauses by hand, IN reads cleaner and, on most engines, produces an identical execution plan, so there's rarely a performance penalty for using it.

Filter style Typical use Index behavior NULL handling
col IN (a, b, c) List of discrete values Uses a range or index seek NULL value in list never matches a row
col = a OR col = b Same as above, verbose Same plan as IN on most engines Same NULL caveat
col BETWEEN a AND b Contiguous numeric or date range Uses range seek NULL column never matches
col IN (SELECT ...) Values from another table May seek or scan depending on stats Subquery returning NULL is harmless to outer match

The table above is worth memorizing because the biggest production error with IN is stuffing a NULL into the list and expecting it to catch rows where the column is empty. It won't. WHERE status IN ('open', NULL) matches only 'open' rows because status = NULL is always UNKNOWN. To include missing values, write WHERE status IN ('open') OR status IS NULL explicitly.

Use IN when the number of values stays modest, roughly under a few hundred per query on SQL Server and MySQL, and under a few thousand on PostgreSQL where larger lists are generally handled better. Beyond that, a join against a temp table or a values table outruns a giant literal list because the optimizer can gather statistics on the joined set. For a short enumerable list of statuses or region codes, IN is the right tool with no second-guessing.

The AND Trap: Reading Multiple Rows That Share One Key

When the real question is "which customers have both an open order and a closed order," the answer lives across rows, not within one. You need two conditions applied to two different rows of the same table, tied together by a shared key. That's where AND on two correlated EXISTS clauses earns its place, and where a naive WHERE status = 'open' AND status = 'closed' collapses to nothing.

Take the orders table with customer_id and status. To find every customer with at least one row matching each status, write two half-conditions:

The EXISTS subqueries each hunt for one status per customer, and the outer AND keeps only customers where both found a match. The GROUP BY collapses the multiple outer rows to one per customer. On a table with 2 million orders and an index on (customer_id, status), this pattern usually resolves with two index seeks per candidate customer, which is far cheaper than a self-join that materializes duplicates.

The same logic works with IN subqueries instead of EXISTS: WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'open') AND customer_id IN (SELECT customer_id FROM orders WHERE status = 'closed'). Choose EXISTS when you only need existence and the inner table is large; choose IN when the inner result is small or you want the optimizer to materialize a hash set. Both demand that customer_id be indexed, or you'll scan the table for each subquery.

GLOSSARY

Term Meaning
IN Operator matching a column against a list or subquery of values
EXISTS Predicate returning true if the subquery produces at least one row
GROUP BY ... HAVING Groups rows by key, then filters the groups rather than individual rows
Correlated subquery Subquery that references columns from the outer query, re-evaluated per row
Full scan Reading every row in a table because no index covers the filter
Index seek Jumping directly to matching rows via a B-tree index
NULL Unknown value; never equal to anything, including another NULL
SARGable Search-argument-able: a predicate the optimizer can turn into an index operation

GROUP BY and HAVING: The Cleaner Way to Require Two Values

The grouped approach is often the most readable: group by the entity key, count the distinct statuses, and keep only groups whose count matches the number of statuses you demanded. It handles three or more required values without piling on subqueries, and it expresses the intent in one pass over the table.

This query reads as "for each customer, count the distinct required statuses they hold, and keep only those who have both." To require three statuses, change the IN list and set the HAVING count to 3; the SQL stays structurally identical, which is the real win. The WHERE clause does the heavy lifting of restricting rows before grouping, and HAVING filters the aggregates after.

One failure case worth knowing: forget the IN filter in the WHERE clause and the COUNT(DISTINCT status) counts every distinct status, so a customer with four unrelated statuses passes a check that asks for two. Always scope the WHERE to the demanded values before counting, or use COUNT(DISTINCT CASE WHEN status IN (...) THEN status END) if you need to count only the matching ones while leaving the rest of the rows untouched.

The grouped pattern also plays nicely with indexes on (customer_id, status) because the WHERE restricts the scan before the aggregation. On MySQL 8.0 and later, the optimizer can sometimes push the condition down, but on a table with millions of rows and only an index on status, expect a full scan of that status partition. Check the EXPLAIN output on your own server, since the plan shifts with statistics.

EXISTS and NOT EXISTS: Filtering One Table Against Existence in Another

EXISTS is the go-to when you don't need any value from the subquery table β€” just confirmation that a matching row is there. The classic example ties a parent to any child: list every customer who has at least one order, which is SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). The inner SELECT 1 is deliberate; the engine ignores the select list for EXISTS, so the numeral is a signal of intent, not a performance lever.

The mirror, NOT EXISTS, lists customers with no orders and is the safe replacement for the NOT IN trap covered later. On a schema where an orphaned customer can exist before their first order, NOT EXISTS returns those customers as expected, even when the orders.customer_id column is nullable. That's a real edge case in e-commerce staging databases where test customers outlive test orders.

Performance hinges on the join column carrying an index in the subquery table. Without an index on orders.customer_id, EXISTS scans orders for each customer row in the outer query, and a 10,000-customer table against a 5-million-order table turns into 50 billion comparisons unless the optimizer rewrites it as a hash semi-join. That rewrite usually happens on SQL Server and PostgreSQL, but not always, so check the plan.

Combine EXISTS with AND when you need two separate checks across rows, as shown in the earlier customer example. Combine it with OR when either relationship qualifies. Keep the correlated subquery focused on one predicate per block; cramming several conditions into one EXISTS weakens the optimizer's ability to pick the best index, and it makes the query far harder to debug when a row unexpectedly disappears.

NOT IN and NULL: The Silent Query Killer

The single most common "why does my query return nothing or everything" bug on this topic is NOT IN against a list that contains a NULL. If any value in the subquery or list is NULL, the NOT IN predicate evaluates to UNKNOWN for every row, so the query returns zero rows β€” even when the correct answer is thousands. This is documented behavior, not a bug, but it catches experienced developers during a late-night incident.

Test it in four lines. Create a two-row table with values 1 and NULL, then run SELECT 1 WHERE 1 NOT IN (SELECT val FROM t). The result is empty because 1 <> NULL is UNKNOWN, poisoning the whole WHERE. Replace NOT IN with NOT EXISTS (SELECT 1 FROM t WHERE t.val = 1) and the single non-matching row appears as expected. The fix is trivial once you know the cause, and the cause is worth a bookmark.

Databases differ slightly here. SQL Server and MySQL both exhibit the zero-row behavior; PostgreSQL is the same but more likely to surface a warning in the client when you benchmark it, and some IDE linters flag NOT IN on a nullable column automatically. None of the three silently "fixes" it, so defensive coding β€” NOT EXISTS for anti-joins, or an explicit WHERE col IS NOT NULL on the subquery β€” is the reliable route.

On a wide table the failure is doubly frustrating because the query still runs fast; it just returns nothing, so a test suite that only checks for errors won't catch it. Add an assertion that a known-good row appears in the output, and the bug surfaces on the first run instead of reaching production.

Reading Multiple Values Across Several Columns

Plenty of teams need to match a composite key: for example, every (customer_id, product_id) pair in a watchlist. That's WHERE (customer_id, product_id) IN ((1,10),(2,20),(3,30)), a row-value construct supported in PostgreSQL, MySQL, and SQLite. SQL Server doesn't accept the tuple form directly, so you either join against a VALUES table or compare concatenated keys, and the join is the cleaner option because it preserves index usage.

Database Tuple IN on two columns Workaround when unsupported
PostgreSQL Supported natively None needed
MySQL 8.0 Supported natively None needed
SQLite Supported natively None needed
SQL Server 2019+ Not supported JOIN (VALUES (1,10),(2,20)) v(a,b) ON ...

The SQL Server join form looks like SELECT o.* FROM orders o JOIN (VALUES (1,10),(2,20)) v(cid, pid) ON o.customer_id = v.cid AND o.product_id = v.pid. That gives the optimizer a real rowset with statistics, so an index on (customer_id, product_id) is used as a seek. Concatenating keys with a separator also works but destroys index usability unless you've built a computed, persisted column, and even then the separator can collide with embedded values.

If you need all columns rather than a single key, and the values come from another table with duplicates, a DISTINCT or GROUP BY collapses the fanout. The tuple approach saves typing when the watchlist is short, but past a few hundred pairs the VALUES join or a temp table wins on both plan stability and readability.

Worked Example: Building Each Query and Reading the Plan

The practical test is not whether a query returns the right answer, but whether it returns it fast enough. Take a schema with 1,000,000 orders, 50,000 customers, and a required list of three statuses per customer. Build each candidate query and time it with SET STATISTICS TIME ON on SQL Server, EXPLAIN ANALYZE on PostgreSQL, or the SHOW PROFILES history on MySQL.

Approach Typical plan Rough cost at 1M rows Notes
status IN (...) Index seek Low Single-column filter
EXISTS twin predicate Two index seeks per customer Moderate Needs (customer_id, status) index
GROUP BY ... HAVING Index scan or range scan Moderate to high One pass, then aggregate
Correlated IN subqueries Often converted to hash semi-join Moderate Check plan for conversion

These are directional figures, not guarantees β€” your index statistics, fill factor, and server memory dictate real numbers. The reproducibility rule: capture the plan on a representative snapshot, not an empty dev database, because the optimizer picks different operators at 10 rows and 10 million. A query that's instant in development can crawl in production purely because the plan flips.

The practical takeaway is that the WHERE clause placement decides the cost. Filter the tail of the data as early as possible, keep the subquery predicates independent, and index the join key. When the WHERE clause pushes a selective condition to the storage engine, the aggregation and join layers have less to do.

Dialect Differences Worth Knowing

The same intent needs slightly different syntax depending on the engine. These differences are stable and don't shift month to month, but they do surface when code gets migrated between systems, and each one has a simple fix once you know the pattern.

MySQL treats NULL inside IN silently rather than erroring, and its optimizer may rewrite IN (subquery) as a semi-join on MySQL 8.0. A missing index on the subquery column can flip the plan to a full scan of the larger table, so verify the EXPLAIN cost even after a rewrite. Duplicate values in an IN list are quietly ignored, which is usually forgiving.

PostgreSQL supports row-value tuples and full LATERAL joins, giving cleaner ways to correlate than the tossed-together subquery. It also handles very large IN lists better than the other engines because it can hash the list, but past a few thousand entries the hash grows and the win narrows.

SQL Server rejects the tuple IN syntax, so use a VALUES join for composite keys. Its optimizer often turns EXISTS into a hash semi-join, but if the subquery references a non-indexed column, the plan reverts to a nested loop that multiplies cost by the outer row count.

FAQ

What's the fastest way to match a column against 500 values?

A WHERE column IN (...) list of 500 literals is generally fine through about a few hundred on SQL Server and MySQL, but past that, push the values into a temp table or a VALUES join so the optimizer gathers statistics. PostgreSQL handles larger literal lists because it can hash them, so 500 is comfortable there. The indexed column matter more than the list length: a seek on 500 values beats a scan on 50.

Why does NOT IN return zero rows when my subquery has a NULL?

Because x <> NULL evaluates to UNKNOWN rather than true, and UNKNOWN rows are excluded from the WHERE clause, so the entire predicate fails for every row. The fix is to rewrite as NOT EXISTS or add WHERE col IS NOT NULL inside the subquery. This is documented behavior across SQL Server, MySQL, and PostgreSQL, not an engine quirk.

Can I use AND to match two different statuses in the same row?

No β€” a single row can't hold 'open' and 'closed' at once, so column = 'open' AND column = 'closed' always returns zero rows. To require two values on separate rows of the same entity, combine two EXISTS subqueries, or group by the entity key with HAVING COUNT(DISTINCT status) = 2. Condition AND operates across rows, not within one.

Is GROUP BY ... HAVING slower than two EXISTS checks?

Not necessarily, and the split depends on the index. A composite index on (entity_id, status) lets both forms perform range seeks and the HAVING version usually costs a single pass, while the twin EXISTS version costs two seeks per entity. On a query requiring three or more values, the grouped form often wins on readability and holds its own on speed.

Does IN use an index on the column?

Yes, when the column is indexed, IN becomes a set of seeks or a range scan, since the optimizer treats it as an OR chain of equality tests. The exception is a list so large that the optimizer chooses a table scan over thousands of seeks. Check the actual plan rather than assuming, since statistics and server memory change the decision.

What's the difference between IN and EXISTS?

IN compares one column against a list or subquery result and returns true on any match, while EXISTS only asks whether the subquery produces at least one row and never looks at its columns. Use EXISTS when you only need existence and the inner table is large; use IN for a short literal list or a small subquery result. Both can be optimized by the same semi-join machinery in modern engines.

Why does my query return nothing at all after adding OR conditions?

A common cause is a downstream AND binding tighter than the OR, so a condition you intended as an alternative becomes part of a larger conjunction. Wrap the alternatives in parentheses, as in WHERE a = 1 AND (b = 2 OR c = 3). The other frequent cause is a NOT IN against a nullable column, covered above, which produces the same ghost-empty result.

Should I use DISTINCT or GROUP BY with IN subqueries?

Use DISTINCT when you just want to remove duplicate rows from the outer select, and GROUP BY when you need to aggregate or filter the groups with HAVING, as in the "has both statuses" pattern. Both can trigger a sort or hash step, so check the plan; on a large table, EXISTS often writes cleaner than either when you only need existence.