Subquery that follows EXISTS

The optimizer converts a subquery that follows the EXISTS keyword only if:

  • The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.

  • The conjunct 'EXISTS (subquery)' is not negated.

  • The subquery is correlated; that is, it contains an outer reference.

 Example