The previous lesson ended with a list of questions you couldn't answer yet, and they all had the same shape: comparing each row with the result of another query. Which products are above the catalogue's average price? Which customers have an average order value above the overall average —the question 04-06 explicitly set aside for this lesson? Which orders include the most expensive product?

The answer to all of them is the same tool: a subquery, a query written in parentheses inside another one. Here you'll learn the vocabulary, the three types of subquery according to what they return, how they're used with IN, ANY and ALL, and the three classic mistakes that produce anything from a red message to —far worse— an empty result with no warning at all.

1. What a subquery is: vocabulary, correlation and types
2. Scalar subquery in the WHERE
3. The question left over from 04-06: the average order value
4. List subqueries: IN, ANY/SOME and ALL
5. The three classic mistakes
6. Where a subquery can appear
7. Common Mistakes and Tips
8. Exercises
9. Conclusion

A subquery (or nested query) is a complete SELECT statement, written in parentheses, that appears inside another SQL statement. The engine runs it and uses its result as if it were a value, a list or a table.

The query containing the other one is the outer query; the one inside, the subquery. A subquery can contain another, with no limit other than readability. Three syntax rules with no exceptions: the parentheses are mandatory; the subquery is written in full (SELECT, FROM, WHERE, GROUP BY… whatever it needs); and an ORDER BY inside a subquery is almost always useless, unless it comes with a LIMIT.

This is the distinction that structures the whole module, and it's worth fixing before any other.

The practical test is infallible: select the subquery, run it on its own and see what happens. The first returns 9.0350000000000000. The second gives ERROR: missing FROM-clause entry for table "p", because p doesn't exist outside the outer query. This entire lesson is about the non-correlated ones.

The second classification, cutting across the previous one, refers to the shape of the result, and it determines where the subquery can appear and which operators it combines with.

Scalar and table subqueries cover 99 % of the SQL you'll write. Row subqueries are elegant but rare: WHERE (price, stock) = (SELECT MAX(price), 40 FROM products) returns a single row, the matcha (€22.00 and 40 units).

Dialect note: row constructors (a, b) = (...) work in PostgreSQL, MySQL and MariaDB; SQLite and SQL Server don't support them and require two conditions joined with AND.

The most common use case of them all: comparing each row with a value computed over the whole set. The question is "which products are above the catalogue's average price?", and the naive attempt is this one:

It's the same error as in 04-06 and for the same reason: when the WHERE runs (step 2 of the logical order) the engine looks at one row at a time and hasn't computed any aggregate. The subquery solves it because it's another query, with its own complete pass over the table:

9 rows: 9 products out of 20 beat the €9.035 average price (SELECT ROUND(AVG(price), 4) FROM products → 9.0350, the figure from 04-04). What matters is how it runs: PostgreSQL evaluates the subquery only once, gets 9.0350000000000000 and replaces the expression with that number; from there on the outer query is a plain WHERE price > 9.0350000000000000. There's no loop: it's a constant computed on the fly. And watch a detail that matters: the comparison is done with the unrounded value — with other data, a cent decides whether a row is in or out. Never round the comparison value; round only what you display.

In 04-06 you computed each customer's average order value and confirmed that the overall average of the 20 orders is €36.40, but you couldn't put the two things together: HAVING knows how to compare a group's aggregate with a constant or with another aggregate of the same group, never with an aggregate computed over a different set of rows. A scalar subquery in the HAVING is the piece that was missing.

First, the reference value. The overall average isn't the average of the 47 lines (that would give the €15.49 average line amount), but the average of the 20 orders:

And now the same expression, injected into the HAVING:

Three customers out of twelve, exactly the ones 04-06 anticipated. Step by step:

1. FROM + two JOINs: it starts from the 47 detail lines and climbs up to the customer; it's the course's canonical detail query. The GROUP BY forms 12 groups, one per buying customer (customers 13, 14 and 15 were already discarded by the INNER JOIN).
2. The scalar subquery is evaluated once and returns 36.39725, the exact unrounded average.
3. HAVING compares each group's average order value with that constant and discards nine groups.
4. SELECT projects and rounds. The rounding is presentation only: the comparison was already made at full precision.

And a business reading: all three are foreign customers, no Spaniard beats the average (the closest is Lucía, with €35.87). Shipping to Portugal and France costs €9.90 and €12.50 against the €4.95 domestic rate, and the customer compensates by placing bigger orders. That said, two of the three have a single order: the genuinely solid case is Sofia, with two orders of €64.88 and €47.00.

Why it goes in the HAVING and not in the WHERE: the condition compares a group aggregate with the constant, and the WHERE can't use aggregates. The subquery only supplies the number to compare against.

When the subquery returns several rows of a single column, the natural way to use it is IN. You already know the operator from 04-02 with a hand-written list; now the list is computed by another query.

(First 3 of 12 rows: customers 1 to 12, the 12 buyers.) There's a capital detail here that we'll come back to in 07-05: the subquery returns 20 values (one per order, with repetitions), but the result has 12 rows. IN doesn't multiply: it asks whether the value is in the list and answers yes or no exactly once per outer row. An INNER JOIN with orders would have returned 20 rows.

A second example, with an aggregate subquery: products from the categories that have more than three products.

17 products out of 20. The categories with more than three references are Food (5), Natural cosmetics (4), Sustainable home (4) and Drinks (4); left out are Personal hygiene (products 18 and 19) and Supplements (number 20). The subquery is a complete aggregate query, with its GROUP BY and its HAVING: you can compute a set of keys with the whole machinery of module 4 and use it as a filter.

The equivalence with = ANY. The standard defines IN as syntactic sugar for = ANY: c.id IN (sub) and c.id = ANY (sub) produce the same plan and the same result. IN reads better and is what you'll see in real code; = ANY matters because it explains the whole family:

SOME is an exact synonym of ANY that nobody uses. The form that really shows up is > ALL, and its natural question is "more expensive than any of the ones in that category":

5 products cost more than all the Food ones, whose ceiling is the olive oil (€12.50). Writing > (SELECT MAX(price) FROM products WHERE category_id = 1) returns the same thing and reads better. With ANY instead of ALL the condition would be "more expensive than the cheapest Food product" (€1.95) and 19 products would come out.

Careful with empty sets. If the subquery returns no rows, > ALL is true for every row (there's no counterexample) and > ANY is false for every row (there's no favourable case). Flawless as logic and baffling in practice: price > ALL (SELECT price FROM products WHERE category_id = 99) returns all 20 products.

Food has five products, the subquery returns five prices and > doesn't know which one to compare against. There are three fixes and each one answers a different question: > (SELECT MAX(price) ...) and > ALL (...) say "more expensive than the most expensive one"; > ANY (...) says "more expensive than at least one". It's the least dangerous of the three because it's noisy, and it's a data error, not a syntax one: the same query would work if the category had a single product and would blow up the moment the second one was added. If the subquery returns several columns, the message is ERROR: subquery must return only one column.

No message, no warning. Category 99 doesn't exist, the subquery finds no rows, AVG over an empty set returns NULL (04-04) and price > NULL evaluates to UNKNOWN for all twenty rows. Since the WHERE only lets through what's TRUE (04-03), the result empties out silently. This is the most dangerous of the three, because an empty report looks like a legitimate report: "there weren't any this month". How to defend yourself: always run the subquery on its own before nesting it; wrap it in COALESCE when a sensible default exists (> COALESCE((SELECT AVG(...)...), 0), 06-04); and if the question is about existence, use EXISTS, which never returns NULL (07-03).

In 04-02 you called it "the most expensive mistake in SQL". With subqueries it's far easier to make, because you no longer see the list: another query computes it and you don't know whether it brings nulls along.

And yet you know there are five employees with no orders: only 4, 5 and 6 appear in orders. What's happened is that the subquery returns {4, 5, 6, NULL} — the 10 web orders have employee_id set to NULL. For employee 1:

The same for all eight rows. With a single NULL in the list, NOT IN can never return TRUE. The three fixes —filtering the nulls inside, NOT EXISTS (07-03) and 03-03's anti-join— are these:

All three return the same thing:

5 rows. And there's an asymmetry that surprises everybody: IN does work with nulls. 4 IN (4, 5, 6, NULL) is TRUE, because it's enough for one comparison to hit. The problem is exclusive to the negation. The comparison of the four ways of answering "what doesn't match" arrives in 07-03, and its definitive table in 07-05.

Almost anywhere a value or a table fits:

Two examples from module 5 revisited, now that you know what they're called: UPDATE products SET price = ROUND(price * 1.05, 2) WHERE id NOT IN (SELECT product_id FROM order_lines) raises by 5 % whatever has never been sold, and DELETE FROM reviews WHERE customer_id NOT IN (SELECT id FROM customers) cleans up orphan reviews. Both are safe because order_lines.product_id and customers.id are NOT NULL. If either allowed nulls, the UPDATE would modify nothing and the DELETE would delete nothing — 5.3's trap, now with consequences for the data. And where a subquery can't appear: in the GROUP BY, nor —in PostgreSQL— inside a CHECK constraint.

• Writing an aggregate in the WHERE. aggregate functions are not allowed in WHERE. What you need is a scalar subquery.
• Using a multi-row subquery where a value is expected. more than one row returned by a subquery used as an expression. Add an aggregate, an ORDER BY ... LIMIT 1, or switch to IN/ANY/ALL.
• Not checking that the scalar returns something. If it returns zero rows it's worth NULL, the filter empties out silently and the report looks correct. It's the most expensive mistake in the lesson.
• NOT IN over a nullable column. Zero rows, always. Use NOT EXISTS or filter the nulls inside the subquery.
• Rounding the comparison value. > ROUND(AVG(price), 2) isn't > AVG(price). Round when displaying, not when comparing. And avoid the ORDER BY inside a list subquery: it adds nothing and costs time.
• Tip: always run the subquery on its own first. It's the module's number-one debugging technique; if it can't run on its own, it's correlated.
• Tip: if you need columns from the other table in the result, it isn't a subquery: it's a JOIN. The subquery filters and computes; it contributes no columns to the outer SELECT (07-05).

Marketing wants a list of expensive products measured against a specific yardstick: the ones above the average price of the Natural cosmetics category. Show id, name, category and price, sorted by price descending, and don't write the threshold by hand. Then answer: how many of the products that come out are from Natural cosmetics, and why don't the other two from that category appear?

A colleague wants to know which customers have never written a review and has written this:

1. Does it work? Justify your answer by looking at 01-06's schema, without running it.
2. Give the result and say how many of those customers have bought at some point.
3. Rewrite it with NOT EXISTS and with an anti-join, and explain why all three are equivalent here.

Management asks: "which orders include the catalogue's most expensive product?". Write a single query returning the order's id, date, customer and status, without writing either the name or the id of that product by hand. (Hint: you'll need two subqueries, one inside the other.) Then say what would happen if two products were tied at the maximum price.

Solution 1

6 rows. The computed threshold is €11.5375 (€46.15 across 4 products). Only two are from Natural cosmetics, the aloe cream and the body oil; the other two in the category —shampoo €8.40 and lip balm €4.60— fall below their own average, which is pulled upwards precisely by the two expensive ones.

The underlying point: the threshold comes from one category but is applied to the whole catalogue, because a non-correlated subquery is evaluated once and holds equally for all twenty rows. Comparing each product with the average of its own category demands a correlated one: next lesson.

Solution 2

1. Yes it works, and you can tell without running it: reviews.customer_id is declared NOT NULL (01-06, table 3.8), so the subquery can't return any NULL and NOT IN behaves correctly. The suspicion was the right reflex in the face of any NOT IN; the schema clears it up. 2. The result:

6 customers out of 15, and three of them have bought: Ana (2 orders), Julien (1) and Diego (1). The other three are the familiar 13, 14 and 15, who have never bought and therefore couldn't review anything. Telling the two groups apart matters: Ana, Julien and Diego can be asked for an opinion; Núria, Hugo and Inés have to be sold something first.

3. The two rewrites, with the same six-row result:

All three are equivalent here for a single reason: reviews.customer_id is NOT NULL. If anonymous reviews with a null customer_id were allowed tomorrow, the NOT IN version would start returning zero rows and the other two would carry on working. The equivalence depends on the schema, not on the syntax.

Solution 3

3 orders. The most expensive product is the Ceremonial matcha green tea 30 g (€22.00) and it has been sold three times. There are two levels: the inner subquery returns a scalar value (the id of the most expensive product) and the middle one, a list of order_id.

If there were a tie, the inner one would still return a single row —LIMIT 1 cuts arbitrarily— and you'd silently lose the orders for the other product. The robust version swaps the = for an IN and drops the LIMIT: WHERE ol.product_id IN (SELECT id FROM products WHERE price = (SELECT MAX(price) FROM products)). With today's data it returns the same thing, but it doesn't break the day somebody adds a second product at €22.00. ORDER BY ... LIMIT 1 inside a subquery is convenient and fragile.

You've opened the module's door:

• A subquery is a query in parentheses inside another one; the one containing it is the outer query. According to what they return there are three types —scalar, row and table— and each admits different operators.
• The distinction that organises the module is non-correlated (mentions nothing from outside, runs on its own, is evaluated once) against correlated (uses an outer alias, can't be run in isolation, is evaluated once per row).
• A scalar in the WHERE solves "above the average": 9 of the 20 products beat the catalogue's €9.035. And a scalar in the HAVING closes the question 04-06 left pending: Julien, Sofia and Tiago are the only ones with an average order value above the €36.40 overall average.
• IN (SELECT ...) filters by a computed list —12 buying customers, 17 products from categories with more than three references— and is equivalent to = ANY; > ALL compares against the maximum and > ANY against the minimum. And you know the three classic mistakes: the scalar with several rows (noisy), the scalar with zero rows (silent: it gives NULL and empties the result) and NOT IN with nulls (zero rows guaranteed).

All the subqueries in this lesson have something in common: they're computed once and are worth the same for every row. That's why none of them could answer "which products beat the average of their category": that threshold is different for each row. In the next lesson, correlated subqueries, the subquery will start looking outwards —at the row the outer query is examining at that moment— and will run once for each of them. The mental model changes, the cost changes and a whole family of new questions appears.