What Are Derived Tables?

A derived table is a SELECT statement in the FROM clause, given a name with an alias. The engine treats it as a table for the rest of the query: you can select from it, join to it, group by its columns and reference its column aliases in the outer query.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 2,  15.00);

SELECT t.customer_id, t.order_count, t.total
FROM (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS t
WHERE t.order_count > 1
ORDER BY t.customer_id;

One row comes back: customer 1 with 2 orders totalling 460.50. Customer 2 has one order, so the outer filter removes that group.

The alias is mandatory — a derived table without one is a syntax error in every engine — and it is what the outer query refers to. The derived table's own aliases, order_count and total, become ordinary column names in the outer query.

What the derived table buys you is a staging point. Because the outer query sees a flat set of columns, you can filter on the aggregate, join the result to something else, and group by a computed value. The biggest of these is grouping by an expression: the outer query cannot GROUP BY a CASE expression directly in most engines without repeating it, but it can group by a column that a derived table computed for it.

One important difference from a CTE. A derived table is scoped to the single query it appears in and cannot be referenced twice. A CTE can be referenced multiple times, which is why the CTE is the better tool when you need the same roll-up in two places. A derived table is the better tool when you want the optimizer to treat the intermediate result as part of the join tree, because the planner is free to push predicates into it.

The rule is that a GROUP BY can only group by a column or by an expression you are willing to repeat verbatim. A derived table computes the expression once, names it, and lets the outer query group by the name.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1,  90.00),
  (3, 2, 940.00),
  (4, 2,  75.25),
  (5, 3, 510.00);

SELECT band,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS band_total
FROM (
  SELECT id,
         amount,
         CASE
           WHEN amount >= 500 THEN 'large'
           WHEN amount >= 100 THEN 'medium'
           ELSE 'small'
         END AS band
  FROM orders
) AS banded
GROUP BY band
ORDER BY band_total DESC;

Three rows come back: the large band with one order of 940.00, the medium band with three orders totalling 720.00, and the small band with one order of 75.25.

Without the derived table you would have to repeat the whole CASE expression in the GROUP BY, or group by an ordinal position — GROUP BY 1 — which works in SQLite and MySQL but is not standard and breaks the moment someone reorders the select list. Both are worse than naming the value.

The derived table is also the right place to do the arithmetic, because the outer query can then treat the result as ordinary data. Here you could add an average per band, a share-of-total percentage, or a ranking of the bands by revenue, all of which would otherwise mean repeating the CASE expression several times.

One caveat about pushdown. Because the outer query can be merged with the derived table by the optimizer, a filter on the outer side may end up inside the CASE, changing the meaning. Concretely: if you add WHERE banded.amount > 100 to the outer query, the optimizer may push that filter below the CASE, so rows that would have landed in the 'small' band are removed before the banding happens, and the band totals change. When that matters — and it usually does for a report — the fix is to prevent the merge with an optimisation barrier, which in SQLite means adding a LIMIT clause with a large value or using a CTE with MATERIALIZED on PostgreSQL.