The three previous lessons answered the what: what a subquery returns, whether it's correlated, whether it asks about existence. This one answers the where. Because the same subquery placed in the SELECT, in the FROM or in the WHERE doesn't do the same thing, doesn't cost the same and doesn't allow the same things.

And there's a clause you haven't used yet which is, by some distance, the most powerful: the FROM. A subquery there is called a derived table and it allows something no other construct in the course allowed: aggregating an already aggregated result. With it you'll finally compute the €36.40 average order value from its source, you'll cross two aggregates of different granularity so that the €727.95 of product and the €118.25 of shipping add up without inflating each other —the problem you've been carrying since module 3— and you'll meet LATERAL, the exception that lets a derived table be correlated.

1. Summary table: what changes depending on where you put it
2. In the SELECT: the computed column
3. In the FROM: the derived table
4. LATERAL: the derived table that can be correlated
5. In the WHERE and in the HAVING
6. In INSERT, UPDATE and DELETE
7. Decision table: given a problem, where to put it
8. Common Mistakes and Tips
9. Exercises
10. Conclusion

Three rules follow from the table: only a value fits in the SELECT (two rows or two columns and the query blows up); in the FROM the alias is mandatory, always, even if you don't use it; and a derived table doesn't see the other tables of its own FROM, which is the restriction LATERAL lifts.

A scalar subquery in the SELECT behaves like just another column. You already used it in 07-02: what matters here is its limit and its alternative.

(First 4 of 15 rows.) The three customers with no orders close the list with 0 and 0.00, thanks to 06-04's COALESCE.

The three properties that define this use: it can only return one value (if you need the total and the number of orders, two subqueries are needed, and each walks orders on its own); it doesn't filter rows, all 15 customers are still there, behaving like a LEFT JOIN without being one; and it runs once per row: two subqueries × 15 customers = 30 runs.

Identical result, the same 15 rows with the same values. But the work is very different:

Look at the second-to-last row, which is the important nuance: the JOIN version needs COUNT(DISTINCT o.id) because the second LEFT JOIN multiplies each order's rows by its lines. It's exactly 04-04's problem, and the subquery version is immune to it. Neither form is always better: with one or two columns the subquery reads better; from three onwards, the GROUP BY wins hands down. The full discussion is 07-05.

A subquery in the FROM produces a derived table: an intermediate result the outer query treats as a real table. It's the clause that unlocks the most useful pattern in analysis: aggregating twice.

Every derived table needs a name, even if you don't use it: its columns have to be qualifiable (t.total), and without a table name that's impossible.

Dialect note: PostgreSQL, MySQL, MariaDB and SQL Server require the alias; SQLite and Oracle let you omit it. Always write it: it's portable and it makes the query readable.

In 07-01 you computed the average order value as SUM(amount) / COUNT(DISTINCT order_id). That's correct, but it's a shortcut: the honest formulation is to compute each order's total and then average those totals. Those are two chained aggregations, and only a derived table allows them.

There are the course's canonical figures, and now by construction: 20 orders, an average of €36.40, revenue of €727.95; the cheapest order is Camille's number 20 and the most expensive Julien's number 12.

What makes the result possible is that the derived table changes the granularity: 47 lines go in and 20 orders come out, and the outer query aggregates over those 20. Without it, AVG over the lines would give the €15.49 average line amount, which is a different thing.

Once you have the derived table, you filter it like any other table:

(First 4 of 6 rows; Tiago's order 9, €44.60, and Lucía's order 1, €42.10, close the list.) 6 orders out of 20 beat the average order value. The filter is in the outer query's WHERE, not in a HAVING: as far as it's concerned, t.total is a normal column. A derived table turns aggregates into ordinary columns, and that simplifies the writing enormously.

This is the case the course has had pending since module 3. Shipping costs live in orders (one per order) and amounts in order_lines (several per order): adding them up in the same query with a JOIN gave €278.70 of shipping instead of €118.25, because each order was counted as many times as it had lines. The solution is to aggregate each thing separately and join the already aggregated results:

(First 3 of 12 rows.) And the columns add up: summed across the 12 rows they give €727.95 of product + €118.25 of shipping = €846.20, the course's three canonical figures. Not a cent inflated.

The key is that each derived table already arrives at customer granularity —ord has 12 rows and so does lin—, so when they're joined each order contributes its shipping only once: the SUM was done before the JOIN. The global version fits in three lines:

From three levels onwards, use CTEs. Two derived tables can still be read; with three, or with a derived table inside another inside another, the indentation eats the screen. The solution is to give each step a name with WITH, and that's 10-02: everything in this section gets rewritten there with half the parentheses.

You already know that a derived table doesn't see the other tables of its own FROM:

The keyword LATERAL lifts that restriction: it tells the engine "evaluate this subquery once for every row of what's to its left". It's a for loop over the previous table, and it combines with CROSS JOIN LATERAL or with LEFT JOIN LATERAL ... ON TRUE. Its natural case is top-N per group:

(6 of the 9 rows.) The first four categories contribute their two best-selling products; Personal hygiene has only one sold (the toothbrush, because the deodorant was never sold) and contributes one row. And Supplements doesn't appear, because its subquery returns zero rows and CROSS JOIN LATERAL behaves like an INNER JOIN; to see it with NULLs you use LEFT JOIN LATERAL (...) AS top ON TRUE, which gives 10 rows. That ON TRUE isn't decoration: the syntax requires a join condition and the correlation is already inside, so there's nothing left to put.

Dialect note: LATERAL is standard SQL:1999 and works in PostgreSQL 9.3+, MySQL 8.0.14+ and Oracle 12c+. In SQL Server the equivalent is called CROSS APPLY (and OUTER APPLY for the version with NULLs), with the same semantics and without the word LATERAL. SQLite doesn't support it. And for this same problem there's a third route, often better: ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY units DESC) filtering by <= 2, which is 10-03.

This is 07-01's and 07-03's territory: in the WHERE you can put scalar subqueries (> (SELECT AVG(...))), list ones (IN, ANY, ALL) and existence ones (EXISTS), correlated or not; and if you need columns from the inner table in the result, that isn't a WHERE, it's a JOIN. The HAVING deserves an example of its own, because its subquery compares a group's aggregate with a value computed over a different set:

3 categories out of the 5 with sales beat the average revenue per category, which is €145.59 (€727.945 across the 5 categories that have sold anything). Sustainable home (€88.58) and Personal hygiene (€31.50) fall below, and Supplements doesn't even reach the GROUP BY because the INNER JOIN discarded it.

Look at the divisor: COUNT(DISTINCT p2.category_id) counts 5, not 6. To divide by the catalogue's six categories, the divisor would have to come from categories, not from the sales. The average depends on what you compute it over.

Module 5 taught you the three statements; now you can give them a subquery in any of their clauses.

The second one hides the lesson's most dangerous trap. Without the final WHERE EXISTS, the three products never sold would receive the result of a subquery with no rows —that is, NULL— and price is NOT NULL: the statement would fail outright with null value in column "price" violates not-null constraint. If the column allowed nulls it would be worse: it would be left as NULL without saying a thing. The 05-03 rule is extended: in an UPDATE with a subquery in the SET, the WHERE must guarantee that the subquery finds something.

• Forgetting a derived table's alias. subquery in FROM must have an alias. Always put it, even if you don't use it. And referencing another table of the same FROM from a derived table gives invalid reference to FROM-clause entry: that's what LATERAL is for.
• Putting a multi-row subquery in the SELECT. more than one row returned by a subquery used as an expression: only one value fits there.
• Summing shipping_cost after joining with order_lines. It's 04-04's mistake —€278.70 instead of €118.25—: aggregate each thing separately in two derived tables.
• Confusing the average order value (€36.40, the average of 20 orders) with the average line amount (€15.49, the average of 47). Only the derived table gives the first. And counting with COUNT(*) after several LEFT JOINs inflates: each order appears as many times as it has lines, so COUNT(DISTINCT o.id).
• An UPDATE with a subquery in the SET and no WHERE narrowing it. The rows with no match receive NULL: either the constraint blows up, or the data is silently corrupted.
• Tip: build derived tables from the inside outwards. Write the subquery on its own, run it, check how many rows and what granularity it has, and only then wrap it: a derived table can always be run in isolation (except with LATERAL).
• Tip: name it after what it contains, not t1 and t2: ord, lin, sales_per_customer. And count the rows at each level —47 lines → 20 orders → 1 row—: if a level doesn't reduce things the way you expected, the error is there and not in the one above.

Management wants the average order value by country: for each country, the number of orders, the product revenue and the average order value (the average of its orders' totals). Use a derived table. Then answer: would the result be different if you computed SUM(amount) / COUNT(DISTINCT order_id) with no derived table?

A colleague wants the report "per customer: orders, lines, units, distinct products and revenue" and has started like this:

1. How many subquery runs does it involve as it stands, and how many if they add the two missing columns?
2. Rewrite it with a LEFT JOIN and GROUP BY, taking care with the order count.
3. What difference will there be in the result for customers 13, 14 and 15?

Marketing wants, for each customer who has bought, their most expensive order: order id, date and amount. Write it with LATERAL and explain why an ordinary derived table wouldn't do.

Solution 1

3 + 3 + 14 = 20 orders and 156.48 + 137.77 + 433.70 = €727.95: it squares with the canonical figures. And the reading confirms what you already saw in 07-01: foreign orders are appreciably bigger —€52.16 and €45.92 against €30.98—, consistent with shipping costs of €9.90 and €12.50 that push customers to bundle their purchases.

On the question: here the result would be the same, because AVG of the per-order totals and SUM(amount) / COUNT(DISTINCT order_id) are arithmetically identical when each order belongs to a single country. The derived table wins anyway for two reasons: it reads far better —it literally says "the average of the orders' totals"— and it lets you compute what the shortcut can't, such as MIN(t.total), MAX(t.total) or the median.

Solution 2

1. The runs. Three subqueries × 15 customers = 45; with the two missing columns, 75. And all five walk the same two tables with the same filter: five times the same work. 2. The rewrite:

(3 of the 15 rows, as a sample.) COUNT(DISTINCT o.id) is mandatory: with a plain COUNT(o.id), Lucía would have 9 orders instead of 3, one per line. It's exactly 04-04's trap. COUNT(ol.id), on the other hand, does go without DISTINCT, because each line is unique.

3. Customers 13, 14 and 15 appear in both versions with the same values: 0 where there's a COUNT and 0.00 where the COALESCE covers SUM's NULL. Neither the subquery in the SELECT nor the LEFT JOIN removes them; what would have removed them is an INNER JOIN, and that's why the LEFT isn't negotiable here.

Solution 3

(First 3 of 12 rows.) Customers 13, 14 and 15 don't appear: their lateral subquery returns zero rows and CROSS JOIN LATERAL discards them, which is exactly what the question asked for. With LEFT JOIN LATERAL ... ON TRUE all 15 rows would come out, with NULLs in the last three.

Why an ordinary derived table won't do: we'd need to write WHERE o.customer_id = c.id inside it, and an ordinary derived table doesn't see c (invalid reference to FROM-clause entry for table "c"). You could work around it by aggregating per customer and joining on MAX(total), but that forces a second JOIN to recover the winning order's id and date, and it duplicates rows if there's a tie. LATERAL solves it in one pass because the LIMIT 1 is applied per customer, and no other construct in the module can do that. The modern alternative is ROW_NUMBER() (10-03).

The where matters as much as the what:

• In the SELECT, a scalar subquery is a computed column: it doesn't filter rows and it runs once per row. With one or two columns it reads very well; from three onwards LEFT JOIN + GROUP BY wins, taking care with the COUNT(DISTINCT).
• In the FROM, a subquery is a derived table and needs an alias without exception (subquery in FROM must have an alias). It's the only way to aggregate over an aggregate: 47 lines → 20 orders → an average order value of €36.40, with a minimum of €22.60 and a maximum of €66.90.
• Two derived tables of different granularity solve the shipping problem you'd been dragging along since module 3: €727.95 of product + €118.25 of shipping = €846.20, with nothing inflated, because each SUM is done before the JOIN.
• LATERAL is the exception that lets a derived table be correlated, and its natural case is top N per group: the two best-selling products of each category, with the LIMIT applied per category. In SQL Server it's called CROSS APPLY; in SQLite it doesn't exist; and for rankings a window function is usually better (10-03).
• In WHERE and HAVING everything from 07-01 and 07-03 applies; in an UPDATE, a subquery in the SET demands a WHERE guaranteeing it finds something, or you'll write nulls. And from three levels onwards readability demands CTEs (10-02).

You now have all the pieces: scalar subqueries, list subqueries, correlated ones, EXISTS, derived tables and LATERAL. And with them, a new problem: almost every question now admits two or three different forms, and none of them tells you which to choose. In the module's last lesson, subquery or JOIN: which one to choose, you'll see the four great equivalences head to head, the real difference between IN and an INNER JOIN —which isn't one of style, but of row count—, the definitive table of the four ways of answering "what doesn't match" and the honest criterion for deciding: readability first, measured performance afterwards.