Subqueries in the FROM Clause (Derived Tables)

The Pre-Aggregation Problem

I needed to join a summary table to a detail table, but the summary didn't exist. I had to calculate it on the fly.

The Quest: The Derived Table

You can put a subquery in the `FROM` clause and give it an alias. The outer query then treats it like a regular table.

The Implementation: The Syntax

SELECT

d.customer_id,

d.order_count,

c.name

FROM (

SELECT customer_id, COUNT(*) AS order_count

FROM orders

GROUP BY customer_id

) AS d -- This is the derived table

JOIN customers c ON d.customer_id = c.id

WHERE d.order_count > 10;

How It Works

1. The inner subquery runs first and produces a result set (customer_id, order_count).

2. This result set is aliased as `d`.

3. The outer query joins `d` to `customers` as if `d` were a real table.

The "Oops" Moment

I forgot to give the derived table an alias. SQL threw an error: "Every derived table must have its own alias."

**Pro Tip**: Always use the `AS alias` after the closing parenthesis of the subquery.

The Victory

I could now create complex, multi-step analysis in a single query. Derived tables are powerful, but they can become unreadable if nested too deeply. Tomorrow, we'll learn a cleaner way: CTEs.

Your Task for Today

Write a query with a subquery in the `FROM` clause. Use it to pre-aggregate data before joining to another table.

*Day 49: Common Table Expressions (CTEs).*