Using the EXISTS Condition

The EXISTS condition does not compare the resulting value of values with an object in the WHERE clause. The condition tests whether or not the subquery returns any values at all. The syntax of the condition is:

WHERE [NOT] EXISTS (SELECT statement)

The EXISTS condition is rarely efficient, in comparison with the other subquery type, especially when the subquery contains a WHERE clause condition that depends on value from the outer query.

For example:

SELECT c0001firstname, c0001surname
FROM t0001customer t0001
WHERE NOT EXISTS (SELECT c0001id FROM t0003rental t0003 WHERE t0001.c0001id = t0003.c0001)

The above example forces the subquery to be executed for each row of the outer query. A more practical and efficient version of the above would use NOT IN:

SELECT c0001firstname, c0001surname
FROM t0001customer t0001
WHERE c0001id NOT IN (SELECT UNIQUE c0001id FROM t0003rental);

Note: The SELECT clause of the subquery used in a EXISTS condition does not have to limit itself to one column or expression. SELECT * is quite valid.