Subqueries with IN operator in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the IN operator do in a SQL query?

The IN operator checks if a value matches any value in a list or the result of a subquery. It helps filter rows where a column's value is in a set of values.

beginner

Write a simple example of a subquery using the IN operator.

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
This finds employees who work in departments located in New York.

intermediate

Why use a subquery with IN instead of multiple OR conditions?

Using IN with a subquery is cleaner and easier to read. It also adapts automatically if the list of values changes, unlike hardcoded OR conditions.

intermediate

Can the subquery inside IN return multiple columns?

No, the subquery inside an IN operator must return only one column. This column provides the list of values to compare against.

intermediate

What happens if the subquery inside IN returns no rows?

If the subquery returns no rows, the IN condition evaluates to false for all rows, so no rows match the condition.

What does this query do?
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Paris');

A Finds customers who ordered from Paris

B Finds customers who live anywhere except Paris

C Finds orders placed in Paris

D Finds orders from customers who live in Paris

The subquery finds customer IDs from Paris, and the main query finds orders from those customers.

Which is true about the subquery in an IN operator?

A It can return multiple columns

B It must return exactly one column

C It cannot use WHERE clause

D It must return only one row

The subquery must return one column with multiple rows allowed, to provide values for the IN list.

If the subquery returns no rows, what is the result of the IN condition?

A False for all rows

B True for all rows

C Error in query

D Returns NULL

No values to match means the IN condition is false for every row.

Which is a benefit of using IN with a subquery?

A Allows multiple columns in subquery

B Runs faster than JOIN always

C Simplifies queries with many OR conditions

D Returns duplicate rows

IN with subquery makes queries easier to write and read compared to many OR conditions.

What does this query return?
SELECT product_name FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');

A Products in Electronics category

B Categories named Electronics

C All products

D Products not in Electronics category

It returns products whose category matches the Electronics category IDs.

Explain how the IN operator works with a subquery in SQL.

Think about how you check if something is in a list of items.

Describe a real-life example where using a subquery with IN is helpful.

Imagine you want to find people connected to a group defined by another table.

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