📦Derived tables

Inline relations in the FROM clause

A derived table is a parenthesized SELECT in the FROM list, always with an alias: FROM (SELECT …) AS alias. It behaves like a view that exists only for the duration of the statement. The outer query may reference only columns projected by the inner SELECT: name them deliberately; never rely on SELECT * in library SQL that other migrations extend.

A WITH common table expression (CTE) is syntactic sugar for one or more named derived tables, usually improving readability for multi-step pipelines. Semantics are the same relational algebra; optimizer treatment (merge vs materialize) is engine-specific, benchmark hot paths instead of guessing.

SQL

Inline derived table: narrow before aggregate

SELECT lane, AVG(bp) AS avg_bp
FROM (
  SELECT lane, read_length_bp AS bp
  FROM sequencing_reads
  WHERE run_id = 'R-900'
) r
GROUP BY lane;

✓Illustrative result set

lane	avg_bp
1	148
2	151.2

SQL

CTE pipeline: daily counts then rolling average

WITH daily AS (
  SELECT run_date, COUNT(*) AS runs
  FROM lab_runs
  GROUP BY run_date
)
SELECT run_date, runs,
       AVG(runs) OVER (ORDER BY run_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS runs_7d_avg
FROM daily;

✓Illustrative result set

run_date	runs	runs_7d_avg
2026-04-05	12	10.2
2026-04-06	9	10.8

Same heavy derived table referenced twice

Some optimizers duplicate work when the same subquery appears twice; others inline both copies into a larger tree. Large sorts or window passes twice hurt latency.

Materialize once: temp table, CTE consumed once, or refactor

Promote to a TEMP TABLE with indexes for exploratory analytics, or chain CTEs so the expensive stage is named once and referenced downstream, verify with EXPLAIN.

📛Naming and scope

Column aliases inside vs outside the derived table

You cannot reference outer aliases inside a sibling derived table in the same FROM: use CTEs chained in order, or lateral joins where supported.
Put ORDER BY inside a derived table only when paired with FETCH FIRST / LIMIT in dialects that guarantee top-N per subquery semantics.

Why not a saved view?

Derived tables shine for one-off reports, migration backfills, and intermediate grains you do not want in the catalog. Promote to a view when multiple services need the same projection versioned in DDL.

Correlation

A derived table in FROM can reference outer columns (lateral / correlated forms): powerful for per-row subcomputations, easy to make quadratic without indexes, treat like correlated EXISTS.

⚡ Advanced / dialect note

SQL Server historically allowed ORDER BY in a derived table without TOP in some contexts; portable SQL treats ordering inside subqueries as meaningless unless the standard’s optional row-ordering pattern for limiting rows is used, always pair with a limit when you rely on order.

Readability vs fences

CTEs make diffs readable; on PostgreSQL, WITH … AS (SELECT …) is not guaranteed to materialize unless you use optimization barriers, do not use CTEs as implicit “cache tables” without measuring plans.

TL;DR

A derived table is a subquery in the FROM clause (inline view); a CTE (WITH) names that subquery for readability and reuse in one statement. Both are relations in the query, scope ends at the statement unless materialized. Use them to break complex logic into stages; verify with EXPLAIN that the optimizer is not re-scanning huge CTEs unintentionally on your engine version.