Subqueries in FROM clause (derived tables) in MySQL - Step-by-Step Execution

Concept Flow - Subqueries in FROM clause (derived tables)

Start Query

↓

Identify FROM clause

↓

Detect Subquery in FROM

↓

Execute Subquery

↓

Create Derived Table with Subquery Result

↓

Use Derived Table in Outer Query

↓

Execute Outer Query

↓

Return Final Result

The database first runs the subquery inside the FROM clause, treats its result as a temporary table, then runs the outer query using that temporary table.

Execution Sample

MySQL

SELECT dt.department, dt.avg_salary
FROM (SELECT department, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department) AS dt
WHERE dt.avg_salary > 50000;

This query calculates average salary per department using a subquery in FROM, then selects departments with average salary above 50000.

Execution Table

Step	Action	Subquery Result	Derived Table Created	Outer Query Filter	Output Rows
1	Start executing main query	N/A	N/A	N/A	N/A
2	Execute subquery in FROM clause	[{department: 'HR', avg_salary: 48000}, {department: 'IT', avg_salary: 60000}, {department: 'Sales', avg_salary: 55000}]	Derived table dt created with 3 rows	N/A	N/A
3	Apply WHERE filter on derived table	N/A	N/A	dt.avg_salary > 50000	Rows with IT and Sales departments pass filter
4	Return final result	N/A	N/A	N/A	[{department: 'IT', avg_salary: 60000}, {department: 'Sales', avg_salary: 55000}]
5	End query execution	N/A	N/A	N/A	Final output returned

💡 All derived table rows processed; outer query filters applied; final rows returned.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	N/A	[{department: 'HR', avg_salary: 48000}, {department: 'IT', avg_salary: 60000}, {department: 'Sales', avg_salary: 55000}]	Same	Same
derived_table_dt	N/A	Created with 3 rows	Same	Same
outer_query_output	N/A	N/A	[{department: 'IT', avg_salary: 60000}, {department: 'Sales', avg_salary: 55000}]	Same

Key Moments - 3 Insights

Why does the subquery run before the outer query?

Because the subquery in the FROM clause creates a temporary table (derived table) that the outer query needs to use, so it must be executed first as shown in execution_table step 2.

Can we use column aliases from the subquery in the outer WHERE clause?

Yes, the outer query can use the aliases defined in the subquery because the subquery result is treated as a table with those columns, as seen in execution_table step 3 where dt.avg_salary is used.

What happens if the subquery returns no rows?

The derived table will be empty, so the outer query will have no rows to process and will return an empty result set, stopping execution after step 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the content of the derived table after step 2?

A [{department: 'HR', avg_salary: 60000}, {department: 'IT', avg_salary: 48000}]

B [{department: 'HR', avg_salary: 48000}, {department: 'IT', avg_salary: 60000}, {department: 'Sales', avg_salary: 55000}]

C Empty table

D N/A

💡 Hint

Check the 'Subquery Result' and 'Derived Table Created' columns in execution_table row 2.

At which step does the outer query apply the WHERE filter?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Outer Query Filter' column in execution_table.

If the subquery returned no rows, what would be the output rows in the final result?

A Empty result set

B All rows from employees table

C Only rows with avg_salary > 50000

D Error occurs

💡 Hint

Refer to key_moments explanation about empty subquery result and execution_table step 2.

Concept Snapshot

Subqueries in FROM clause create a temporary table called a derived table.
The subquery runs first, producing rows and columns.
The outer query treats this result as a table and can filter or join it.
Use alias for the subquery result to reference it in outer query.
This helps break complex queries into simpler parts.

Full Transcript

This visual execution trace shows how a subquery inside the FROM clause works in MySQL. First, the database runs the subquery to get a temporary table with department names and their average salaries. This temporary table is called a derived table and is given an alias 'dt'. Then, the outer query uses this derived table to select only those departments where the average salary is greater than 50000. The trace shows each step: starting the main query, executing the subquery, creating the derived table, applying the WHERE filter, and finally returning the filtered rows. Variables like the subquery result and derived table content are tracked step-by-step. Common confusions like why the subquery runs first and how aliases work are clarified. The quiz questions help reinforce understanding by asking about the derived table content, when filtering happens, and what if the subquery returns no rows.