Derived Tables

A subquery in FROM acts as a table for one statement; the alias is mandatory, and a CTE often reads better.

SyntaxSELECT ... FROM (SELECT ...) [AS] alias [(col_list)] ...

A derived table is a subquery in the FROM clause. The rest of the statement treats its result as an ordinary table: you can join to it, filter it, group it. It exists for the length of one statement and nothing else can see it. The usual reason to use one is to aggregate or rank first and then filter or join the summarised result.

The alias is not optional

Every table in a FROM clause must have a name, so a derived table must be aliased. Without one you get ERROR 1248 (42000): Every derived table must have its own alias. AS itself is the optional part — these are the same:

FROM (SELECT ...) AS dt   -- longhand
FROM (SELECT ...) dt      -- shorthand, identical

You can also name the columns positionally, which is longhand for aliasing each expression inside: FROM (SELECT 1, 2) AS dt (a, b) is the same as FROM (SELECT 1 AS a, 2 AS b) AS dt.

Merged or materialised

By default MySQL 8.4 merges a derived table into the outer query block — the derived_merge flag is on. Merging is impossible if the subquery uses an aggregate function, DISTINCT, GROUP BY, HAVING, LIMIT, UNION, or a subquery in its select list; then it is materialised into an internal temporary table. The MERGE() and NO_MERGE() optimizer hints override the choice.

When a CTE reads better

A WITH clause expresses the same thing, named before it is used instead of inlined mid-statement. Prefer it when the nesting is more than one level deep, or when you need the same result twice: a CTE referenced twice is materialised once.

Example

Example · sql
-- Customers whose lifetime paid spend is over 1000, with the figure shown.
-- The aggregate has to happen before it can be filtered and joined, so it goes
-- in a derived table. GROUP BY here forces materialisation rather than merging.
SELECT c.id, c.name, spend.order_count, spend.lifetime_total
FROM customers AS c
JOIN (
        SELECT o.customer_id,
               COUNT(*)         AS order_count,
               SUM(o.total)     AS lifetime_total
        FROM orders AS o
        WHERE o.status = 'paid'
        GROUP BY o.customer_id
     ) AS spend                       -- alias is mandatory: ERROR 1248 without it
  ON spend.customer_id = c.id
WHERE spend.lifetime_total > 1000
ORDER BY spend.lifetime_total DESC;

When to use it

  • Aggregate a child table first, then join the totals back to the parent.
  • Filter on a computed column that WHERE cannot see, such as a window function result.
  • Apply ORDER BY and LIMIT inside a subquery that IN would otherwise reject.
  • Pre-reduce a large table before joining it, so the join works on fewer rows.

More examples

Derived table rewritten as a CTE

Use this as the default refactor once a query grows past one derived table. The CTE reads top-to-bottom in the order the work happens, and a name declared once can be referenced twice: if you needed to join 'spend' to itself, the derived table version would have to repeat the whole subquery, while the CTE is materialised once.

Example · sql
-- Derived table: the definition sits in the middle of the statement.
SELECT c.name, spend.lifetime_total
FROM customers AS c
JOIN (SELECT customer_id, SUM(total) AS lifetime_total
        FROM orders WHERE status = 'paid' GROUP BY customer_id) AS spend
  ON spend.customer_id = c.id;

-- CTE: the same relation, named first. Identical result and, for a single
-- reference, typically the same plan.
WITH spend AS (
    SELECT customer_id, SUM(total) AS lifetime_total
    FROM orders WHERE status = 'paid' GROUP BY customer_id
)
SELECT c.name, spend.lifetime_total
FROM customers AS c
JOIN spend ON spend.customer_id = c.id;

Naming columns on the alias

Column aliasing on the derived table is worth knowing because expression columns otherwise inherit unusable names such as SUM(total). It is especially handy over a UNION, where the column names come from the first branch only. The number of names must equal the number of columns, or the statement fails.

Example · sql
-- Columns named on the derived table alias. The count must match exactly.
SELECT dt.a, dt.b
FROM (SELECT 1, 2) AS dt (a, b);

-- Longhand: the same names given as select-list aliases instead.
SELECT dt.a, dt.b
FROM (SELECT 1 AS a, 2 AS b) AS dt;

-- Where the alias list earns its place: renaming the output of a UNION
-- without editing either branch.
SELECT src.channel, src.amount
FROM (
      SELECT 'web',   SUM(total) FROM orders WHERE source = 'web'
      UNION ALL
      SELECT 'store', SUM(total) FROM orders WHERE source = 'store'
     ) AS src (channel, amount);

Filtering on a window function result

This is the canonical top-N-per-group pattern and the most frequent legitimate use of a derived table. The inner query does no filtering at all; its only job is to make the ranking a real column. Note that the ROW_NUMBER() expression must be repeated in the ORDER BY of the window because the select-list alias line_total is not available inside the same select list.

Example · sql
-- Top 3 order lines by value within each order.
-- ROW_NUMBER() cannot be used in WHERE, because window functions are evaluated
-- after WHERE. The derived table gives the filter something to see.
SELECT order_id, product_id, line_total
FROM (
    SELECT oi.order_id,
           oi.product_id,
           oi.qty * oi.unit_price AS line_total,
           ROW_NUMBER() OVER (PARTITION BY oi.order_id
                              ORDER BY oi.qty * oi.unit_price DESC) AS rn
    FROM order_items AS oi
) AS ranked
WHERE rn <= 3
ORDER BY order_id, rn;

Related

sql withsql cte readabilitysql subquery vs joinsql group bysql correlated

Discussion

  • Be the first to comment on this lesson.