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');<br>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?<br>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?<br>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.