Try answering this with a single SELECT: 'Which orders are larger than the average order?' You need the average first, and then you need to compare every row against it — two questions, one answer. A subquery lets you nest the first question inside the second, so the database answers both in one statement. By the end of this lesson you'll read and write nested queries the way analysts do every day.
What you'll learn
What
A subquery (also called an inner query or nested query) is a complete SELECT statement placed inside another SQL statement. The inner query runs first and its result feeds the outer query — as a single value, a list of values, or a whole table.
Why
Many real questions are two-step questions: 'above average', 'in the top segment', 'customers who have ever done X'. Without subqueries you would run one query, copy its result by hand, and paste it into a second query — fragile, slow, and impossible to automate.
Where it's used
Subqueries appear in every analytics codebase, every BI tool's generated SQL, and virtually every SQL interview. They are also the conceptual foundation for CTEs, which you'll meet in the next lesson.
Where this runs in production
Marketplace analysts flag orders whose value exceeds the category average — a scalar subquery computes the average while the outer query compares every order against it.
Retention teams select subscribers who have NOT appeared in the viewing-events table in the last 30 days — a classic NOT IN / NOT EXISTS subquery over billions of rows.
Merchant dashboards show 'your store vs stores like yours'. Correlated subqueries compute each merchant's peer-group average revenue on the fly.
Imagine asking a colleague: 'Find every order bigger than our average order.' They can't start until they know the average — so first they compute it (inner errand), write the number on a sticky note, and then walk through the orders comparing each one against the note (outer errand). A subquery is exactly that sticky note: the inner query's answer, held in place for the outer query to use.
Scalar — returns exactly one value (one row, one column), usable anywhere a literal could go: WHERE amount > (SELECT AVG(amount) …). List — returns one column with many rows, used with IN / NOT IN. Table — returns rows and columns, used in the FROM clause as a derived table with an alias.
-- Non-correlated: inner query runs once SELECT order_id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders); -- Correlated: inner query references the outer row (o.customer_id) SELECT o.order_id, o.amount FROM orders o WHERE o.amount > ( SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id );
The first query compares every order to one global average. The second compares each order to that customer's own average — the inner WHERE clause reaches out to the outer alias o. That reach is the correlation.
If a subquery used in a scalar position (after =, >, <) returns more than one row, PostgreSQL raises 'more than one row returned by a subquery used as an expression'. Guard with an aggregate (AVG, MAX), a LIMIT 1, or switch to IN if you genuinely expect a list.
Follow the data: the inner query produces a result, and that result plugs into the outer query. Click each node for detail.
An e-commerce analyst wants every order whose amount beats the store-wide average.
Run the inner question on its own first — always a good habit. AVG(amount) collapses the whole table to a single number: 84.50. One row, one column: a scalar.
Your task
The products table has product_id, name, category, and price. Find every product priced above the average price of its own category. Fill in the blanks: the outer query needs a correlated comparison, and the inner query must scope its average to the outer row's category.
name | category | price -----------------+-----------+-------- Espresso Machine| kitchen | 249.00 Chef Knife | kitchen | 89.00 4K Monitor | tech | 399.00 Mechanical KB | tech | 149.00
Write your solution in the editor on the right, then hit Run.
A subquery used after a comparison operator like `>` must return what shape of result?
What makes a subquery 'correlated'?
The subquery in `WHERE id NOT IN (SELECT user_id FROM events)` returns the values (1, 2, NULL). How many outer rows survive the filter?
Which pattern is the safe, idiomatic choice?
The orders table has order_id, customer_id, and amount. Write a query returning order_id and amount for every order whose amount is greater than the overall average order amount. Sort by amount descending.
How do the raw result sets differ before any deduplication?
When would you choose a subquery over a join, and vice versa?
I choose based on what the output needs and what reads closest to the business question. If I need columns from both tables in the result — say, order details alongside customer names — a join is the natural tool. If the second table is only a filter — 'customers who have ever ordered' — an EXISTS subquery states that intent directly and can never duplicate rows the way a one-to-many join can. Performance-wise, modern optimizers frequently rewrite IN subqueries and joins into the same execution plan, so I don't assume one is faster; I check EXPLAIN when it matters. I also reach for subqueries when I need a computed scalar, like comparing rows against an average, because there's no clean join equivalent. The one pattern I actively avoid is NOT IN against a nullable column, where NOT EXISTS is both safer and usually faster.
Explain the difference between a correlated and a non-correlated subquery, including the performance implications.
A non-correlated subquery is fully self-contained: it references only its own tables, runs once, and its result is reused for every outer row — like computing a global average up front. A correlated subquery references a column from the outer query, such as WHERE customer_id = o.customer_id, so logically it must be re-evaluated for each outer row. That per-row semantics is what enables questions like 'each order versus its own customer's average'. The performance implication is that a naive execution is a nested loop: N outer rows times a scan of the inner table each. Optimizers often decorrelate these into joins or use indexes on the correlated column to make each probe cheap, but on large unindexed tables correlated subqueries are a common cause of slow queries. In interviews I'd add that window functions frequently replace correlated subqueries with a single pass over the data, which is usually the better production answer.
Why does NOT IN behave unexpectedly with NULLs, and what do you use instead?
NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. Any comparison with NULL evaluates to unknown under SQL's three-valued logic, and an AND chain containing unknown can never be true — at best it's unknown, and WHERE only keeps rows that are strictly true. So a single NULL in the subquery's result silently empties the entire outer result set, with no error and no warning. That combination — silent and total — makes it one of the most dangerous traps in analytical SQL. The fix is NOT EXISTS with a correlated equality: it asks 'is there a matching row?' per outer row, and NULLs in the inner column don't match anything, so they don't poison the result. Alternatively you can filter NULLs inside the subquery with WHERE user_id IS NOT NULL, but NOT EXISTS is the habit that never bites.
Common Mistakes to Avoid
1) Using NOT IN against a column that can contain NULL — one NULL silently returns zero rows; use NOT EXISTS. 2) Writing a scalar subquery that can return multiple rows — PostgreSQL errors at runtime, and only when the data triggers it; guard with an aggregate or rethink the shape. 3) Forgetting the alias on a derived table — FROM (SELECT …) needs AS something in PostgreSQL. 4) Reaching for a correlated subquery when a join or window function does the same work in one pass — trace whether the inner query really needs the outer row. 5) Selecting columns in the inner query that the outer position can't use — IN compares against exactly one inner column; SELECT * inside IN is an error waiting to happen.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: what's the difference between a correlated and non-correlated subquery?' • 'Show me the NOT IN NULL trap with a tiny 3-row example I can trace by hand.' • 'Give me a business question that needs a derived table in FROM, and let me try writing it first.' • 'Rewrite this IN subquery as a join and explain when the results would differ.' • 'Interview mode: quiz me on EXISTS vs IN and grade my answers.'
Glossary
Subquery — a complete SELECT nested inside another SQL statement. Inner query — the nested SELECT; runs (conceptually) before the outer query uses its result. Outer query — the enclosing statement that consumes the subquery's result. Scalar subquery — a subquery returning exactly one row and one column. IN — operator testing whether a value appears in a list or subquery result. Correlated subquery — an inner query that references a column from the outer query, re-evaluated per outer row. EXISTS — operator that is true when the subquery returns at least one row. NOT EXISTS — NULL-safe way to express 'no matching row'. Derived table — a subquery in the FROM clause, aliased and treated as a table. Three-valued logic — SQL's true/false/unknown system; comparisons with NULL yield unknown. Decorrelation — an optimizer rewrite turning a correlated subquery into a join.
Recommended Resources
• Docs: PostgreSQL manual, 'Subquery Expressions' (EXISTS, IN, ANY/ALL) — the authoritative reference for edge cases. • Read: 'What's the difference between EXISTS and IN?' threads on the PostgreSQL wiki for real optimizer behavior. • Practice: take any IN subquery you write this week and rewrite it as both a join and an EXISTS — compare row counts. • Next in DSM: CTEs (Common Table Expressions) take the derived-table pattern you saw here and make it readable with named steps.
Recap
✓ A subquery is a full SELECT nested inside another statement — the inner result feeds the outer query. ✓ Result shape decides usage: scalar after comparisons, single column with IN, full table in FROM (with an alias). ✓ Non-correlated subqueries run once; correlated subqueries reference an outer column and run per outer row. ✓ EXISTS tests for row presence and short-circuits; NOT EXISTS is the NULL-safe form of 'never did X'. ✓ NOT IN silently returns zero rows if the inner list contains a NULL — a trap worth memorizing. ✓ Derived tables let you aggregate an aggregation, like averaging per-customer totals. Next up: CTEs (Common Table Expressions). Deeply nested subqueries are hard to read — WITH lets you name each step and build multi-stage analyses that read top-to-bottom.
Run your code to see the output here.