What are derived tables, and how does the FROM (subquery) clause work internally?

Q: What are derived tables, and how does the FROM (subquery) clause work internally?

In MySQL, a **derived table** is a subquery used in the FROM clause that acts as a temporary table. It allows you to encapsulate complex queries and use their results as if they were a regular table in the outer query. Defined as a subquery in the FROM clause and assigned a temporary alias, e.g., `FROM (SELECT ...) AS sub`. Computed first during query execution and treated as a virtual table for the outer query. Can be joined with other tables or further filtered using WHERE, GROUP BY, HAVING, and ORDER BY clauses. Useful for breaking complex queries into manageable parts or performing aggregation before joining. Internally, MySQL executes the subquery to produce a result set, then the outer query treats this result set as a table. This means optimizations like indexes on underlying tables may not always apply directly to derived tables. SELECT dept.department_name, dt.avg_salary FROM departments AS dept JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS dt ON dept.id = dt.department_id WHERE dt.avg_salary > 60000;

Difficulty: 5/10

derived tables, subquery execution, query optimization

Understanding Derived Tables and FROM (Subquery) Clauses in MySQL

In MySQL, a derived table is a subquery used in the FROM clause that acts as a temporary table. It allows you to encapsulate complex queries and use their results as if they were a regular table in the outer query.

Key Points About Derived Tables

1
Defined as a subquery in the FROM clause and assigned a temporary alias, e.g., FROM (SELECT ...) AS sub.
2
Computed first during query execution and treated as a virtual table for the outer query.
3
Can be joined with other tables or further filtered using WHERE, GROUP BY, HAVING, and ORDER BY clauses.
4
Useful for breaking complex queries into manageable parts or performing aggregation before joining.

Internally, MySQL executes the subquery to produce a result set, then the outer query treats this result set as a table. This means optimizations like indexes on underlying tables may not always apply directly to derived tables.

Example of a Derived Table

Scenario Questions

0-2 years experience

Follow-up Questions