Subqueries in WHERE clause in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find employees with salary above department average

Given a table employees with columns id, name, department_id, and salary, which query returns all employees whose salary is greater than the average salary of their department?

A SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B SELECT name FROM employees WHERE salary > AVG(salary) FROM employees GROUP BY department_id;

C SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = employees.department_id);

💡 Hint

Think about how to compare each employee's salary to the average salary of their own department using a subquery.

Option A correctly uses a correlated subquery to calculate the average salary for the employee's department and compares the employee's salary to it. Option A is invalid syntax. Option A compares salary to the average salary of all employees, not per department. Option A compares salary to all salaries in the department, which is not the average.

❓ query_result

intermediate

List products with price higher than any product in category 5

Given a table products with columns product_id, category_id, and price, which query returns products whose price is higher than the price of every product in category 5?

A SELECT product_id FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 5);

B SELECT product_id FROM products WHERE price > (SELECT MIN(price) FROM products WHERE category_id = 5);

C SELECT product_id FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 5);

D SELECT product_id FROM products WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 5);

💡 Hint

Use a subquery to compare each product's price to all prices in category 5.

Option C correctly uses ALL to ensure the product's price is greater than every price in category 5. Option C uses ANY, which means greater than at least one price, so it returns more rows. Option C compares to the maximum price, which is equivalent to ALL but uses a scalar subquery; however, the question asks for a subquery in WHERE clause with ALL. Option C compares to the minimum price, which is incorrect.

📝 Syntax

advanced

Identify the syntax error in subquery usage

Which option contains a syntax error when using a subquery in the WHERE clause?

A SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Paris');

B SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE city = 'Paris');

C SELECT * FROM orders WHERE customer_id = ANY (SELECT id FROM customers WHERE city = 'Paris');

D SELECT * FROM orders WHERE customer_id = IN (SELECT id FROM customers WHERE city = 'Paris');

💡 Hint

Check the placement of the IN keyword in the WHERE clause.

Option D is invalid syntax because it uses '= IN' together, which is not allowed. IN is a standalone operator and should not be combined with '='. Options A, B, and D are syntactically valid.

❓ optimization

advanced

Optimize query with subquery in WHERE clause

You have a query that selects employees with salaries above the average salary of their department using a subquery in the WHERE clause. Which rewritten query is more efficient by avoiding repeated subquery execution?

A SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

B SELECT e.name FROM employees e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

C SELECT name FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

D SELECT name FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

💡 Hint

Try to calculate averages once per department and join instead of using a correlated subquery.

Option B precomputes average salaries per department and joins, avoiding repeated subquery execution for each employee. Option B uses a correlated subquery executed for each row, which is less efficient. Options C and D misuse ALL and ANY with aggregate results and do not produce correct filtering.

🧠 Conceptual

expert

Understanding NULL behavior in subqueries within WHERE clause

Consider a table orders with columns order_id, customer_id, and shipped_date which can be NULL if not shipped. Which query correctly returns all orders where the customer has at least one shipped order?

A SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

B SELECT * FROM orders WHERE shipped_date IS NOT NULL AND customer_id IN (SELECT customer_id FROM orders);

C SELECT * FROM orders WHERE customer_id = ANY (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

D SELECT * FROM orders WHERE customer_id = ALL (SELECT customer_id FROM orders WHERE shipped_date IS NOT NULL);

💡 Hint

Think about how to find customers who have shipped orders and then select all their orders.

Option A correctly selects orders where the customer has at least one shipped order by checking if the customer_id is in the set of customers with shipped orders. Option A unnecessarily filters orders to shipped ones and includes all customers, which is redundant. Option A is valid but equivalent to A; however, = ANY is less common and can be confusing. Option A requires the customer_id to match all shipped orders, which is logically incorrect.

Practice

1. What does a subquery in the WHERE clause do in MySQL?

easy

A. Updates data in multiple tables at once

B. Filters rows based on the result of another query

C. Creates a new table in the database

D. Deletes rows from a table

Solution

1. Step 1: Understand the role of subqueries in WHERE clause

   A subquery inside a WHERE clause is used to filter rows by comparing values to the result of another query.

2. Step 2: Differentiate from other SQL operations

   Creating tables, updating, or deleting data are not done by subqueries in WHERE; they are separate SQL commands.

3. Final Answer:

   Filters rows based on the result of another query -> Option B

4. Quick Check:

   Subquery in WHERE = filter rows [OK]

Hint: Subqueries in WHERE filter rows using another query's result [OK]

Common Mistakes:

• Thinking subqueries create or modify tables
• Confusing subqueries with JOINs
• Assuming subqueries always return multiple rows

2. Which of the following is the correct syntax for using a subquery in the WHERE clause to find employees with salaries greater than the average salary?

easy

A. SELECT * FROM employees WHERE salary = (SELECT salary FROM employees);

B. SELECT * FROM employees WHERE salary IN SELECT AVG(salary) FROM employees;

C. SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

D. SELECT * FROM employees WHERE salary > ALL SELECT AVG(salary) FROM employees;

Solution

1. Step 1: Check subquery syntax in WHERE clause

   The subquery must be enclosed in parentheses and return a single value for comparison with salary.

2. Step 2: Validate each option's syntax

   Only SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); correctly uses parentheses around a single-value subquery. Other options miss parentheses, misuse IN with a single value, use = with a multi-row subquery, or misuse ALL.

3. Final Answer:

   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); -> Option C

4. Quick Check:

   Correct subquery syntax uses parentheses [OK]

Hint: Always enclose subqueries in parentheses in WHERE clause [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Using IN with single-value subquery incorrectly
• Comparing to multiple rows without IN or EXISTS

3. Given the tables orders(order_id, customer_id, amount) and customers(customer_id, city), what will this query return?

SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Paris');

medium

A. All order IDs from customers who live in Paris

B. All order IDs from customers who do not live in Paris

C. All customer IDs from Paris

D. Syntax error due to missing JOIN

Solution

1. Step 1: Understand the subquery result

   The subquery selects customer IDs where city is 'Paris'. This returns a list of customer IDs living in Paris.

2. Step 2: Apply the subquery result in the main query

   The main query selects order IDs where the customer_id is in that list, so orders from Paris customers are returned.

3. Final Answer:

   All order IDs from customers who live in Paris -> Option A

4. Quick Check:

   IN with subquery filters matching customer IDs [OK]

Hint: IN subquery filters rows matching any returned value [OK]

Common Mistakes:

• Confusing orders with customers
• Assuming JOIN is required for this filter
• Thinking it returns customer IDs instead of order IDs

4. Identify the error in this query:

SELECT name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location = 'NY' AND active = 1);

Assuming multiple departments can be active in NY.

medium

A. No error, query is correct

B. Missing GROUP BY clause in subquery

C. Incorrect table name 'departments'

D. Subquery returns multiple rows but '=' expects one value

Solution

1. Step 1: Analyze subquery result cardinality

   The subquery may return multiple department_id values because multiple departments can be active in NY.

2. Step 2: Check operator compatibility

   The '=' operator expects a single value, but the subquery returns multiple rows, causing an error.

3. Final Answer:

   Subquery returns multiple rows but '=' expects one value -> Option D

4. Quick Check:

   Use IN for multiple values, '=' for single value [OK]

Hint: Use IN if subquery returns multiple rows, not '=' [OK]

Common Mistakes:

• Using '=' with subquery returning multiple rows
• Assuming subquery always returns one row
• Ignoring error messages about subquery results

5. You want to find all products that have never been ordered. Given tables products(product_id, name) and orders(order_id, product_id), which query correctly uses a subquery in the WHERE clause to achieve this?

hard

A. SELECT name FROM products WHERE product_id NOT IN (SELECT product_id FROM orders);

B. SELECT name FROM products WHERE product_id IN (SELECT product_id FROM orders);

C. SELECT name FROM products WHERE product_id = (SELECT product_id FROM orders);

D. SELECT name FROM products WHERE product_id > (SELECT product_id FROM orders);

Solution

1. Step 1: Understand the goal

   We want products that have no matching product_id in orders, meaning they were never ordered.

2. Step 2: Use NOT IN with subquery

   Using NOT IN with a subquery selecting product_ids from orders filters products not present in orders.

3. Final Answer:

   SELECT name FROM products WHERE product_id NOT IN (SELECT product_id FROM orders); -> Option A

4. Quick Check:

   NOT IN filters out matching values [OK]

Hint: Use NOT IN with subquery to find missing matches [OK]

Common Mistakes:

• Using IN instead of NOT IN for exclusion
• Using '=' with subquery returning multiple rows
• Using comparison operators incorrectly with subqueries