Subqueries with EXISTS in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of EXISTS with matching subquery

Given the tables employees and departments, what is the output of the following query?

SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.location = 'New York');

MySQL

CREATE TABLE employees (id INT, name VARCHAR(50), department_id INT);
CREATE TABLE departments (id INT, location VARCHAR(50));
INSERT INTO employees VALUES (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 10);
INSERT INTO departments VALUES (10, 'New York'), (20, 'Chicago');

A No rows returned

B Bob

C Alice, Bob, Charlie

D Alice, Charlie

💡 Hint

EXISTS returns true if the subquery finds any matching row.

The subquery checks if the employee's department is located in New York. Departments with id 10 are in New York, so employees Alice and Charlie match.

📝 Syntax

intermediate

Identify the syntax error in EXISTS subquery

Which option contains a syntax error in the use of EXISTS in this query?

SELECT name FROM employees WHERE EXISTS (SELECT * FROM departments WHERE id = department_id);

A SELECT name FROM employees WHERE EXISTS (SELECT * FROM departments WHERE id = department_id);

B SELECT name FROM employees WHERE EXISTS SELECT * FROM departments WHERE id = department_id;

C SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = department_id);

D SELECT name FROM employees WHERE EXISTS (SELECT id FROM departments WHERE id = department_id);

💡 Hint

EXISTS requires a subquery enclosed in parentheses.

Option B is missing parentheses around the subquery after EXISTS, causing a syntax error.

❓ optimization

advanced

Optimizing EXISTS subquery for performance

Which query is generally more efficient when checking existence of related rows in orders for each customers row?

A) SELECT c.id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

B) SELECT c.id FROM customers c JOIN orders o ON o.customer_id = c.id;

A Query B is more efficient because it returns fewer rows.

B Both queries have the same performance in all cases.

C Query A is more efficient because EXISTS stops at first match per customer.

D Query B is more efficient because JOIN is faster than EXISTS.

💡 Hint

EXISTS stops searching after finding the first matching row.

EXISTS returns true as soon as it finds one matching row, so it can be faster than JOIN which may return multiple rows per customer.

🔧 Debug

advanced

Why does this EXISTS query return no rows?

Given the tables products and sales, why does this query return no rows?

SELECT p.name FROM products p WHERE EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.id AND s.quantity > 1000);

Data:
products: (1, 'Pen'), (2, 'Notebook')
sales: (1, 500), (2, 800)

A No sales have quantity greater than 1000, so EXISTS is false for all products.

B The subquery is missing a GROUP BY clause causing no matches.

C The products table has no matching ids with sales table.

D The query syntax is invalid causing no output.

💡 Hint

EXISTS returns true only if the subquery finds rows matching all conditions.

The subquery filters sales with quantity > 1000, but none exist, so EXISTS returns false for all products, resulting in no rows.

🧠 Conceptual

expert

Understanding EXISTS with correlated subqueries

What does the following query return?

SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id AND e.salary > 70000);

Assuming departments and employees tables with relevant data.

A Names of departments that have at least one employee earning more than 70000.

B Names of all departments regardless of employee salaries.

C Names of departments with no employees earning more than 70000.

D Names of employees earning more than 70000.

💡 Hint

EXISTS checks if the subquery finds any employee in the department with salary > 70000.

The query returns department names where the subquery finds at least one employee with salary greater than 70000, filtering departments accordingly.

Practice

1. What does the EXISTS keyword do in a MySQL query?

easy

A. Counts the total number of rows in a table

B. Deletes rows from a table based on a condition

C. Checks if a subquery returns any rows and returns TRUE or FALSE

D. Joins two tables together

Solution

1. Step 1: Understand the purpose of EXISTS

   EXISTS checks if the subquery returns at least one row.

2. Step 2: Compare with other options

   Counting rows, deleting rows, and joining tables are different operations.

3. Final Answer:

   Checks if a subquery returns any rows and returns TRUE or FALSE -> Option C

4. Quick Check:

   EXISTS means row presence check = B [OK]

Hint: EXISTS means "is there at least one row?" [OK]

Common Mistakes:

• Confusing EXISTS with COUNT
• Thinking EXISTS deletes rows
• Mixing EXISTS with JOIN

2. Which of the following is the correct syntax to use EXISTS in a WHERE clause?

easy

A. SELECT * FROM table WHERE EXISTS = (SELECT column FROM table2);

B. SELECT * FROM table WHERE EXISTS SELECT column FROM table2;

C. SELECT * FROM table WHERE EXISTS IN (SELECT column FROM table2);

D. SELECT * FROM table WHERE EXISTS (SELECT column FROM table2);

Solution

1. Step 1: Recall EXISTS syntax

   EXISTS must be followed by a subquery in parentheses.

2. Step 2: Check each option

   SELECT * FROM table WHERE EXISTS (SELECT column FROM table2); uses correct syntax with parentheses; others have syntax errors.

3. Final Answer:

   SELECT * FROM table WHERE EXISTS (SELECT column FROM table2); -> Option D

4. Quick Check:

   EXISTS needs parentheses around subquery = C [OK]

Hint: EXISTS always uses parentheses for the subquery [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Using '=' or 'IN' with EXISTS
• Writing subquery without parentheses

3. Given tables customers(id, name) and orders(id, customer_id), what does this query return?

SELECT name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);

medium

A. An error because of invalid subquery

B. All customers who have placed at least one order

C. All orders with matching customer names

D. All customers including those without orders

Solution

1. Step 1: Understand the EXISTS subquery

   The subquery checks if there is at least one order with customer_id matching customers.id.

2. Step 2: Interpret the WHERE clause effect

   Only customers with matching orders exist, so only those customers are returned.

3. Final Answer:

   All customers who have placed at least one order -> Option B

4. Quick Check:

   EXISTS filters customers with orders = D [OK]

Hint: EXISTS filters rows with matching related data [OK]

Common Mistakes:

• Thinking it returns all customers
• Confusing orders with customers
• Assuming syntax error in subquery

4. Identify the error in this query:

SELECT * FROM employees WHERE EXISTS SELECT * FROM departments WHERE departments.id = employees.dept_id;

medium

A. Missing parentheses around the subquery after EXISTS

B. Using SELECT * inside EXISTS is not allowed

C. The WHERE clause inside subquery is invalid

D. EXISTS cannot be used with multiple tables

Solution

1. Step 1: Check EXISTS syntax

   EXISTS requires the subquery to be enclosed in parentheses.

2. Step 2: Verify other parts

   SELECT * is allowed inside EXISTS; WHERE clause is valid; EXISTS works with multiple tables.

3. Final Answer:

   Missing parentheses around the subquery after EXISTS -> Option A

4. Quick Check:

   EXISTS needs parentheses = A [OK]

Hint: Always put subquery in parentheses after EXISTS [OK]

Common Mistakes:

• Omitting parentheses after EXISTS
• Thinking SELECT * is invalid inside EXISTS
• Misunderstanding WHERE clause usage

5. You want to find all products that have never been ordered. Given tables products(id, name) and orders(id, product_id), which query correctly uses EXISTS to achieve this?

hard

A. SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id);

B. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id);

C. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.id = products.id);

D. SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.id = products.id);

Solution

1. Step 1: Understand the goal

   We want products with no matching orders, so use NOT EXISTS with matching product_id.

2. Step 2: Analyze each option

   SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); correctly uses NOT EXISTS with orders.product_id = products.id. SELECT name FROM products WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); finds products with orders. Options C and D compare wrong columns.

3. Final Answer:

   SELECT name FROM products WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id); -> Option A

4. Quick Check:

   NOT EXISTS filters products without orders = A [OK]

Hint: Use NOT EXISTS to find items with no related rows [OK]

Common Mistakes:

• Using EXISTS instead of NOT EXISTS
• Comparing wrong columns in subquery
• Confusing product_id with order id