Subquery with EXISTS operator in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the EXISTS operator do in SQL?

EXISTS checks if a subquery returns any rows. It returns TRUE if the subquery has at least one row, otherwise FALSE.

beginner

Write a simple example of a query using EXISTS.

SELECT * FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.ID);<br>This returns customers who have at least one order.

intermediate

How is EXISTS different from IN in SQL?

EXISTS checks for the presence of rows and stops at the first match, making it efficient for some cases. IN compares values and may scan more rows.

intermediate

Can EXISTS subquery refer to the outer query tables?

Yes, EXISTS subqueries often use columns from the outer query to filter results, making them correlated subqueries.

intermediate

Why might you use EXISTS instead of JOIN?

EXISTS is useful when you only want to check if related rows exist, not to retrieve them. It can be faster and clearer for existence checks.

What does the EXISTS operator return if the subquery finds no rows?

A FALSE

B An error

C NULL

D TRUE

EXISTS returns FALSE if the subquery returns zero rows.

Which of the following is a valid use of EXISTS?

A SELECT * FROM Employees WHERE EXISTS Employees.DeptID = Departments.ID;

B SELECT * FROM Employees WHERE EXISTS (Departments.ID);

C SELECT * FROM Employees WHERE EXISTS Departments;

D SELECT * FROM Employees WHERE EXISTS (SELECT * FROM Departments WHERE Departments.ID = Employees.DeptID);

Option D correctly uses EXISTS with a subquery that relates to the outer query.

EXISTS subqueries are usually:

A Uncorrelated

B Always return multiple rows

C Correlated

D Used only with aggregate functions

EXISTS subqueries often refer to outer query columns, making them correlated.

Which is true about EXISTS performance?

A EXISTS always scans the entire subquery result

B EXISTS stops checking after finding the first matching row

C EXISTS is slower than IN in all cases

D EXISTS returns all rows from the subquery

EXISTS stops as soon as it finds one matching row, which can improve performance.

What does this query do? SELECT * FROM Products WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.ProductID = Products.ID);

A Returns products that have at least one order

B Returns all products

C Returns products with no orders

D Returns orders with matching products

It returns products that have at least one matching order.

Explain how the EXISTS operator works in SQL and give a simple example.

Think about checking if something exists before selecting.

Describe the difference between EXISTS and IN operators and when you might prefer EXISTS.

Consider performance and purpose of each operator.