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.

Practice

1. What does the IN keyword do in a SQL query when used with a subquery in the WHERE clause?

easy

A. It filters rows where a column's value matches any value returned by the subquery.

B. It joins two tables together based on matching columns.

C. It updates rows in the table with values from the subquery.

D. It deletes rows that do not exist in the subquery.

Solution

1. Step 1: Understand the role of IN with subqueries

   The IN keyword checks if a value exists in a list of values returned by the subquery.

2. Step 2: Identify the filtering effect

   Rows are kept only if their column value matches any value from the subquery's result.

3. Final Answer:

   It filters rows where a column's value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN filters rows by subquery values [OK]

Hint: Think of IN as 'is in this list' from the subquery [OK]

Common Mistakes:

• Confusing IN with JOIN
• Thinking IN updates or deletes rows
• Assuming IN returns a single value only

2. Which of the following is the correct syntax to use a subquery with IN in PostgreSQL?

easy

A. SELECT * FROM employees WHERE department_id IN (SELECT * FROM departments);

B. SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

C. SELECT * FROM employees WHERE department_id IN SELECT id FROM departments;

D. SELECT * FROM employees WHERE department_id = IN (SELECT id FROM departments);

Solution

1. Step 1: Check the correct use of parentheses

   The subquery must be enclosed in parentheses after IN.

2. Step 2: Verify the subquery returns a single column

   The subquery should return one column of values to compare against.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); -> Option B

4. Quick Check:

   Subquery in parentheses with single column [OK]

Hint: Always put subquery inside parentheses after IN [OK]

Common Mistakes:

• Using = IN instead of just IN
• Omitting parentheses around subquery
• Selecting multiple columns in subquery

3. Given the tables:
employees(id, name, department_id)
departments(id, name)
What will this query return?

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

medium

A. An error because subquery returns multiple rows.

B. Names of all employees regardless of department.

C. Names of employees who do not work in Sales.

D. Names of employees who work in the Sales department.

Solution

1. Step 1: Understand the subquery filtering departments

   The subquery selects department IDs where the department name is 'Sales'.

2. Step 2: Filter employees by matching department IDs

   The main query returns employee names whose department_id matches those IDs.

3. Final Answer:

   Names of employees who work in the Sales department. -> Option D

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters departments, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing NOT IN with IN
• Assuming subquery causes error with multiple rows

4. Identify the error in this query:

SELECT * FROM employees WHERE department_id IN SELECT id FROM departments WHERE name = 'HR';

medium

A. Missing parentheses around the subquery after IN.

B. Using IN instead of EXISTS.

C. Subquery returns multiple columns.

D. department_id cannot be compared with id.

Solution

1. Step 1: Check syntax for subquery with IN

   The subquery must be enclosed in parentheses after the IN keyword.

2. Step 2: Identify missing parentheses causing syntax error

   The query lacks parentheses around the subquery, causing invalid syntax.

3. Final Answer:

   Missing parentheses around the subquery after IN. -> Option A

4. Quick Check:

   Subquery must be in parentheses after IN [OK]

Hint: Always wrap subqueries in parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS syntax
• Assuming subquery returns multiple columns

5. You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
customers(id, name)
orders(id, customer_id, product_id)
products(id, name, category)
Which query correctly uses a subquery with IN to get these customers?

hard

A. SELECT name FROM customers WHERE id IN (SELECT id FROM products WHERE category = 'Electronics');

B. SELECT name FROM customers WHERE id = IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

C. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

D. SELECT name FROM customers WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM products WHERE category = 'Electronics'));

Solution

1. Step 1: Identify the correct subquery for products in 'Electronics'

   The innermost subquery selects product IDs where category is 'Electronics'.

2. Step 2: Use these product IDs to find matching orders

   The middle subquery selects customer IDs from orders with those product IDs.

3. Step 3: Select customer names whose IDs match those customer IDs

   The main query selects customer names where their ID is in the list from the orders subquery.

4. Final Answer:

   SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics')); -> Option C

5. Quick Check:

   Nested IN subqueries filter customers by product category [OK]

Hint: Use nested IN subqueries to filter step-by-step [OK]

Common Mistakes:

• Using = IN instead of IN
• Mixing up customer_id and product_id columns
• Selecting wrong columns in subqueries