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.