A derived table is a subquery in the FROM clause that acts like a temporary table. Think of it as creating a virtual table on the fly that exists only for the duration of your query. You can SELECT from it, JOIN it with other tables, and treat it exactly like a regular table.

The key rule: Every derived table MUST have an alias. You can't write FROM (SELECT ...) - you must write FROM (SELECT ...) AS some_name. This alias lets you reference the derived table in the outer query.

Why use derived tables? They let you break complex logic into steps. Instead of one giant query, you can filter data in a subquery, then aggregate the filtered results in the outer query. Much more readable and often faster.

**How Derived Tables Work:**

When you put a subquery in FROM clause:

1. Database executes the subquery first

2. Creates temporary result set in memory

3. Outer query treats this result set as a table

4. After query completes, temporary result set is discarded

Example:

SELECT * FROM (SELECT * FROM employees WHERE salary > 80000) AS high_earners;

Execution:

1. Inner query: SELECT * FROM employees WHERE salary > 80000

- Returns 150 rows of high earners

2. Creates temp table "high_earners" with those 150 rows

3. Outer query: SELECT * FROM high_earners

- Returns all 150 rows

4. Temp table discarded

**The Mandatory Alias Rule:**

Every derived table MUST have an alias. This is required by SQL standard.

❌ Wrong: SELECT * FROM (SELECT * FROM employees)

✅ Correct: SELECT * FROM (SELECT * FROM employees) AS e

Why? The outer query needs a name to reference the derived table. Even if you don't use the alias, you must provide it.

**Derived Tables vs Regular Tables:**

Derived table:

- Exists only during query execution

- Defined inline in query

- No storage overhead

- Can't be indexed

- Recreated every time query runs

Regular table:

- Persists in database

- Created with CREATE TABLE

- Uses disk storage

- Can have indexes

- Data persists between queries

**Common Use Cases:**

1. Pre-Aggregation:

Calculate totals first, then join with details

SELECT d.name, agg.total_sales