What is the difference between a subquery in WHERE id IN (SELECT ...) and a subquery with WHERE EXISTS (SELECT ...)? IN compares values with a result set, EXISTS checks for the existence of at least one row IN is executed for each row of the outer query, EXISTS once No difference IN works only with numbers, EXISTS with any types

Correct answer: IN compares values with a set of results, EXISTS checks for the existence of at least one row.

A subquery with IN returns rows where the value matches any of the values returned by the subquery. A subquery with EXISTS checks whether at least one row satisfies the condition.

Differences:

• IN compares specific values, EXISTS checks for the presence of rows.
• EXISTS is often more efficient for existence checks, especially with complex conditions.

Example:

SELECT * FROM orders o
WHERE o.customer_id IN (SELECT c.id FROM customers c WHERE c.status = 'active');

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

Both queries select orders of active customers but operate differently.