What is a derived table, and how does it differ from a correlated subquery or a CTE?
A derived table is an inline subquery in the FROM clause that acts as a virtual table for the duration of the query; it is not correlated to the outer query and has no name reuse. A CTE is named and can be referenced multiple times, while a correlated subquery executes per-row in WHERE or SELECT.
How to think about it
The question is really testing whether you understand where SQL evaluates subqueries and what scope they have. There are three places a subquery can live: the FROM clause (derived table), the WHERE/SELECT clause (correlated subquery), or the top of the query as a named block (CTE). Each has different evaluation timing and reusability.
What a derived table looks like
A derived table is a subquery you place directly in the FROM clause and give an alias. The engine evaluates it once — producing a complete virtual result set — before the outer query touches it. That’s the key: it runs once, up front, uncorrelated.
The classic use case: you need to filter on an aggregate, but you want to join the aggregated result to another table afterward. A derived table lets you aggregate first, then treat the result like any other table.
Derived table vs. correlated subquery
A derived table is uncorrelated — it does not look at the outer query’s current row. A correlated subquery in WHERE or SELECT does, and re-executes for every row the outer query processes. That makes correlated subqueries flexible but potentially slow on large tables.
Derived table vs. CTE
Functionally identical in most cases — the optimizer inlines them the same way. The practical difference is reusability and readability. A CTE is named at the top of the query and can be referenced multiple times. A derived table is anonymous and inline; if you need its result in two places, you must write the subquery twice.
The key insight
Both derived tables and CTEs are evaluated before the outer query runs. The difference is purely about naming: CTEs get a label so you can refer to them again. Derived tables do not — they are disposable.