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.