Nested subqueries in SQL - Step-by-Step Execution

Concept Flow - Nested subqueries

Start Outer Query

↓

Encounter Subquery

↓

Execute Subquery

↓

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

SQL

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 Sales 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	Use subquery result	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	End query	Full query executed	Result set: ['Alice', 'Bob']

💡 Subquery returns department id 3, outer query uses it to filter employees, then finishes.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
subquery_result	NULL	3	3	3
outer_filter	NULL	NULL	department_id = 3	department_id = 3
result_set	Empty	Empty	Empty	['Alice', 'Bob']

Key Moments - 2 Insights

Why does the outer query wait before filtering employees?

Because the outer query needs the subquery result first (see execution_table step 2) to know which department_id to filter by.

What happens if the subquery returns no rows?

The outer query's condition becomes false or NULL, so no employees match, resulting in an empty result set (not shown here but implied after step 2).

Visual Quiz - 3 Questions

Look at the execution table, what value does the subquery return at step 2?

A 3

B Sales

C Alice

D department_id

💡 Hint

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

At which step does the outer query apply the filter using the subquery result?

A Step 4

B Step 1

C Step 3

D Step 5

💡 Hint

Look for when 'WHERE department_id = 3' is used in the execution_table.

If the subquery returned id = 5 instead of 3, how would the final result set change?

A It would include employees with department_id = 3

B It would include employees with department_id = 5

C It would return all employees

D It would return no employees

💡 Hint

Refer to variable_tracker 'outer_filter' and how it uses the subquery_result.

Concept Snapshot

Nested subqueries run inside outer queries.
The subquery executes first and returns a value.
Outer query uses this value to filter or compare.
Syntax: SELECT ... WHERE column = (SELECT ...);
Useful to break complex queries into parts.

Full Transcript

Nested subqueries work by running the inner query first to get a value. Then the outer query uses that value to filter or find matching rows. For example, to find employees in the Sales department, the subquery finds the Sales department's id. The outer query then selects employees with that department id. The execution flow starts with the outer query, pauses to run the subquery, gets the result, and continues. Variables like subquery_result hold the inner query's output, which the outer query uses to filter data. If the subquery returns no rows, the outer query returns no results. This step-by-step process helps build complex queries in manageable parts.