Subqueries and nested queries in DBMS Theory - Step-by-Step Execution

Concept Flow - Subqueries and nested queries

Start Outer Query

↓

Detect Subquery

↓

Execute Subquery

↓

Return Subquery Result

↓

Use Result in Outer Query

↓

Complete Outer Query Execution

The outer query detects a subquery, runs it first, then uses its result to complete the main query.

Execution Sample

DBMS Theory

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.

Analysis Table

Step	Action	Query Part	Result/Value
1	Start outer query	SELECT name FROM employees WHERE department_id = (...)	Waiting for subquery result
2	Detect subquery	SELECT id FROM departments WHERE name = 'Sales'	Subquery identified
3	Execute subquery	SELECT id FROM departments WHERE name = 'Sales'	Returns id = 3
4	Use subquery result	WHERE department_id = 3	Filter employees with department_id = 3
5	Execute outer query	SELECT name FROM employees WHERE department_id = 3	Returns employee names in Sales
6	End	Query complete	Final list of employee names in Sales

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

State Tracker

Variable	Start	After Step 3	After Step 5	Final
subquery_result	None	3	3	3
outer_query_filter	None	None	department_id = 3	department_id = 3
final_output	None	None	List of employee names	List of employee names

Key Insights - 3 Insights

Why does the subquery run before the outer query filters employees?

Because the outer query needs the subquery's result to know which department_id to filter by, as shown in execution_table step 3 and 4.

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 or need 'IN' instead.

What happens if the subquery returns no rows?

The outer query's filter becomes department_id = NULL, which matches no employees, so the final output is empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what value does the subquery return?

A department_id

B Sales

C 3

D NULL

💡 Hint

Check the 'Result/Value' column at step 3 in the execution_table.

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

A Step 4

B Step 1

C Step 2

D Step 6

💡 Hint

Look for when 'Use subquery result' happens in the execution_table.

If the subquery returned no rows, what would the final output be according to variable_tracker?

A List of all employees

B Empty list

C Error message

D List of departments

💡 Hint

Refer to key_moments explanation about subquery returning no rows and its effect on final_output.

Concept Snapshot

Subqueries run inside outer queries.
The subquery executes first and returns a value.
Outer query uses this value to filter or compare.
Subqueries can return single or multiple values.
Use '=' for single value, 'IN' for multiple.
If subquery returns no rows, outer query may return empty result.

Full Transcript

Subqueries and nested queries work by running the inner query first to get a result. This result is then used by the outer query to filter or select data. For example, to find employees in the Sales department, the subquery finds the Sales department's id. Then the outer query uses this id to find matching employees. The execution steps show the subquery running first, returning id 3, then the outer query filtering employees with department_id 3. Variables track the subquery result and how it is used. Common confusions include why the subquery runs first, what happens if it returns multiple or no values. Quizzes test understanding of these steps and results. Remember, subqueries must return appropriate values for the outer query to work correctly.