Why subqueries nest queries in MySQL - Visual Breakdown

Concept Flow - Why subqueries nest queries

Start Outer Query

↓

Encounter Subquery

↓

Execute Subquery First

↓

Return Subquery Result

↓

Use Result in Outer Query

↓

Complete Outer Query Execution

The outer query pauses to run the inner subquery first, then uses its result to continue and finish.

Execution Sample

MySQL

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Find employees who work in the 'Sales' department by first finding the department's id.

Execution Table

Step	Action	Query Part	Result/Value
1	Start outer query	SELECT name FROM employees WHERE department_id = (...)	Waiting for subquery result
2	Execute subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
3	Subquery result used	WHERE department_id = 3	Filter employees with department_id = 3
4	Fetch matching employees	SELECT name FROM employees WHERE department_id = 3	Returns names: 'Alice', 'Bob'
5	Query complete	Full query	Result set with 'Alice', 'Bob'

💡 Subquery executed first to provide value for outer query condition, then outer query completes.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	null	3	3	3
outer_query_condition	department_id = (subquery)	department_id = 3	department_id = 3	department_id = 3
result_set	empty	empty	['Alice', 'Bob']	['Alice', 'Bob']

Key Moments - 2 Insights

Why does the outer query wait for the subquery to finish before continuing?

Because the outer query needs the subquery's result to know which rows to select, as shown in execution_table step 2 and 3.

Can the subquery return multiple values here?

No, because the outer query uses '=' which expects a single value; if multiple values were returned, it would cause an error.

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result at step 2?

A 3

B 'Sales'

C Alice

D department_id

💡 Hint

Check the 'Result/Value' column in step 2 of execution_table.

At which step does the outer query start filtering employees by department_id?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Look for when the subquery result is used in the outer query condition in execution_table.

If the subquery returned multiple ids, what would happen to the outer query?

A It would ignore extra values

B It would work normally

C It would cause an error because '=' expects one value

D It would return all employees regardless of department

💡 Hint

Refer to key_moments about subquery returning multiple values and '=' operator.

Concept Snapshot

Subqueries nest inside outer queries.
The subquery runs first to provide a value.
Outer query uses this value to filter results.
Subquery must return one value if '=' is used.
This nesting allows complex filtering in one query.

Full Transcript

When a query contains a subquery, the database first runs the subquery to get its result. This result is then used by the outer query to filter or select rows. For example, to find employees in the 'Sales' department, the subquery finds the department id for 'Sales'. The outer query then selects employees with that department id. The outer query waits for the subquery to finish because it needs the subquery's value to continue. If the subquery returns multiple values but the outer query expects one, an error occurs. This nesting of queries allows building complex queries step-by-step.