Subqueries with IN operator in MySQL - Step-by-Step Execution

Concept Flow - Subqueries with IN operator

Start Query

↓

Execute Subquery

↓

Get List of Values

↓

Main Query: Check if value IN list

↓

Return Matching Rows

↓

End Query

The main query runs after the subquery returns a list of values. It checks if a column's value is in that list and returns matching rows.

Execution Sample

MySQL

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

Find employees who work in departments located in NY by using a subquery with IN operator.

Execution Table

Step	Action	Subquery Result	Main Query Condition	Rows Returned
1	Execute subquery: SELECT id FROM departments WHERE location = 'NY'	[2, 4]	N/A	N/A
2	Check each employee's department_id IN [2,4]	N/A	department_id IN (2,4)	Evaluate for each employee
3	Employee 1: department_id=1	N/A	1 IN (2,4)? False	No
4	Employee 2: department_id=2	N/A	2 IN (2,4)? True	Yes, include
5	Employee 3: department_id=3	N/A	3 IN (2,4)? False	No
6	Employee 4: department_id=4	N/A	4 IN (2,4)? True	Yes, include
7	Employee 5: department_id=2	N/A	2 IN (2,4)? True	Yes, include
8	Return all matching employees	N/A	N/A	Employees 2,4,5
9	End query	N/A	N/A	Query complete

💡 All employees checked; only those with department_id in subquery result are returned.

Variable Tracker

Variable	Start	After Step 1	After Step 8	Final
subquery_result	empty	[2,4]	[2,4]	[2,4]
employee_department_id	varies	N/A	N/A	N/A
matching_employees	empty	empty	[Employee 2, Employee 4, Employee 5]	[Employee 2, Employee 4, Employee 5]

Key Moments - 3 Insights

Why does the subquery run before the main query checks the IN condition?

Because the main query needs the list of values from the subquery to compare each employee's department_id. This is shown in execution_table step 1 where the subquery result is obtained first.

What happens if the subquery returns an empty list?

Then the IN condition will always be false, so no rows from the main query will match. This is implied by the logic in steps 2-7 where the department_id must be in the subquery result.

Can the subquery return duplicate values and how does that affect the IN condition?

Duplicates in the subquery result do not affect the IN condition because IN checks for presence, not count. The execution_table shows the subquery result as a list, but duplicates do not change the matching logic.

Visual Quiz - 3 Questions

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

A [1, 3]

B [2, 4]

C [3, 5]

D [1, 2]

💡 Hint

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

At which step does the main query decide to include Employee 4 in the result?

A Step 3

B Step 5

C Step 6

D Step 7

💡 Hint

Look at the 'Rows Returned' column for Employee 4 in the execution_table.

If the subquery returned an empty list, what would be the final matching_employees value?

A No employees

B Only employees with department_id 1

C All employees

D Only employees with department_id 2

💡 Hint

Refer to key_moments explanation about empty subquery results.

Concept Snapshot

Subqueries with IN operator:
- Subquery runs first and returns a list of values.
- Main query checks if column values are IN that list.
- Returns rows matching any value in the list.
- If subquery returns empty, main query returns no rows.
- Duplicates in subquery do not affect matching.

Full Transcript

This visual execution shows how a SQL query with a subquery using the IN operator works. First, the subquery runs and returns a list of department IDs located in NY. Then, the main query checks each employee's department_id to see if it is in that list. Only employees with matching department IDs are returned. The execution table traces each step, showing the subquery result and how each employee is checked. Key moments clarify why the subquery runs first and what happens if it returns no results. The quiz tests understanding of the subquery result, matching steps, and empty subquery cases.