Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL derived tables

Reference for SQL derived tables: use a subquery in the FROM clause as a temporary table, filter aggregates, rename columns and compare with CTEs.

A derived table is a subquery used in the FROM clause. Its result behaves like a temporary table that exists only for that statement. You give it an alias and then select from it, filter it or join it like any other table.

Derived tables are handy when you need to work on a result in two steps: aggregate first, then filter or aggregate again. A typical case is "the average of the per-customer totals", which cannot be written as a single AVG(SUM(...)) in most databases.

SQL
SELECT AVG(total)
FROM (
  SELECT customer_id, SUM(quantity) AS total
  FROM orders
  GROUP BY customer_id
) AS per_customer;

Try it

SQL Average of per-customer totals ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Join a derived table back to its source ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Column aliases and a computed filter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
SELECT ...
FROM (SELECT ... FROM ... ) [AS] alias [(column_alias, ...)]
[JOIN ...];
Part Description
(SELECT ...) Any query, including GROUP BY, LIMIT and joins. It cannot refer to other tables of the same FROM list (unless LATERAL).
alias Names the derived table. Required by several databases.
(column_alias, ...) Optional list that renames the columns (PostgreSQL, MySQL 8 and SQL Server allow it; SQLite does not).

Dialect differences

Database Alias required? Notes
SQLite No Allowed; give one anyway for readability.
PostgreSQL Before version 16 Error "subquery in FROM must have an alias"; optional since 16. Supports AS t(col1, col2).
MySQL Yes Error 1248: Every derived table must have its own alias.
SQL Server Yes Also allows AS t(col1, col2).
Oracle No Do not use AS before the alias: write FROM (SELECT ...) stats.

Usage notes

  • Always alias the derived table and every computed column inside it. The outer query can only see the names the inner SELECT produces.
  • Aggregate then filter. Filtering on an aggregate inside a derived table is the same as HAVING, but derived tables also let you aggregate the aggregates.
  • Window functions. Results of a window function cannot be filtered in the same SELECT's WHERE. Put the window function in a derived table (or CTE) and filter outside, as in top-N queries.
  • CTE or derived table? A CTE does the same job with a name defined up front, and can be reused several times in one statement. Derived tables are fine for a single use.
  • Optimization. Most optimizers merge simple derived tables into the outer query. Complicated ones (with GROUP BY, DISTINCT or LIMIT) are computed first. Indexes on the inner source tables still help; the derived result itself has no index.
  • Ordering. An ORDER BY inside a derived table has no guaranteed effect on the outer result (and SQL Server rejects it without TOP). Order in the outer query.