Subqueries in WHERE with IN in PostgreSQL - Step-by-Step Execution

Concept Flow - Subqueries in WHERE with IN

Start Query

↓

Execute Subquery

↓

Get List of Values

↓

Main Query WHERE checks if value IN list

↓

Include

↓

Return Rows

↓

End

The main query runs a subquery first to get a list of values. Then it checks each row if a column's value is in that list. Rows matching are returned.

Execution Sample

PostgreSQL

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 inside WHERE with IN.

Execution Table

Step	Action	Subquery Result	Main Query Row	Condition Check	Include Row?
1	Run subquery	[1, 3]	N/A	N/A	N/A
2	Check employee 1	N/A	department_id=1	1 IN [1,3] = True	Yes
3	Check employee 2	N/A	department_id=2	2 IN [1,3] = False	No
4	Check employee 3	N/A	department_id=3	3 IN [1,3] = True	Yes
5	Check employee 4	N/A	department_id=4	4 IN [1,3] = False	No
6	End	N/A	N/A	N/A	N/A

💡 All employees checked; rows included only if department_id is in subquery result [1,3].

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
subquery_result	empty	[1,3]	[1,3]	[1,3]	[1,3]	[1,3]	[1,3]
current_employee_department_id	N/A	N/A	1	2	3	4	N/A
include_row	N/A	N/A	Yes	No	Yes	No	N/A

Key Moments - 3 Insights

Why do we run the subquery first before checking each employee?

The subquery runs first to get the list of department IDs in NY. Then the main query checks if each employee's department_id is in that list (see execution_table step 1).

What happens if an employee's department_id is not in the subquery result?

If the department_id is not in the list, the condition is false and that employee is excluded (see execution_table steps 3 and 5).

Can the subquery return multiple values?

Yes, the subquery returns a list of values (like [1,3]) which the IN operator uses to check membership (see execution_table step 1).

Visual Quiz - 3 Questions

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

A [1, 3]

B [2, 4]

C [1, 2, 3]

D []

💡 Hint

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

At which step does the condition check fail for an employee?

A Step 2

B Step 3

C Step 4

D Step 6

💡 Hint

Look at the 'Condition Check' and 'Include Row?' columns in execution_table.

If the subquery returned [2,4] instead, which employee would be included at step 3?

A Employee with department_id=1

B Employee with department_id=3

C Employee with department_id=2

D Employee with department_id=4

💡 Hint

Refer to variable_tracker for department_id values and subquery_result.

Concept Snapshot

Subqueries in WHERE with IN:
- Subquery runs first, returns list of values.
- Main query checks if column value is IN that list.
- Rows matching condition are included.
- Useful to filter based on related table values.
- Syntax: WHERE column IN (subquery).

Full Transcript

This visual execution shows how a SQL query with a subquery in the WHERE clause using IN 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. If yes, the employee's name is included in the result. The execution table traces each step: running the subquery, checking each employee, and deciding inclusion. The variable tracker shows how the subquery result and current employee's department_id change during execution. Key moments clarify why the subquery runs first, what happens when the condition fails, and that the subquery can return multiple values. The quiz tests understanding of the subquery result, condition checks, and how changing the subquery affects included rows.