DocsSubqueriesDerived Tables

Derived Tables

Subqueries

Concept

A derived table is a subquery in the FROM clause that acts as a virtual table for the outer query. It must be enclosed in parentheses and given an alias. The outer query can select from the derived table just like any regular table, joining it, filtering it, and aggregating it.

Derived tables are useful when you need to pre-aggregate, pre-filter, or reshape data before joining it with other tables. For example, you might compute per-customer order totals in a derived table and then join that with the customer table to add demographic information.

Derived tables have the same capabilities as CTEs (Common Table Expressions) but are inline and scoped to a single use. If you need to reference the same subquery multiple times, a CTE is cleaner. For single-use intermediate results, derived tables are perfectly fine.

Syntax

-- Basic derived table
SELECT dt.category, dt.avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS dt
WHERE dt.avg_price > 50;

-- Joining a derived table with a regular table
SELECT c.first_name, ot.order_count, ot.total_spent
FROM customers c
JOIN (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS ot ON ot.customer_id = c.customer_id;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Top 5 categories by revenue, with product count
SELECT
  cs.category,
  cs.revenue,
  cs.units_sold,
  pc.product_count
FROM (
  SELECT
    p.category,
    SUM(oi.unit_price * oi.quantity) AS revenue,
    SUM(oi.quantity) AS units_sold
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category
) AS cs
JOIN (
  SELECT category, COUNT(*) AS product_count
  FROM products
  GROUP BY category
) AS pc ON pc.category = cs.category
ORDER BY cs.revenue DESC
LIMIT 5;

Common Pitfalls & Tips

1A derived table MUST have an alias. Forgetting the AS alias causes a syntax error.
2You cannot reference a derived table more than once in the same query. If you need to, use a CTE instead.
3Deeply nested derived tables become hard to read. Extract them into CTEs for better readability when nesting exceeds two levels.

Practice derived tables