Subquery with IN operator in SQL - Step-by-Step Execution

Concept Flow - Subquery with IN operator

Start Query

↓

Execute Subquery

↓

Get List of Values

↓

Main Query uses IN operator

↓

Filter rows where column IN (subquery results)

↓

Return Filtered Rows

↓

End Query

The main query runs after the subquery returns a list of values. The IN operator filters rows matching any value from that list.

Execution Sample

SQL

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.

Execution Table

Step	Action	Subquery Result	Main Query Filter	Output Rows
1	Run subquery: SELECT id FROM departments WHERE location = 'NY'	[2, 4]	N/A	N/A
2	Main query filters employees WHERE department_id IN (2,4)	N/A	department_id IN (2,4)	Employees with dept 2 or 4
3	Return filtered employee names	N/A	N/A	Alice, Bob, Carol
4	End query	N/A	N/A	Final result set returned

💡 Query ends after filtering employees whose department_id matches any id from the subquery.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
subquery_result	empty	[2,4]	[2,4]	[2,4]
main_query_filter	none	none	department_id IN (2,4)	department_id IN (2,4)
output_rows	empty	empty	partial	['Alice', 'Bob', 'Carol']

Key Moments - 3 Insights

Why does the main query wait for the subquery to finish before filtering?

Because the IN operator needs the list of values from the subquery to know which rows to keep, as shown in execution_table step 1 and 2.

Can the subquery return multiple values for the IN operator?

Yes, the subquery can return many values, and the main query filters rows matching any of those values, as seen in subquery_result [2,4].

What happens if the subquery returns no rows?

The IN list is empty, so no rows match the condition, and the main query returns an empty result set.

Visual Quiz - 3 Questions

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

A ['NY']

B [1, 3]

C [2, 4]

D Empty

💡 Hint

Check the 'Subquery Result' column in execution_table row for step 1.

At which step does the main query apply the filter using the IN operator?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the 'Main Query Filter' column in execution_table to find when filtering happens.

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

A All employees

B No employees

C Only employees with department_id 0

D Error

💡 Hint

Refer to key_moments explanation about empty subquery results.

Concept Snapshot

Subquery with IN operator:
- Subquery runs first, returns list of values.
- Main query filters rows where column IN that list.
- Syntax: WHERE column IN (subquery)
- Returns rows matching any subquery value.
- If subquery empty, no rows returned.

Full Transcript

This visual execution shows how a SQL query with a subquery using the IN operator runs step-by-step. First, the subquery executes and returns a list of department IDs located in NY. Then, the main query filters employees whose department_id matches any ID from that list. The output is the names of employees working in those departments. The execution table tracks each step, showing the subquery result, the filter applied, and the final output rows. Key moments clarify why the main query waits for the subquery and what happens if the subquery returns no rows. The quiz tests understanding of these steps by referencing the execution table and variable changes.