Subqueries in WHERE (IN, EXISTS, ANY, ALL) in SQL
`IN`, `EXISTS`, `ANY`, and `ALL` extend WHERE filtering to test a row's values against the results of a subquery. Each operator expresses a different membership or comparison relationship between the outer row and the subquery's output.
Before this Scalar Subqueries, BETWEEN, IN, and LIKE
Builds toward Correlated Subqueries
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.
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)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.
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.
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?
9 Subqueries in WHERE (IN, EXISTS, ANY, ALL) 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.
These problems are part of the Subqueries in WHERE (IN, EXISTS, ANY, ALL) lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.