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.
SELECT AVG(total) FROM ( SELECT customer_id, SUM(quantity) AS total FROM orders GROUP BY customer_id ) AS per_customer;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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
SELECTproduces. - 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'sWHERE. 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,DISTINCTorLIMIT) are computed first. Indexes on the inner source tables still help; the derived result itself has no index. - Ordering. An
ORDER BYinside a derived table has no guaranteed effect on the outer result (and SQL Server rejects it withoutTOP). Order in the outer query.
Related
- WITH (CTEs), scalar subqueries, LATERAL joins, GROUP BY, FROM
- Lessons: Subqueries, CTEs