Subqueries in WHERE clause in MySQL - Step-by-Step Execution

Concept Flow - Subqueries 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 by first executing the subquery inside the WHERE clause, then uses that result to filter rows.

Execution Sample

MySQL

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

Select employee names who work in departments located in NY by using a subquery in the WHERE clause.

Execution Table

Step	Action	Evaluation	Result
1	Execute subquery: SELECT id FROM departments WHERE location = 'NY'	Find department ids with location 'NY'	[2, 4]
2	Use subquery result in main query WHERE clause	Filter employees with department_id IN [2, 4]	Employees with department_id 2 or 4
3	Return filtered employee names	List names of employees in departments 2 or 4	['Alice', 'Bob', 'Diana']
4	End query	All matching rows returned	Query complete

💡 Subquery result used to filter main query rows; no more rows to process.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
subquery_result	null	[2, 4]	[2, 4]	[2, 4]
filtered_employees	null	null	['Alice', 'Bob', 'Diana']	['Alice', 'Bob', 'Diana']

Key Moments - 3 Insights

Why does the subquery run before filtering employees?

Because the main query needs the list of department ids from the subquery to know which employees to select, as shown in execution_table step 1 and 2.

Can the subquery return multiple values for the WHERE clause?

Yes, the subquery can return multiple values, which is why IN is used in the WHERE clause to match any of those values, as seen in execution_table step 1 result [2, 4].

What happens if the subquery returns no results?

If the subquery returns an empty list, the main query filters on an empty set, so no rows match and the result is empty. This is implied by the filtering step in execution_table.

Visual Quiz - 3 Questions

Look at the execution table, what is the subquery result after step 1?

A [2, 4]

B ['Alice', 'Bob', 'Diana']

C [1, 3]

D null

💡 Hint

Check the 'Result' column in execution_table row with Step 1.

At which step does the main query filter employees using the subquery result?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column describing filtering in execution_table.

If the subquery returned an empty list, what would be the filtered_employees value after step 2?

A ['Alice', 'Bob', 'Diana']

B null

C []

D All employees

💡 Hint

Refer to variable_tracker for filtered_employees and think about filtering with no matching department ids.

Concept Snapshot

Subqueries in WHERE clause:
- Subquery runs first and returns values.
- Main query uses these values to filter rows.
- Use IN for multiple values from subquery.
- If subquery empty, main query returns no rows.
- Enables dynamic filtering based on related data.

Full Transcript

This visual execution shows how a subquery inside a WHERE clause works. First, the subquery runs to find department ids located in NY. The result is [2, 4]. Then, the main query uses this list to filter employees whose department_id is in that list. The filtered employees are Alice, Bob, and Diana. Finally, these names are returned as the query result. If the subquery returned no ids, no employees would match and the result would be empty. This step-by-step flow helps understand how subqueries provide dynamic filtering in SQL.