Subqueries with EXISTS in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the EXISTS keyword do in a SQL query?

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

intermediate

How does a subquery with EXISTS differ from a normal JOIN?

EXISTS only checks for the presence of rows in the subquery and returns TRUE or FALSE, while JOIN combines rows from two tables based on a condition.

beginner

Write a simple example of a query using EXISTS to find customers who have placed orders.

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

intermediate

Why might you use EXISTS instead of IN in a subquery?

EXISTS can be more efficient because it stops searching as soon as it finds a matching row, while IN may scan all rows. EXISTS also handles NULLs better.

beginner

Can EXISTS subqueries return data? Explain.

No, EXISTS subqueries do not return data themselves. They only return TRUE or FALSE to indicate if the subquery found any rows.

What does the EXISTS keyword return in a SQL query?

A All rows from the subquery

B TRUE if subquery returns at least one row

C The count of rows in the subquery

D NULL if subquery returns no rows

EXISTS returns TRUE if the subquery returns any rows, otherwise FALSE.

Which of the following is a valid use of EXISTS?

A SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE condition);

B SELECT EXISTS FROM table1;

C EXISTS SELECT * FROM table1;

D SELECT * FROM table1 WHERE EXISTS = 1;

EXISTS is used with a subquery inside WHERE clause to check for row existence.

Why might EXISTS be preferred over IN in some cases?

A IN ignores NULL values

B IN is faster than EXISTS

C EXISTS returns all matching rows

D EXISTS stops searching after finding one match

EXISTS can be more efficient because it stops after finding the first matching row.

What does the subquery inside EXISTS typically return?

A NULL values only

B A specific column to display

C Any column or 1, since only existence matters

D The total count of rows

The subquery inside EXISTS usually returns 1 or any column because only the presence of rows matters.

If a subquery inside EXISTS returns no rows, what is the result?

A FALSE

B TRUE

C An error

D NULL

EXISTS returns FALSE if the subquery returns no rows.

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

Think about checking if something exists rather than retrieving data.

Describe a scenario where using EXISTS is better than using a JOIN or IN clause.

Consider when you only need to know if related data exists.