Subqueries with IN operator in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees working in departments with more than 3 employees

Given the tables employees(emp_id, emp_name, dept_id) and departments(dept_id, dept_name), which query returns the names of employees who work in departments that have more than 3 employees?

MySQL

SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 3);

A SELECT emp_name FROM employees WHERE dept_id = ANY (SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 3);

B SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 3);

C SELECT emp_name FROM employees WHERE dept_id NOT IN (SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 3);

D SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_id > 3);

💡 Hint

Use a subquery with GROUP BY and HAVING to find departments with more than 3 employees, then filter employees by those departments.

Option B correctly uses a subquery to find departments with more than 3 employees and filters employees working in those departments. Option B uses = ANY which is equivalent but less common. Option B selects employees NOT in those departments, which is the opposite. Option B filters departments by dept_id > 3, which is unrelated to employee count.

❓ query_result

intermediate

List products sold in categories with at least 5 products

Given tables products(product_id, product_name, category_id) and categories(category_id, category_name), which query returns the names of products that belong to categories having 5 or more products?

MySQL

SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM products GROUP BY category_id HAVING COUNT(*) >= 5);

A SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM products GROUP BY category_id HAVING COUNT(*) >= 5);

B SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_id >= 5);

C SELECT product_name FROM products WHERE category_id NOT IN (SELECT category_id FROM products GROUP BY category_id HAVING COUNT(*) >= 5);

D SELECT product_name FROM products WHERE category_id = (SELECT category_id FROM products GROUP BY category_id HAVING COUNT(*) >= 5);

💡 Hint

Use a subquery with GROUP BY and HAVING to find categories with at least 5 products, then filter products by those categories.

Option A correctly uses a subquery to find categories with 5 or more products and filters products accordingly. Option A filters categories by category_id >= 5, which is unrelated to product count. Option A selects products NOT in those categories, which is the opposite. Option A uses = with a subquery returning multiple rows, causing an error.

📝 Syntax

advanced

Identify the syntax error in the subquery with IN operator

Which option contains a syntax error in the use of the IN operator with a subquery?

MySQL

SELECT customer_name FROM customers WHERE customer_id IN SELECT customer_id FROM orders WHERE order_date > '2023-01-01';

A SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

B ;)'10-10-3202' > etad_redro EREHW sredro MORF di_remotsuc TCELES( NI di_remotsuc EREHW sremotsuc MORF eman_remotsuc TCELES

C SELECT customer_name FROM customers WHERE customer_id IN SELECT customer_id FROM orders WHERE order_date > '2023-01-01';

D SELECT customer_name FROM customers WHERE customer_id = ANY (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

💡 Hint

Remember that the subquery must be enclosed in parentheses when used with IN.

Option C is missing parentheses around the subquery after IN, causing a syntax error. Options A, B, and C correctly use parentheses or valid syntax.

❓ optimization

advanced

Optimize query using IN operator with subquery for better performance

Which query is generally more efficient in MySQL when selecting employees who belong to departments located in 'New York'?

MySQL

Tables: employees(emp_id, emp_name, dept_id), departments(dept_id, dept_name, location)

A SELECT emp_name FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'New York');

B SELECT emp_name FROM employees WHERE dept_id = ANY (SELECT dept_id FROM departments WHERE location = 'New York');

C SELECT emp_name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.dept_id = employees.dept_id AND location = 'New York');

D SELECT emp_name FROM employees JOIN departments ON employees.dept_id = departments.dept_id WHERE location = 'New York';

💡 Hint

Consider how JOINs can be optimized by the database engine compared to subqueries.

Option D uses a JOIN which is generally more efficient in MySQL for this case because it allows the optimizer to use indexes and avoid repeated subquery execution. Options A, B, and D use subqueries which may be less efficient.

🧠 Conceptual

expert

Understanding NULL behavior with IN operator and subqueries

Consider the query:
SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics' OR category_name IS NULL);
What will happen if the subquery returns NULL values in the list?

A The query will return products only where category_id matches non-NULL category_ids from the subquery; rows with NULL category_id will not match.

B The query will return no products because NULL in the subquery causes the IN condition to evaluate to UNKNOWN for all rows.

C The query will return all products because NULL is treated as a match in IN operator.

D The query will raise a runtime error due to NULL values in the subquery.

💡 Hint

Remember how SQL treats NULL in IN conditions: NULL means unknown, not true or false.

Option A is correct because NULL values in the subquery do not match any category_id in products. The IN operator returns TRUE only if the value matches a non-NULL value in the list. NULL causes the condition to be UNKNOWN, so those rows are excluded. Option A is incorrect because NULL is not treated as a match. Option A is incorrect because only rows with NULL category_id fail to match, not all rows. Option A is incorrect because NULLs do not cause runtime errors.

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