Subqueries in the FROM Clause

In the previous lesson, we used a subquery in the WHERE clause to filter data. But subqueries can also be used in the FROM clause.

When you place a subquery in the FROM clause, you are essentially creating a temporary, on-the-fly table that only exists for the duration of your query. This is formally known as a Derived Table.

Why use a Derived Table?

Derived tables are useful when you need to perform multiple layers of aggregation.

For example, finding the total value of an order is a standard SUM() with a GROUP BY. But what if you want to find the average order value across the entire company? You need to first calculate the total of each order, and then take the average of those totals.

You cannot nest aggregate functions like AVG(SUM(price)). Instead, you use a Derived Table.

The Syntax

To use a derived table, you wrap your inner query in parentheses and place it in the FROM clause.

Crucially, every derived table MUST be given an alias.

SELECT AVG(order_total)
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
) AS order_summaries;

Let’s break this down:

The inner query (SELECT order_id, SUM...) calculates the total value for every individual order.
The database treats this temporary result set as if it were a real table named order_summaries.
The outer query (SELECT AVG(order_total) FROM order_summaries) then calculates the average value of those pre-calculated totals.

[!WARNING] Forgetting to alias the derived table (AS my_alias) is the most common syntax error when using subqueries in the FROM clause. The database engines enforce this strictly.

Try it yourself in the lab. Build a derived table to find out what our single biggest order total is!