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.

1. What Is a Subquery?

1
• A query nested inside SELECT, WHERE, or HAVING.
2
• Returns a single value (scalar), a list (IN), or sometimes a table.
3
• Not directly joinable unless used in FROM (which becomes a derived table).

Example of a Subquery in WHERE

Here, the subquery returns a single value, and no JOIN is used.

2. What Is a Derived Table?

1
• A SELECT subquery placed in the FROM clause.
2
• Acts like a temporary, inline table.
3
• Can be joined with other tables.
4
• Must have an alias.

Example of a Derived Table Used with JOIN

Here, the derived table d produces grouped data that can be joined like a regular table.

3. Key Differences Between Subqueries and Derived Tables

1
• Placement: Subqueries appear in WHERE/SELECT; derived tables appear in FROM.
2
• Join Capability: Only derived tables can be joined; WHERE subqueries cannot.
3
• Optimization: MySQL may optimize derived tables (materialize or merge), but scalar subqueries often execute per row unless optimized.
4
• Reusability: Derived tables can be reused in multiple JOINs; subqueries cannot.
5
• Readability: Derived tables make complex joins and aggregations clearer.

4. When to Use What?

1
• Use subqueries when you need a single value or simple existence checks.
2
• Use derived tables when you need multi-column results that must be joined.
3
• Use derived tables for better readability in complex transformations.
4
• 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.

Difficulty: 6/10

Topics: subquery vs derived table, MySQL optimizer, performance impact

Scenario Questions

0-2 years experience

1
We need to list each customer with the date of their most recent order. How would you write that using a subquery in the SELECT clause versus a derived table in the FROM clause, and what difference would you expect in the result set?
2
If you join a subquery that aggregates sales per region to the regions table, and then rewrite the same logic as a derived table, what change might you see in MySQL's execution plan?

2-5 years experience

1
Our reporting page started timing out after we changed a join from a derived table to a correlated subquery. Walk me through how you would investigate and fix the performance regression.
2
When refactoring a legacy query that uses a subquery in the SELECT list to a derived table in the FROM clause, what trade‑offs do you consider regarding readability, optimizer behavior, and result correctness?
3
A query using a derived table works on dev data but fails with ‘Too many tables’ on production. How would you decide whether to keep the derived table or rewrite it as a subquery?

5-8 years experience

1
Design a daily metrics aggregation pipeline in MySQL. Would you prefer derived tables or subqueries for the intermediate joins, and how would you justify that choice for scalability and optimizer hints?
2
Our analytics microservice sometimes hits the temporary table limit because of complex joins. Explain how using derived tables versus subqueries could affect temporary table usage and overall system throughput.
3
If you need to rewrite a suite of queries to run on a read‑replica with limited CPU and memory, how would you restructure joins that currently use subqueries to improve performance while preserving semantics?

8+ years experience

1
Your organization is creating a company‑wide guideline for MySQL query patterns. Propose a policy for when to use derived tables versus subqueries, considering maintainability, optimizer predictability, and future migration to a columnar store.
2
During a migration to a sharded MySQL architecture, how does the choice between subqueries and derived tables impact query routing, data locality, and cross‑shard performance, and what guidelines would you set for engineering teams?

Follow-up Questions

❓What does the EXPLAIN output look like for each approach?
❓How would adding an index affect the two versions?
❓Can you think of a case where the optimizer would rewrite a subquery into a derived table automatically?