JOINs Using Subqueries vs. Derived Tables in MySQL
JOINs can be performed directly on tables, on derived tables (subqueries in the FROM clause), or involve subqueries inside the SELECT or WHERE clause. Although both subqueries and derived tables use nested queries, their behavior, performance, and optimization differences are important to understand.
• A query nested inside SELECT, WHERE, or HAVING.
• Returns a single value (scalar), a list (IN), or sometimes a table.
• Not directly joinable unless used in FROM (which becomes a derived table).
Here, the subquery returns a single value, and no JOIN is used.
• A SELECT subquery placed in the FROM clause.
• Acts like a temporary, inline table.
• Can be joined with other tables.
• Must have an alias.
Here, the derived table d produces grouped data that can be joined like a regular table.
• Placement: Subqueries appear in WHERE/SELECT; derived tables appear in FROM.
• Join Capability: Only derived tables can be joined; WHERE subqueries cannot.
• Optimization: MySQL may optimize derived tables (materialize or merge), but scalar subqueries often execute per row unless optimized.
• Reusability: Derived tables can be reused in multiple JOINs; subqueries cannot.
• Readability: Derived tables make complex joins and aggregations clearer.
• Use subqueries when you need a single value or simple existence checks.
• Use derived tables when you need multi-column results that must be joined.
• Use derived tables for better readability in complex transformations.
• Avoid subqueries that execute per-row (especially correlated ones) unless necessary.
In summary: Subqueries are used for filtering or single-value extraction, while derived tables behave like temporary tables that can participate in JOINs. Derived tables are typically more flexible and efficient in JOIN-heavy queries.