{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"subqueries-and-exists","title":"Subqueries: a query inside a query","url":"https://addaly.com/learn/data-and-sql/subqueries-and-exists","position":20,"of":66,"minutes":8,"answer":"Reach for EXISTS and NOT EXISTS over IN and NOT IN: they say what you mean, cannot be poisoned by a NULL, and let the planner turn them into a join.","keyIdea":"Reach for EXISTS and NOT EXISTS over IN and NOT IN: they say what you mean, cannot be poisoned by a NULL, and let the planner turn them into a join."},"body":{"format":"markdown","text":"## Four shapes, four uses\n\nA subquery is a SELECT inside another statement. It appears in four positions, and they behave differently enough to be worth separating.\n\n**Scalar, in the SELECT list.** Returns exactly one row and one column, becoming a value on each output row:\n\n```sql\nSELECT o.id, o.amount,\n       (SELECT full_name FROM customers c WHERE c.id = o.customer_id) AS customer\nFROM orders o;\n```\n\nIt cannot change the grain, which is its great virtue. If it ever returns two rows, the query fails rather than silently duplicating: a much better failure than a join's.\n\n**In the FROM clause**, as a derived table. It must have an alias:\n\n```sql\nSELECT city, avg(order_count) FROM (\n  SELECT customer_id, city, count(*) AS order_count\n  FROM orders GROUP BY customer_id, city\n) per_customer\nGROUP BY city;\n```\n\nThis is how you aggregate an aggregate: the average number of orders per customer, by city, which no single GROUP BY can express.\n\n**In WHERE, with IN or a comparison.** Filters against a list computed elsewhere:\n\n```sql\nSELECT * FROM students\nWHERE id IN (SELECT student_id FROM payments WHERE amount > 20000);\n```\n\n**Correlated, referencing the outer query.** The subquery mentions a column from outside, so conceptually it runs once per outer row:\n\n```sql\nSELECT * FROM orders o\nWHERE o.amount > (SELECT avg(amount) FROM orders WHERE customer_id = o.customer_id);\n```\n\n\"Orders larger than that customer's own average.\" The `o.customer_id` reference is what makes it correlated.\n\n## EXISTS, and why it is the one to reach for\n\n```sql\nSELECT s.* FROM students s\nWHERE EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);\n```\n\n`EXISTS` asks a yes/no question and stops at the first matching row. `SELECT 1` is conventional: the columns are never read, so people write the cheapest thing possible.\n\nThree reasons to prefer it over the alternatives.\n\nIt **cannot multiply rows**. A join to `payments` would return one row per payment; `EXISTS` returns one row per student regardless of how many payments there are. When the question is \"which students have paid\" rather than \"what did they pay\", this is exactly right and no `DISTINCT` is needed.\n\nIt **handles NULL correctly**. `NOT IN` against a list containing a NULL returns zero rows, always, as the NULL lesson shows in detail. `NOT EXISTS` has no such trap.\n\nIt **stops early**. A student with 400 payments is settled by the first one.\n\n## IN versus EXISTS versus JOIN\n\nFor a simple membership test on modern Postgres, all three are usually rewritten into the same plan, and the choice is about readability. The differences that survive:\n\n- Use `EXISTS` when you want one row per outer row and only care whether a match exists.\n- Use `JOIN` when you need columns from the other table in the output.\n- Use `IN` when the list is small and literal, or comes from a simple subquery, and you find it clearer.\n- Never use `NOT IN` against a subquery on a nullable column.\n\nThat last one is worth writing on a wall. `NOT IN (SELECT student_id FROM payments)` returns nothing at all if a single `student_id` is NULL, and returning nothing looks like a valid finding.\n\n## The performance shape to know\n\nA correlated subquery *can* run once per outer row. Ten thousand outer rows and a subquery costing 2 ms is twenty seconds. Postgres often decorrelates these into a single join or hash aggregate, and then the cost disappears; but \"often\" is not \"always\", and other engines are less reliable about it.\n\nThe diagnostic is direct: run `EXPLAIN ANALYZE` and look for a node with `loops=10000`. That number is telling you the subquery ran ten thousand times. When you see it and the query is slow, rewrite the correlated form as a pre-aggregated CTE joined once:\n\n```sql\nWITH avg_per_customer AS (\n  SELECT customer_id, avg(amount) AS avg_amount FROM orders GROUP BY customer_id\n)\nSELECT o.* FROM orders o\nJOIN avg_per_customer a ON a.customer_id = o.customer_id\nWHERE o.amount > a.avg_amount;\n```\n\nOne pass to build the averages, one join to use them. Same answer, and it stays fast as the table grows.\n\n## Readability\n\nSubqueries nested three deep are the hardest SQL to read, because you have to hold the inner result in your head while parsing the outer one. Beyond one level of nesting, name the pieces with CTEs, the next lesson. A query built from four named steps is understandable by somebody who has never seen it; the same logic nested four deep is not."},"check":{"question":"`SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM blocked_customers)` returns no rows at all, although only a dozen customers are blocked. Why?","options":[{"text":"The subquery returns more rows than `NOT IN` can handle, so the comparison fails and returns nothing.","correct":false,"tempting":"There is no such row limit; large IN lists are slow rather than empty."},{"text":"`NOT IN` requires the subquery to return a single column backed by a unique constraint, and without one the comparison is undefined and returns nothing.","correct":false,"tempting":"Invents a requirement; duplicate values in the list are harmless."},{"text":"At least one `customer_id` in `blocked_customers` is NULL, so the expanded chain of inequalities contains a comparison that can never be true.","correct":true},{"text":"The two `customer_id` columns have different types, so no value ever matches and the negation excludes everything.","correct":false,"tempting":"A type mismatch would make nothing match, which for a negation would return *all* rows rather than none."}]},"prev":{"slug":"set-operations","title":"UNION, INTERSECT and EXCEPT","url":"https://addaly.com/learn/data-and-sql/set-operations"},"next":{"slug":"common-table-expressions","title":"CTEs: naming the steps","url":"https://addaly.com/learn/data-and-sql/common-table-expressions"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Subqueries: a query inside a query', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/subqueries-and-exists (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Subqueries: a query inside a query","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"Reach for EXISTS and NOT EXISTS over IN and NOT IN: they say what you mean, cannot be poisoned by a NULL, and let the planner turn them into a join.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"four-shapes-four-uses","runs":[{"text":"Four shapes, four uses"}]},{"t":"p","runs":[{"text":"A subquery is a SELECT inside another statement. It appears in four positions, and they behave differently enough to be worth separating."}]},{"t":"p","runs":[{"text":"Scalar, in the SELECT list.","b":true},{"text":" Returns exactly one row and one column, becoming a value on each output row:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT o.id, o.amount,\n       (SELECT full_name FROM customers c WHERE c.id = o.customer_id) AS customer\nFROM orders o;"},{"t":"p","runs":[{"text":"It cannot change the grain, which is its great virtue. If it ever returns two rows, the query fails rather than silently duplicating: a much better failure than a join’s."}]},{"t":"p","runs":[{"text":"In the FROM clause","b":true},{"text":", as a derived table. It must have an alias:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, avg(order_count) FROM (\n  SELECT customer_id, city, count(*) AS order_count\n  FROM orders GROUP BY customer_id, city\n) per_customer\nGROUP BY city;"},{"t":"p","runs":[{"text":"This is how you aggregate an aggregate: the average number of orders per customer, by city, which no single GROUP BY can express."}]},{"t":"p","runs":[{"text":"In WHERE, with IN or a comparison.","b":true},{"text":" Filters against a list computed elsewhere:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM students\nWHERE id IN (SELECT student_id FROM payments WHERE amount > 20000);"},{"t":"p","runs":[{"text":"Correlated, referencing the outer query.","b":true},{"text":" The subquery mentions a column from outside, so conceptually it runs once per outer row:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT * FROM orders o\nWHERE o.amount > (SELECT avg(amount) FROM orders WHERE customer_id = o.customer_id);"},{"t":"p","runs":[{"text":"“Orders larger than that customer’s own average.” The "},{"text":"o.customer_id","code":true},{"text":" reference is what makes it correlated."}]},{"t":"h2","id":"exists-and-why-it-is-the-one-to-reach-for","runs":[{"text":"EXISTS, and why it is the one to reach for"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT s.* FROM students s\nWHERE EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);"},{"t":"p","runs":[{"text":"EXISTS","code":true},{"text":" asks a yes/no question and stops at the first matching row. "},{"text":"SELECT 1","code":true},{"text":" is conventional: the columns are never read, so people write the cheapest thing possible."}]},{"t":"p","runs":[{"text":"Three reasons to prefer it over the alternatives."}]},{"t":"p","runs":[{"text":"It "},{"text":"cannot multiply rows","b":true},{"text":". A join to "},{"text":"payments","code":true},{"text":" would return one row per payment; "},{"text":"EXISTS","code":true},{"text":" returns one row per student regardless of how many payments there are. When the question is “which students have paid” rather than “what did they pay”, this is exactly right and no "},{"text":"DISTINCT","code":true},{"text":" is needed."}]},{"t":"p","runs":[{"text":"It "},{"text":"handles NULL correctly","b":true},{"text":". "},{"text":"NOT IN","code":true},{"text":" against a list containing a NULL returns zero rows, always, as the NULL lesson shows in detail. "},{"text":"NOT EXISTS","code":true},{"text":" has no such trap."}]},{"t":"p","runs":[{"text":"It "},{"text":"stops early","b":true},{"text":". A student with 400 payments is settled by the first one."}]},{"t":"h2","id":"in-versus-exists-versus-join","runs":[{"text":"IN versus EXISTS versus JOIN"}]},{"t":"p","runs":[{"text":"For a simple membership test on modern Postgres, all three are usually rewritten into the same plan, and the choice is about readability. The differences that survive:"}]},{"t":"ul","items":[[{"text":"Use "},{"text":"EXISTS","code":true},{"text":" when you want one row per outer row and only care whether a match exists."}],[{"text":"Use "},{"text":"JOIN","code":true},{"text":" when you need columns from the other table in the output."}],[{"text":"Use "},{"text":"IN","code":true},{"text":" when the list is small and literal, or comes from a simple subquery, and you find it clearer."}],[{"text":"Never use "},{"text":"NOT IN","code":true},{"text":" against a subquery on a nullable column."}]]},{"t":"p","runs":[{"text":"That last one is worth writing on a wall. "},{"text":"NOT IN (SELECT student_id FROM payments)","code":true},{"text":" returns nothing at all if a single "},{"text":"student_id","code":true},{"text":" is NULL, and returning nothing looks like a valid finding."}]},{"t":"h2","id":"the-performance-shape-to-know","runs":[{"text":"The performance shape to know"}]},{"t":"p","runs":[{"text":"A correlated subquery "},{"text":"can","i":true},{"text":" run once per outer row. Ten thousand outer rows and a subquery costing 2 ms is twenty seconds. Postgres often decorrelates these into a single join or hash aggregate, and then the cost disappears; but “often” is not “always”, and other engines are less reliable about it."}]},{"t":"p","runs":[{"text":"The diagnostic is direct: run "},{"text":"EXPLAIN ANALYZE","code":true},{"text":" and look for a node with "},{"text":"loops=10000","code":true},{"text":". That number is telling you the subquery ran ten thousand times. When you see it and the query is slow, rewrite the correlated form as a pre-aggregated CTE joined once:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH avg_per_customer AS (\n  SELECT customer_id, avg(amount) AS avg_amount FROM orders GROUP BY customer_id\n)\nSELECT o.* FROM orders o\nJOIN avg_per_customer a ON a.customer_id = o.customer_id\nWHERE o.amount > a.avg_amount;"},{"t":"p","runs":[{"text":"One pass to build the averages, one join to use them. Same answer, and it stays fast as the table grows."}]},{"t":"h2","id":"readability","runs":[{"text":"Readability"}]},{"t":"p","runs":[{"text":"Subqueries nested three deep are the hardest SQL to read, because you have to hold the inner result in your head while parsing the outer one. Beyond one level of nesting, name the pieces with CTEs, the next lesson. A query built from four named steps is understandable by somebody who has never seen it; the same logic nested four deep is not."}]}],"check":{"question":[{"text":"SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM blocked_customers)","code":true},{"text":" returns no rows at all, although only a dozen customers are blocked. Why?"}],"options":[{"text":[{"text":"The subquery returns more rows than "},{"text":"NOT IN","code":true},{"text":" can handle, so the comparison fails and returns nothing."}],"correct":false,"tempting":[{"text":"There is no such row limit; large IN lists are slow rather than empty."}]},{"text":[{"text":"NOT IN","code":true},{"text":" requires the subquery to return a single column backed by a unique constraint, and without one the comparison is undefined and returns nothing."}],"correct":false,"tempting":[{"text":"Invents a requirement; duplicate values in the list are harmless."}]},{"text":[{"text":"At least one "},{"text":"customer_id","code":true},{"text":" in "},{"text":"blocked_customers","code":true},{"text":" is NULL, so the expanded chain of inequalities contains a comparison that can never be true."}],"correct":true,"tempting":null},{"text":[{"text":"The two "},{"text":"customer_id","code":true},{"text":" columns have different types, so no value ever matches and the negation excludes everything."}],"correct":false,"tempting":[{"text":"A type mismatch would make nothing match, which for a negation would return "},{"text":"all","i":true},{"text":" rows rather than none."}]}],"order":[3,1,2,0],"why":null},"prev":{"slug":"set-operations","title":"UNION, INTERSECT and EXCEPT","minutes":7},"next":{"slug":"common-table-expressions","title":"CTEs: naming the steps","minutes":7}}}