Derived Tables
IntermediateA derived table is a subquery in the FROM clause that acts as an inline, unnamed view, enabling multi-level aggregation and intermediate filtering without creating a permanent object.
Overview
A derived table is written as a subquery enclosed in parentheses inside the FROM clause with a mandatory alias. The outer query treats it like any other table. Derived tables are useful for flattening two-level aggregations (e.g., average of per-user totals), pre-filtering rows before a join, or restructuring data before grouping. Unlike CTEs, derived tables cannot be referenced more than once — use a CTE if you need to reference the intermediate result in multiple places. The query planner generally inlines derived tables into the outer query, so performance is usually identical to the equivalent CTE or subquery.
Average Order Value Per City Using a Derived Table
You cannot average an aggregate in a single GROUP BY. A derived table first computes the per-user total, then the outer query averages those totals by city.
-- Goal: average order value per city
-- (average of each user's total spend — not a simple AVG of all amounts)
-- Anti-pattern: AVG of all order amounts (misleading for users with many orders)
SELECT u.city, AVG(o.amount) AS naive_avg
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.city;
-- This over-represents high-volume users.
-- Correct: derived table computes per-user total first, outer query averages
SELECT city, AVG(user_total) AS avg_user_order_value
FROM (
SELECT u.city, o.user_id, SUM(o.amount) AS user_total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.city, o.user_id
) per_user_totals -- mandatory alias
GROUP BY city
ORDER BY avg_user_order_value DESC;Nesting Derived Tables and Pre-filtering Before Joins
Pre-filtering inside a derived table reduces the rows before a join, which can dramatically improve performance. Nested derived tables keep the logic layered without creating temp tables.
-- Pre-filter completed high-value orders before joining to products
SELECT pd.category, SUM(hv.amount) AS category_revenue
FROM (
-- Inner derived table: only completed orders over 500
SELECT id, product_id, amount
FROM orders
WHERE status = 'completed'
AND amount > 500
AND created_at >= '2024-01-01'
) hv
JOIN products pd ON pd.id = hv.product_id
GROUP BY pd.category
ORDER BY category_revenue DESC;
-- Nested derived tables (legal but prefer CTEs for readability beyond 2 levels):
SELECT dept_name, avg_sal
FROM (
SELECT department_id, AVG(salary) AS avg_sal
FROM (
SELECT id, department_id, salary
FROM employees
WHERE hire_date >= '2020-01-01' -- inner filter
) recent_hires
GROUP BY department_id
) dept_avg
JOIN departments d ON d.id = dept_avg.department_id
WHERE avg_sal > 70000;Key Points to Remember
- 1A derived table is a subquery in the FROM clause and must have an alias — omitting the alias is a syntax error.
- 2Use a derived table to perform multi-level aggregation (average of sums) that cannot be done in a single GROUP BY.
- 3Pre-filtering rows inside a derived table before a join can significantly reduce the number of rows the join processes.
- 4Unlike a CTE, a derived table cannot be referenced more than once in the same query — use a CTE for reuse.
- 5The planner typically inlines derived tables, so performance is usually the same as an equivalent CTE.
- 6Prefer CTEs over deeply nested derived tables (more than 2 levels) for readability.
Interview Questions
Sign in to ask AriaWhat is a derived table and how does it differ from a CTE?
Write a query to compute the average order value per city (average of per-user totals, not average of all orders).
Why might pre-filtering inside a derived table improve join performance?
When would you choose a derived table over a CTE?
Ask Aria about Derived Tables
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.