Derived tables

Subqueries in FROM, and the pre-aggregation pattern.

What you'll be able to do

Write a subquery in the FROM clause
Pre-aggregate to avoid a fan-out
Alias derived tables correctly

Subquery as a table

SELECT region, AVG(order_total) AS avg_order
FROM (
  SELECT region, order_id, SUM(line_total) AS order_total
  FROM order_lines
  GROUP BY region, order_id
) AS per_order
GROUP BY region;

A derived table lets you aggregate twice — first to order level, then to region. Most dialects require an alias on it, even if you never reference it.

The pre-aggregation pattern

This is the standard fix for a join fan-out:

SELECT o.order_id, o.total, li.item_count
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM line_items GROUP BY order_id
) li ON li.order_id = o.order_id;

The derived table has exactly one row per order, so nothing multiplies and o.total stays correct.

Readability

Nested derived tables get unreadable quickly — the logic runs inside-out while you read top-down. Two levels is usually the point to switch to CTEs, which express the same thing as named steps.

Key points

A derived table is a subquery in FROM and usually needs an alias
Pre-aggregating in a derived table prevents join fan-out
Beyond two levels of nesting, use CTEs instead

Check yourself