Subquery in WHERE clause in SQL - Step-by-Step Execution

Concept Flow - Subquery in WHERE clause

Start Query

↓

Evaluate Subquery

↓

Get Subquery Result

↓

Use Result in WHERE Clause

↓

Filter Main Query Rows

↓

Return Filtered Rows

↓

End Query

The main query runs, but first the subquery inside the WHERE clause runs to get values. Then the main query uses those values to filter its rows.

Execution Sample

SQL

SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'NY'
);

This query finds employees who work in departments located in NY by using a subquery in the WHERE clause.

Execution Table

Step	Action	Subquery Evaluation	Main Query Filtering	Output Rows
1	Start main query	Not started	No filtering yet	All employees
2	Run subquery	SELECT id FROM departments WHERE location = 'NY'	Waiting for subquery result	No output yet
3	Subquery returns department ids	Result: [2, 5]	Use these ids in WHERE clause	No output yet
4	Filter employees WHERE department_id IN (2,5)	Subquery result used	Filter employees with department_id 2 or 5	Employees in dept 2 or 5
5	Return filtered rows	Subquery done	Filtered employees returned	List of employees in NY departments
6	End query	Complete	Complete	Final result set

💡 Subquery completes and main query filters employees based on subquery result, then returns final rows.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
subquery_result	empty	running	[2,5]	[2,5]	[2,5]
filtered_employees	all employees	all employees	all employees	employees with dept_id 2 or 5	employees with dept_id 2 or 5

Key Moments - 2 Insights

Why does the subquery run before the main query filters rows?

Because the main query needs the subquery result to know which department_ids to filter employees by, as shown in execution_table step 3.

What happens if the subquery returns no rows?

Then the WHERE clause condition becomes false for all rows, so no employees are returned. This is implied after step 3 if subquery_result is empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the subquery_result?

A [1, 3]

B [2, 5]

C Empty

D [10, 20]

💡 Hint

Check the 'Subquery Evaluation' column at step 3 in the execution_table.

At which step does the main query start filtering employees?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Main Query Filtering' column to see when filtering begins.

If the subquery returned an empty list, what would be the output rows?

A No employees

B Employees with department_id 0

C All employees

D Employees with department_id NULL

💡 Hint

Refer to key_moments about what happens if subquery returns no rows.

Concept Snapshot

Subquery in WHERE clause:
- Runs subquery first to get values
- Uses those values to filter main query rows
- Syntax: WHERE column IN (subquery)
- Subquery result must be compatible with condition
- If subquery empty, main query returns no rows

Full Transcript

This visual execution shows how a SQL query with a subquery in the WHERE clause runs. First, the subquery runs to find department ids located in NY. Then the main query uses these ids to filter employees who belong to those departments. The execution table traces each step: starting the main query, running the subquery, getting results, filtering employees, and returning the final list. Variables like subquery_result and filtered_employees change as the query progresses. Key moments clarify why the subquery runs first and what happens if it returns no rows. The quiz tests understanding of these steps by asking about subquery results and filtering timing.