What is a derived table, and how does it differ from a correlated subquery or a CTE?

How to think about it

The question is really testing whether you understand where SQL evaluates a subquery and what scope it has. A subquery can live in three places, each with different timing and reusability: the FROM clause (a derived table), a WHERE/SELECT clause (a correlated subquery), or at the top as a named block (a CTE).

A derived table

A derived table is a subquery you drop straight into FROM and give an alias. The engine evaluates it once, up front, producing a complete virtual result the outer query then reads — uncorrelated, no per-row re-execution. Its classic job: aggregate first, then treat the aggregate like any other table.

-- Aggregate first, then filter on the result
SELECT dept, avg_sal
FROM (
    SELECT dept, ROUND(AVG(salary)) AS avg_sal
    FROM employees
    GROUP BY dept
) dept_stats
WHERE avg_sal > 75000;

dept	avg_sal
Eng	111667.0
Sales	76000.0

Engineering and Sales clear the 75k bar; HR (a single 60k salary) doesn’t. The inner query produced one row per department, and the outer query filtered that virtual table — something a bare WHERE AVG(...) can’t do, since you can’t filter on an aggregate before it’s computed.

Derived table vs correlated subquery

A derived table is uncorrelated — it never looks at the outer query’s current row, so it runs once. A correlated subquery in WHERE/SELECT does look, and re-executes for every outer row — flexible, but a potential O(n²) on a big table.

Derived table vs CTE

Functionally these are the same — the optimizer inlines them identically. The difference is purely naming. A CTE is labelled at the top and can be referenced repeatedly; a derived table is anonymous and inline, so needing its result twice means writing it twice:

-- The same dept_stats, as a named CTE — readable and reusable
WITH dept_stats AS (
    SELECT dept, ROUND(AVG(salary)) AS avg_sal
    FROM employees
    GROUP BY dept
)
SELECT e.name, e.dept, ds.avg_sal
FROM employees e
JOIN dept_stats ds ON e.dept = ds.dept
ORDER BY e.dept, e.name;

name	dept	avg_sal
Aarav	Eng	111667.0
Bea	Eng	111667.0
Chen	Eng	111667.0
Farah	HR	60000.0
Dara	Sales	76000.0
Eli	Sales	76000.0

Here every employee sits beside their department’s average — the named dept_stats joined back to the rows. Written as a derived table it would work the same, but the name makes it readable and lets you reference it again without copy-pasting the aggregation.

The idea underneath

Both the derived table and the CTE are evaluated before the outer query runs; the only real difference is the label. A CTE gets a name so you can refer to it again — a derived table is disposable.