✏️ Explanatory Question
Level: Advanced — A powerful technique for breaking complex queries into manageable pieces.
A derived table (also called an inline view) is a subquery placed in the FROM clause that acts as a temporary, virtual table for the outer query. The outer query then treats its result set just like a regular table.
AS dept_avg). Without an alias, MySQL throws a syntax error — "Every derived table must have its own alias."
| Aspect | Derived Table | WHERE Subquery |
|---|---|---|
| Location | FROM clause | WHERE / SELECT clause |
| Returns | A full table | Value(s) for comparison |
| Alias required? | Yes (mandatory) | No |
| Can be joined? | Yes | No |
-- Derived table: average salary per department, then filter
SELECT dept_id, avg_salary
FROM (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) AS dept_avg -- alias is REQUIRED
WHERE avg_salary > 50000;
-- Join a derived table with another table
SELECT d.dept_name, t.emp_count
FROM departments d
JOIN (
SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
) AS t ON d.dept_id = t.dept_id;
-- Find the top earner in each department using a derived table
SELECT e.name, e.salary, e.dept_id
FROM employees e
JOIN (
SELECT dept_id, MAX(salary) AS max_sal
FROM employees
GROUP BY dept_id
) AS m ON e.dept_id = m.dept_id AND e.salary = m.max_sal;WITH) is defined before the main query — often cleaner and reusable. CTEs are covered in a later question.