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.

Practice

1. What does the IN operator do when used with a subquery in MySQL?

easy

A. Checks if a value matches any value returned by the subquery

B. Joins two tables based on a common column

C. Updates values in the main table using subquery results

D. Deletes rows that match the subquery results

Solution

1. Step 1: Understand the IN operator

   The IN operator checks if a value exists within a list of values.

2. Step 2: Role of subquery with IN

   The subquery returns a list of values, and IN checks if the main query's value matches any of these.

3. Final Answer:

   Checks if a value matches any value returned by the subquery -> Option A

4. Quick Check:

   IN with subquery = value match list [OK]

Hint: IN checks if value is in subquery result list [OK]

Common Mistakes:

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

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

easy

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

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

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

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

Solution

1. Step 1: Review correct IN syntax

   The IN operator must be followed by parentheses enclosing the subquery.

2. Step 2: Check each option

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); correctly uses IN with parentheses and a subquery. Others have syntax errors.

3. Final Answer:

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

4. Quick Check:

   IN + (subquery) = correct syntax [OK]

Hint: Use IN followed by parentheses around subquery [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subquery
• Placing IN before column name

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 work in the Sales department

D. Names of employees who do not work in Sales

Solution

1. Step 1: Analyze subquery

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

2. Step 2: Main query filters employees

   The main query selects employee names whose department_id matches any ID from the subquery.

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters IDs; IN matches employees with those IDs [OK]

Common Mistakes:

• Thinking IN causes error with multiple rows
• Ignoring subquery filter condition
• Assuming it returns all employees

4. Identify the error in this query:

SELECT * FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'New York';

medium

A. Subquery returns no rows

B. Incorrect table name 'orders'

C. Using IN instead of EXISTS

D. Missing parentheses around the subquery after IN

Solution

1. Step 1: Check IN operator syntax

   The IN operator requires the subquery to be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around the subquery after IN, causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery after IN -> Option D

4. Quick Check:

   IN needs (subquery) [OK]

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

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS
• Assuming table names cause error

5. You want to find all products that have been ordered by customers from the city 'Chicago'.
Given tables:
products(id, name)
orders(id, product_id, customer_id)
customers(id, city)
Which query correctly uses a subquery with IN to get the product names?

hard

A. SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago'));

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

C. SELECT name FROM products WHERE id IN SELECT product_id FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'Chicago';

D. SELECT name FROM products WHERE id IN (SELECT id FROM customers WHERE city = 'Chicago');

Solution

1. Step 1: Understand the relationships

   Products link to orders by product_id; orders link to customers by customer_id.

2. Step 2: Build nested subqueries

   Inner subquery gets customer IDs from Chicago; middle subquery gets product_ids from orders by those customers; outer query selects product names with those IDs.

3. Final Answer:

   SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago')); -> Option A

4. Quick Check:

   Nested IN subqueries correctly filter products [OK]

Hint: Use nested IN subqueries to filter related tables stepwise [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subqueries
• Using wrong columns in subqueries