Subquery with EXISTS operator in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find customers with orders

Given two tables Customers and Orders, which query returns all customers who have placed at least one order?

SQL

SELECT CustomerID, CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

A Returns customers who have no orders.

B Returns all customers regardless of orders.

C Syntax error due to missing alias.

D Returns all customers who have at least one order.

💡 Hint

EXISTS checks if the subquery returns any rows for each customer.

The EXISTS operator returns true if the subquery finds any matching rows. Here, it checks if there is at least one order for each customer.

❓ query_result

intermediate

Identify products never ordered

Which query returns products that have never been ordered from the Products and OrderDetails tables?

SQL

SELECT ProductID, ProductName FROM Products WHERE NOT EXISTS (SELECT 1 FROM OrderDetails WHERE OrderDetails.ProductID = Products.ProductID);

A Syntax error due to missing NOT keyword.

B Returns products that have been ordered at least once.

C Returns products that have never been ordered.

D Returns all products regardless of orders.

💡 Hint

NOT EXISTS returns true when the subquery finds no matching rows.

The query uses NOT EXISTS to find products without any matching order details, meaning never ordered.

📝 Syntax

advanced

Detect syntax error in EXISTS subquery

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

SQL

SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

A SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

B SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

C SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT * FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

D SELECT EmployeeID FROM Employees WHERE EXISTS (SELECT OrderID FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID);

💡 Hint

The subquery in EXISTS must select at least one column or a constant.

Option B is missing the column or constant after SELECT, causing a syntax error.

❓ optimization

advanced

Optimize query with EXISTS vs IN

Which query is generally more efficient to find customers with orders, assuming Orders has many rows?

A SELECT CustomerID FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

B SELECT CustomerID FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);

C SELECT CustomerID FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

D SELECT DISTINCT CustomerID FROM Orders;

💡 Hint

EXISTS stops searching after finding the first match, IN may scan all matches.

EXISTS is often more efficient because it stops checking once a match is found, while IN may process all matching rows.

🧠 Conceptual

expert

Understanding EXISTS with correlated subqueries

Consider the query:

SELECT DepartmentID FROM Departments d WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.DepartmentID = d.DepartmentID AND e.Salary > 50000);

What does this query return?

A Departments that have at least one employee earning more than 50000.

B All departments regardless of employee salaries.

C Departments with no employees earning more than 50000.

D Employees earning more than 50000 in any department.

💡 Hint

EXISTS checks for each department if the subquery finds any employee with salary > 50000.

The query returns departments where the subquery finds at least one employee with salary above 50000.

Practice

1. What does the EXISTS operator do in an SQL query?

easy

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

B. Counts the number of rows in a table.

C. Joins two tables based on a condition.

D. Deletes rows from a table.

Solution

1. Step 1: Understand the purpose of EXISTS

   The EXISTS operator checks if the subquery returns at least one row.

2. Step 2: Compare with other options

   Counting rows, joining tables, or deleting rows are different SQL operations unrelated to EXISTS.

3. Final Answer:

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

4. Quick Check:

   EXISTS = TRUE if rows found [OK]

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

Common Mistakes:

• Thinking EXISTS counts rows instead of checking existence
• Confusing EXISTS with JOIN operations
• Assuming EXISTS deletes or modifies data

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 LIKE (SELECT column FROM table2);

Solution

1. Step 1: Review correct EXISTS syntax

   EXISTS is used as EXISTS (subquery) without any operator like =, IN, or LIKE.

2. Step 2: Identify incorrect options

   Options B, C, and D misuse operators (=, IN, LIKE) with EXISTS, causing syntax errors.

3. Final Answer:

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

4. Quick Check:

   EXISTS uses only parentheses for subquery [OK]

Hint: EXISTS always followed by (subquery) without operators [OK]

Common Mistakes:

• Adding = or IN after EXISTS
• Using LIKE with EXISTS
• Forgetting parentheses around subquery

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

SELECT name FROM Customers c WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id);

medium

A. All customers including those without orders.

B. All customers who have placed at least one order.

C. All orders with customer names.

D. An error because of incorrect subquery.

Solution

1. Step 1: Understand the EXISTS subquery condition

   The subquery checks if there is at least one order with customer_id matching the customer's id.

2. Step 2: Determine which customers are returned

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

3. Final Answer:

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

4. Quick Check:

   EXISTS filters customers with orders [OK]

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

Common Mistakes:

• Thinking it returns all customers regardless of orders
• Confusing with JOIN that returns all orders
• Assuming syntax error due to subquery

4. Identify the error in this query:

SELECT * FROM Employees e WHERE EXISTS SELECT * FROM Salaries s WHERE s.emp_id = e.id;

medium

A. EXISTS cannot be used in WHERE clause.

B. Using SELECT * inside EXISTS is not allowed.

C. Missing parentheses around the subquery after EXISTS.

D. The alias 'e' is not defined.

Solution

1. Step 1: Check EXISTS syntax

   EXISTS requires the subquery to be enclosed in parentheses.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around the subquery after EXISTS, causing a syntax error.

3. Final Answer:

   Missing parentheses around the subquery after EXISTS. -> Option C

4. Quick Check:

   EXISTS (subquery) needs parentheses [OK]

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

Common Mistakes:

• Omitting parentheses around subquery
• Thinking SELECT * is invalid inside EXISTS
• Misunderstanding alias 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 find these products?

hard

A. SELECT name FROM Products p WHERE NOT IN (SELECT product_id FROM Orders);

B. SELECT name FROM Products p WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

C. SELECT name FROM Products p WHERE EXISTS NOT (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

D. SELECT name FROM Products p WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id);

Solution

1. Step 1: Understand the goal

   We want products with no matching orders, so the subquery should check for orders and we want those where no such orders exist.

2. Step 2: Use NOT EXISTS correctly

   NOT EXISTS with a subquery checking orders for the product returns products never ordered.

3. Step 3: Evaluate other options

   SELECT name FROM Products p WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id); returns products with orders, C has invalid syntax, D uses NOT IN which can cause issues with NULLs.

4. Final Answer:

   SELECT name FROM Products p WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.product_id = p.id); -> Option D

5. Quick Check:

   NOT EXISTS finds missing related rows [OK]

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

Common Mistakes:

• Using EXISTS instead of NOT EXISTS for missing data
• Incorrect syntax like EXISTS NOT
• Using NOT IN which may fail with NULLs