Subqueries in WHERE (IN, EXISTS, ANY, ALL) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Scalar Subqueries, BETWEEN, IN, and LIKE
Builds toward Correlated Subqueries
What is a subquery in a SQL WHERE clause?
A subquery in WHERE filters rows based on whether a value appears in a separate query's results.
You need all customers who have placed at least one order. The customer IDs with orders live in the orders table. You could join the two tables and deduplicate, but there's a more direct way: ask SQL to check, for each customer, whether their ID appears in the set of customer IDs from the orders table. That check — does this value exist in that subquery's result — is what IN, NOT IN, EXISTS, and NOT EXISTS do.
How do you use IN with a subquery?
IN (subquery) passes a row when the column value appears anywhere in the subquery's result:
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders)SQL runs the subquery first, collects all customer_id values from orders, and then keeps only customers whose id appears in that set.
NOT IN inverts it — customers who have never placed an order:
SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)What is the difference between IN and EXISTS?
EXISTS checks whether the subquery returns any rows at all for the current outer row. This is a correlated subquery — the inner query references the outer row:
SELECT id, name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)The inner query runs once per customer, checking whether any order exists for that customer. SELECT 1 is conventional — the actual value doesn't matter, only whether any row is returned. EXISTS is often faster than IN on large tables because it stops as soon as it finds one match.
NOT EXISTS finds customers with no matching orders — the same result as NOT IN, but with different NULL behavior.
SELECT id, name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
What do ANY and ALL do in a SQL subquery?
ANY and ALL compare a value against every result from a subquery. price > ANY (subquery) passes when the price is greater than at least one value in the subquery's result. price > ALL (subquery) passes only when it's greater than every value.
Why does NOT IN return no rows when the subquery has NULLs?
The one thing that trips people up: NOT IN with NULLs in the subquery.
If the subquery contains any NULL values, NOT IN returns no rows at all — not some rows, not an error. Empty result. The reason is subtle: SQL can't confirm that a value is "not equal to NULL" because comparisons with NULL are undefined. If the subquery might return NULLs, use NOT EXISTS instead — it handles NULLs correctly.
The orders table has a row where customer_id is NULL. You run: WHERE id NOT IN (SELECT customer_id FROM orders). How many rows come back?
Practice Subqueries in WHERE
Brightlane's CRM team is building an active-buyer segment for a promotional campaign and needs to identify customers who have placed at least one order.
Write a query to return the customer ID and name for every customer whose id appears in the orders table.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - The
orderstable contains every order;customer_ididentifies the buyer. - A customer with multiple orders appears once in the result.
Output:
- One row per active buyer, with columns
idandname.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
nest a subquery in Station Zero9 Subqueries in WHERE practice problems
Write a query to return the customer ID and name for every customer whose id appears in the orders table.
Write a query to return the user ID and name for every user who has made at least one conversion.
Write a query to return the category ID and name for every utilised category.
Write a query to return the user ID and name for every user who has at least one session on record.
Write a query to return the customer ID and name for every customer with no purchase history.
Write a query to return the employee ID and name for every employee who has at least one direct report on record.
Write a query to return the name and price of every qualifying product.
Write a query to return the name of every such product.
Write a query to return the name and price of every premium product.
Start learning to practice all 9 Subqueries in WHERE problems, with instant grading and mastery tracking.
Common questions about Subqueries in WHERE
How many columns can a subquery inside IN return?
One, unless you compare against a matching list of columns. Selecting two columns into a plain IN is rejected with a complaint that the subquery has too many columns, which usually means a stray column was left in the SELECT.
Does it matter what an EXISTS subquery selects?
No. EXISTS only asks whether any row came back, so selecting one, selecting several columns, or selecting NULL all give the same answer. Writing SELECT 1 is a convention that signals the value is deliberately unused.
Is NOT EXISTS safer than NOT IN?
Where the subquery can produce NULLs, yes. NOT IN against a list containing a NULL returns no rows at all, because SQL cannot confirm a value differs from something unknown. NOT EXISTS asks a different question and is unaffected.
