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.

Practice

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

easy

A. It checks if a value matches any value returned by the subquery.

B. It updates values in the main query based on the subquery.

C. It deletes rows that are returned by the subquery.

D. It creates a new table from the subquery results.

Solution

1. Step 1: Understand the role of IN operator

   The IN operator compares a value to a list of values and returns true if it matches any of them.

2. Step 2: Understand subquery usage

   The subquery returns a list of values that the main query uses to filter rows with the IN operator.

3. Final Answer:

   It checks if a value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN with subquery = match any value [OK]

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

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with JOIN
• Assuming IN creates new tables

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

easy

A. SELECT * FROM employees WHERE IN department_id (SELECT id 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: 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. Options A, B, and D have syntax errors.

3. Final Answer:

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

4. Quick Check:

   IN syntax = IN (subquery) [OK]

Hint: Use parentheses around subquery after IN [OK]

Common Mistakes:

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

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in the Sales department.

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

C. All employee names regardless of department.

D. An error because subquery returns multiple rows.

Solution

1. Step 1: Understand subquery filtering

   The subquery selects dept_id values where dept_name is 'Sales'.

2. Step 2: Main query filters employees

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

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales dept_id [OK]

Hint: IN filters rows matching subquery values [OK]

Common Mistakes:

• Thinking it returns employees outside Sales
• Assuming subquery causes error with multiple rows
• Ignoring subquery filtering condition

4. Identify the error in this query:

SELECT emp_id FROM Employees WHERE dept_id IN SELECT dept_id FROM Departments;

medium

A. Subquery should be in the FROM clause.

B. Using IN instead of EXISTS.

C. dept_id should be compared with = not IN.

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, causing a 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
• Using = instead of IN for multiple values

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

hard

A. SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

B. SELECT name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

C. SELECT name FROM Customers WHERE customer_id IN (SELECT product_id FROM Products WHERE category = 'Electronics');

D. SELECT name FROM Customers WHERE customer_id IN (SELECT order_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

Solution

1. Step 1: Understand the relationships

   Customers link to Orders by customer_id; Orders link to Products by product_id.

2. Step 2: Analyze the nested subqueries

   The innermost subquery selects product_ids in 'Electronics'. The middle subquery selects customer_ids from Orders with those product_ids. The outer query selects customer names with those customer_ids.

3. Final Answer:

   SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics')); -> Option A

4. Quick Check:

   Nested IN filters customers by Electronics orders [OK]

Hint: Use nested IN for multi-level filtering [OK]

Common Mistakes:

• Using = instead of IN for multiple customer_ids
• Comparing customer_id with product_id or order_id
• Missing nested subquery for product filtering