Subqueries in the FROM Clause (Derived Tables)
You'll learn: How to use subqueries in the FROM clause to create temporary result sets that act like regular tables within your query.
What Are Derived Tables?
A derived table is a subquery placed in the FROM clause that creates a temporary, on-the-fly table you can query just like any physical table. Think of it as building a custom dataset mid-query, then immediately using it as your data source.
Instead of querying a permanent table, you're querying the results of another query. This is incredibly powerful when you need to perform multi-step transformations or calculations that can't easily be done in a single flat query.
Why Use Derived Tables?
Derived tables help you:
- Pre-aggregate data before joining or filtering
- Break complex logic into readable steps
- Calculate values that you then use in outer-level filtering or grouping
- Work around limitations where you can't directly use aggregate results
Basic Syntax
SELECT outer_columns
FROM (
SELECT inner_columns
FROM actual_table
WHERE inner_conditions
) AS alias_name
WHERE outer_conditions;
Critical: You must give the derived table an alias (the AS alias_name part).
Simple Example
SELECT avg_salary_dept
FROM (
SELECT department, AVG(salary) AS avg_salary_dept
FROM employees
GROUP BY department
) AS dept_averages
WHERE avg_salary_dept > 50000;
Here, the inner query aggregates salaries by department. The outer query treats those results as a table called dept_averages and filters departments with high averages.
Key Takeaway: Subqueries in FROM create reusable, temporary result sets that simplify complex queries by breaking them into logical, nested steps—just remember to always alias your derived tables.