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.
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.