What is a derived table (subquery in FROM)?
A derived table is a subquery in the FROM clause that acts like a temporary table for that one statement. It must have an alias, and it is useful for filtering or aggregating before joining.
Explain it simply
Imagine first making a small summary sheet of each department's average salary, and then comparing employees against that sheet. The summary sheet exists only while you work on this question. That is a derived table.
Example
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
) d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;The inner query builds one row per department. The alias d is mandatory in MySQL, PostgreSQL and SQL Server, and the columns inside are referenced through it.
Derived tables also let you filter on window functions or aliases, which WHERE cannot do directly:
SELECT * FROM (
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn <= 3;What to say in the interview
"A derived table is an inline view: a subquery in FROM with an alias, scoped to one statement. I use it to aggregate before joining or to filter on window function results. A CTE is a more readable way to write the same thing."
Common mistakes
- Forgetting the alias, which gives a syntax error.