DERIVED TABLE (Subquery in FROM)

DERIVED TABLE (Subquery in FROM) is a SQL statement in the Subqueries category. A subquery in the FROM clause that acts as a temporary table (also called an inline view). The syntax is SELECT alias.* FROM (SELECT ...) AS alias WHERE condition;. It returns temporary result set. A typical example: SELECT dept_stats.dept_name, dept_stats.avg_salary FROM ( SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary FROM employees e JOIN departments d ON e.dept_id = d.id GROUP BY d.name ) AS dept_stats WHERE dept_stats.avg_salary > 60000; -- Derived table filters aggregated data in outer query A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query. A close relative is EXISTS, which returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets.