Subqueries in the FROM Clause (Derived Tables)
Wrapping a query as a virtual table and why aliases are mandatory.
What a Derived Table Is
A subquery in the FROM clause is called a derived table (or inline view). Instead of returning a single value, it returns a whole result set that the outer query treats as if it were a real table.
- It can have many rows and many columns.
- You query it, join it, and filter it like any table.
Interviewers use derived tables to test whether you can break a problem into stages.
Aliases Are Mandatory
The number-one gotcha: a derived table must have an alias. Without one, most engines reject the query.
- MySQL: Every derived table must have its own alias.
- Postgres: subquery in FROM must have an alias.
Give it a name (here dept_avg) and you can reference its columns by that name.
SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_avg;All lessons in this course
- Scalar Subqueries in SELECT and WHERE
- Subqueries in the FROM Clause (Derived Tables)
- IN, ANY and ALL Subqueries
- EXISTS vs IN Performance