Derived Tables

A derived table is a subquery that appears in the FROM clause of an outer query. The database computes the subquery first and presents its result as a virtual table that the outer query can JOIN, filter, aggregate, and otherwise treat exactly like a base table. The derived table exists only for the duration of the query — it is not stored in the database.

Three mandatory rules: First, the subquery must be enclosed in parentheses. Second, the derived table must have an alias — FROM (SELECT ...) AS alias_name. Without the alias, the query produces a syntax error. Third, the outer query must reference the derived table by its alias — any column the outer query wants to use must be included in the derived table's SELECT list.

The practical rules: derived tables cannot be referenced more than once in the same query (use a CTE for reuse), they cannot reference each other (derived table A cannot reference derived table B in the same FROM clause — use CTEs for sequential dependencies), and they cannot contain ORDER BY in most databases unless LIMIT is also specified. Beyond these restrictions, derived tables are fully functional SQL — they can contain JOINs, GROUP BY, HAVING, window functions, and any other SQL construct. The power comes from their composability: the outer query treats the result of any arbitrary SELECT as a clean, queryable table.

A correlated subquery in WHERE executes once per outer row — N executions for N outer rows. This can be O(n²) in complexity for large tables. A derived table in FROM is computed once — it runs the subquery a single time, produces a result set, and the outer query joins to that result. For aggregations that would otherwise be correlated, the derived table approach is dramatically faster at scale.

The specific case where derived table beats correlated subquery: "for each employee, find those whose salary is above the department average." Correlated subquery runs SELECT AVG(salary) FROM employees WHERE department = e.department once per employee — N subquery executions. Derived table runs SELECT department, AVG(salary) FROM employees GROUP BY department once total — one execution — and the outer query JOINs employees to these pre-computed averages. For 10,000 employees across 20 departments: correlated = 10,000 subquery executions, derived table = 1 execution producing 20 rows, then one JOIN pass through 10,000 employee rows.

Additional reasons to prefer derived table over correlated subquery: when the outer query needs multiple columns from the subquery result (correlated scalar subquery returns one value — you need one correlated subquery per column; derived table provides all columns at once), when the same computation is needed in multiple places in the outer query (derived table computes it once and it is reusable via alias; correlated subquery must be repeated), and when the subquery logic is complex enough that seeing it inline in WHERE hurts readability. Use correlated subquery when the logic is simple, involves existence checks (EXISTS), or when the per-row dependency is genuinely required and cannot be expressed as a join.

The fan-out problem occurs when joining a one-to-many relationship before aggregating — each "one" side row is duplicated once per "many" side row, causing aggregates on the one-side column to count the same value multiple times. SUM(orders.total_amount) after joining orders to order_items triple-counts each order's total_amount for an order with three items.

The derived table solution: pre-aggregate the many-side table before joining. Instead of joining orders to order_items and then aggregating, first aggregate order_items to order-level metrics in a derived table (one row per order_id with SUM(line_total) and SUM(quantity)), then join that one-row-per-order result to orders. The resulting join is one-to-one at the order level — no fan-out, no inflated aggregates.

Implementation: SELECT o.store_id, SUM(o.total_amount) AS revenue, SUM(item_stats.total_qty) AS total_items FROM orders AS o JOIN (SELECT order_id, SUM(quantity) AS total_qty FROM order_items GROUP BY order_id) AS item_stats ON o.order_id = item_stats.order_id GROUP BY o.store_id. The derived table item_stats has exactly one row per order — the join is safe. Using COUNT(DISTINCT o.order_id) instead of COUNT(*) is the other approach when pre-aggregation is not possible — it counts unique orders rather than order-item rows. The derived table approach is preferred when multiple many-side metrics are needed, as it is cleaner and more efficient.

Window functions are computed at the SELECT stage of query execution — after FROM, WHERE, GROUP BY, and HAVING have already been processed. WHERE runs before window functions are computed, so referencing a window function result in WHERE is a reference to a value that does not yet exist. This is why WHERE rank_in_store <= 2 fails — rank_in_store is a window function result that has not been computed when WHERE evaluates.

The derived table solution: compute the window function in an inner query, then filter on its result in the outer query's WHERE. The inner query: SELECT *, RANK() OVER (PARTITION BY store_id ORDER BY total_amount DESC) AS rank_in_store FROM orders. This produces a result set where every row has its rank attached. The outer query: SELECT * FROM (inner_query) AS ranked WHERE rank_in_store <= 2. The outer WHERE now filters on rank_in_store which exists as a column in the derived table — it was computed in the inner query and is just a regular column from the outer query's perspective.

This pattern — window function in derived table, filter in outer WHERE — is one of the most common uses of derived tables. It appears every time you need to filter by rank, percentile, running total threshold, or any other window-computed value. The same pattern works for HAVING (cannot filter on window function results in HAVING of the same query) and for ORDER BY (though ORDER BY can reference window function columns in the same query without a derived table). An equivalent approach uses CTEs: WITH ranked AS (SELECT *, RANK() OVER (...) AS rn FROM orders) SELECT * FROM ranked WHERE rn <= 2. Both derived table and CTE are correct — the CTE version is more readable for complex ranking queries.

A derived table is defined inline within the FROM clause — it appears as a parenthesised subquery immediately where it is used. A CTE (Common Table Expression) is defined before the main query using the WITH keyword — it is given a name and can be referenced by that name anywhere in the subsequent query. Both produce the same intermediate result set and both are computed at query execution time (not stored).

The key functional differences: CTEs can be referenced multiple times in the same query; a derived table can only be referenced once at the location it is defined. CTEs can be recursive (WITH RECURSIVE) enabling hierarchical queries; derived tables cannot. CTEs read top-to-bottom in logical order — each step builds on the previous; derived tables are read inside-out which can be harder to follow for complex logic. In PostgreSQL, CTEs are materialised by default (computed once and cached) while derived tables are typically inlined into the main query plan; this difference is controllable with the MATERIALIZED and NOT MATERIALIZED hints.

Choosing between them: use a derived table when the subquery is short (under 5 lines), used exactly once, and the inline position makes the query's logic clear — a simple pre-aggregation that is directly joined to one other table. Use a CTE when the intermediate result has a meaningful name that documents what it computes, when the same result is referenced more than once, when the logic has multiple sequential steps where each builds on the previous, or when the derived table would be long enough to obscure the outer query's logic. For simple cases, derived tables are more compact. For complex analytical queries, CTEs produce code that is significantly easier to read, review, and maintain.

Cause: Every derived table (subquery in FROM) requires an alias immediately after the closing parenthesis. Without the alias, the database cannot give the derived table a name for the outer query to reference it by, which is a syntax error. This is one of the most common beginner mistakes with derived tables.

Fix: Add AS alias_name immediately after the closing parenthesis of the subquery: FROM (SELECT ...) AS my_derived_table. The alias can be any valid identifier. Choose a descriptive name that reflects what the derived table computes: AS store_stats, AS customer_totals, AS ranked_orders. Short aliases like AS t or AS dt work syntactically but add no clarity — use descriptive names.

Cause: The outer query references a column name that is either not returned by the derived table's SELECT list or is referenced by the wrong alias. The outer query can only access columns that the derived table explicitly returns — if total_spend is computed inside the derived table but not included in its SELECT, it is not accessible to the outer query.

Fix: Ensure the derived table's SELECT list includes every column the outer query needs: SELECT customer_id, SUM(total_amount) AS total_spend FROM orders GROUP BY customer_id. If the column has a computed expression (SUM(total_amount)), give it an alias in the derived table so the outer query can reference it by a clear name. Check the alias prefix — if the derived table is aliased AS cs, reference its columns as cs.total_spend not just total_spend (though both often work, prefixing prevents ambiguity errors).

Cause: The derived table does not aggregate to the correct granularity before the join. If the derived table joins orders to order_items without aggregating to order-level first, the derived table itself has one row per order-item, and any subsequent join or aggregation in the outer query operates on inflated data. The fan-out problem can live inside the derived table, not just between the derived table and the outer query.

Fix: Verify the derived table's row count is the expected granularity: SELECT COUNT(*) FROM (your derived table subquery). If it returns more rows than expected (more than one per store_id for a per-store aggregation), the derived table has a fan-out — add GROUP BY to the correct level inside the derived table. The derived table should produce exactly one row per entity at the granularity the outer query needs.

Cause: A window function result is referenced in the WHERE clause of the same query that defines it. WHERE is evaluated before SELECT in SQL's logical execution order — window functions are computed at the SELECT stage. So WHERE rank_in_store <= 2 references a column that has not been computed yet when WHERE executes.

Fix: Wrap the window function computation in a derived table (or CTE), then filter in the outer query's WHERE: SELECT * FROM (SELECT *, RANK() OVER (...) AS rank_in_store FROM orders) AS ranked WHERE rank_in_store <= 2. The outer WHERE now references rank_in_store as a regular column of the derived table — it exists because it was computed in the inner query. This pattern is necessary any time you need to filter, sort, or further process a window function result.

Cause: Derived tables can only be referenced once at the location they are defined. Unlike CTEs, a derived table cannot be used in multiple places in the same query. If the same pre-computed result is needed in two JOIN conditions or two WHERE subclauses, you cannot reference the derived table alias a second time — it does not exist as a named entity outside its single use point.

Fix: Convert the derived table to a CTE: WITH derived_name AS (the subquery) SELECT ... FROM derived_name JOIN ... derived_name. CTEs can be referenced multiple times in the same query. Alternatively, use a temporary table (CREATE TEMP TABLE) for results that are needed in many places across multiple queries. If the second reference is in a subquery within the outer WHERE (like WHERE col > (SELECT AVG(col) FROM derived_table_alias)), PostgreSQL may allow this but it is database-dependent — CTEs are the portable solution.

Write a single query using at least two derived tables that produces a customer value report. The report should show: customer_id, full name, loyalty_tier, city, total_delivered_spend (sum of their delivered orders), order_count, avg_order_value (rounded to 2 decimal places), the overall average spend per customer (as a single broadcast value — same for all rows), the difference between the customer's spend and the overall average (vs_overall_avg), and a value_segment: 'Champion' if spend > 2 × overall average, 'High Value' if spend > overall average, 'Average' if within ±20% of overall average, 'Below Average' otherwise. Only include customers who have at least one delivered order. Sort by total_delivered_spend descending.