Subqueries in FROM (Derived Tables)

A subquery can appear in the FROM clause, in which case its result set is treated exactly like a real table for the rest of the query. This kind of subquery is usually called a derived table (also called an inline view in some database documentation). Once you write a subquery in FROM, you can SELECT specific columns from it, join it to other tables, filter it with WHERE, group it further, and so on — the outer query simply does not know or care that its "table" was computed on the fly.

Derived Tables Must Have an Alias

SQL

-- Pre-aggregate order totals per customer, then filter the aggregate
SELECT customer_totals.customer_id, customer_totals.lifetime_value
FROM (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE customer_totals.lifetime_value > 5000;

Warning

Almost every database requires a derived table to have an alias — the AS customer_totals in the example above is not optional styling. Omitting it produces a syntax error in PostgreSQL, MySQL, and SQL Server alike, because the parser needs a name to refer to the subquery's columns by. (The AS keyword itself is optional in most dialects — you can write FROM (SELECT ...) customer_totals — but the alias name is required.)

Why Use a Derived Table

The most common reason to reach for a derived table is to pre-aggregate or pre-shape data before doing more work with it. Once data has been summarized in the subquery, the outer query can filter, join, or sort that summary as if it were an ordinary table — without repeating the aggregation logic or fighting with GROUP BY rules in a single giant query.

SQL

-- Join a derived table of monthly revenue against a targets table
SELECT
  monthly_revenue.month,
  monthly_revenue.revenue,
  targets.target_revenue,
  monthly_revenue.revenue - targets.target_revenue AS variance
FROM (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
) AS monthly_revenue
JOIN targets
  ON targets.month = monthly_revenue.month
ORDER BY monthly_revenue.month;

Notice that the derived table (monthly_revenue) already did the hard work of grouping and summing before the JOIN ever happens. The outer query is left with a much simpler job: join two already-shaped result sets together and compute a difference.

Filtering an Aggregate Further

A derived table is also useful when you need to filter on an aggregated value using something other than HAVING — for example, when the filter condition itself involves another calculation, or when you want to reuse the aggregated column multiple times without repeating the expression.

SQL

SELECT product_id, total_sold, total_sold * 0.1 AS bonus_threshold
FROM (
  SELECT product_id, SUM(quantity) AS total_sold
  FROM order_items
  GROUP BY product_id
) AS product_sales
WHERE total_sold > 100;

Note

For anything beyond a single, simple derived table, a Common Table Expression (CTE) written with WITH name AS (...) is usually the more readable alternative — it names the subquery before the main query starts, and multiple CTEs can be chained together cleanly instead of nesting parentheses inside FROM. See the CTEs with WITH page for the same pattern rewritten more readably.

A subquery in FROM is called a derived table — it behaves exactly like a real table to the outer query.
An alias is required — write FROM (SELECT ...) AS alias_name, or you'll get a syntax error.
Derived tables are ideal for pre-aggregating or pre-filtering data before further joins, filters, or sorting.
For multiple layered subqueries, prefer CTEs (WITH) for readability over nested derived tables.