In this lesson, you will learn how to use temporary query results as first-class tables to simplify complex relational paths.
The Problem: When Tables Aren't Enough
Sometimes, the data you need to join isn't sitting neatly in a table. Perhaps you need to calculate an average for each department before joining that number back to your main list of employees. If you tried to do this in one giant step, the logic would become tangled and unreadable.
The Solution: The Derived Table
A derived table is a subquery placed inside the FROM or JOIN clause. Think of it as a "on-the-fly" table that exists only for the duration of your query. By condensing a calculation into a derived table, you treat the result as a standard table with its own name and columns.
Imagine you want to see which employees earn more than the average salary of their specific department. Instead of wrestling with complex logic, you can create a derived table of averages and join it:
SELECT e.name, e.salary, d.avg_salary
FROM employees AS e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;
Why This Works
In the code above, the subquery inside the JOIN is evaluated first. It generates a mini-table called d containing only two columns: department_id and avg_salary. Once that temporary result is ready, the main query treats it exactly like a permanent table in your database.
This approach is powerful because it breaks a complex "many-step" problem into a clean, modular structure. You define the "shape" of your data in the derived table, then join it to your main dataset to complete the request. It turns a chaotic multi-part query into a structured, logical pipeline.
Key Takeaway: A derived table allows you to perform complex calculations in a subquery and treat the output as a regular table, making your joins cleaner and your logic easier to manage.