Derived Table

A derived table is a subquery used in the FROM clause, given an alias, and queried as if it were a real table — sometimes called an inline view.

Derived tables let you pre-aggregate or pre-filter data before joining it to something else, all in one statement. Unlike a CTE, a derived table has no name usable elsewhere in the query and must be defined inline exactly where it's used — CTEs are generally preferred today for readability when the same logic is needed more than once.

SELECT dept_avg.department_id, dept_avg.avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) dept_avg
WHERE dept_avg.avg_salary > 8000;