So far in this chapter, I’ve been using the comparison operators, IN, ALL, and ANY to compare a specific test value to values in a subquery result. EXISTS and NOT EXISTS don’t compare values; rather, they simply look for the existence or nonexistence of rows in a subquery result. The important characteristics of an existence test are:
An existence test doesn’t compare values, so it isn’t preceded by a test expression.
The subquery can be simple or correlated but usually is correlated (see “Simple and Correlated Subqueries” earlier in this chapter).
The subquery can return any number of columns and rows.
By convention, the SELECT clause in the subquery is SELECT * to retrieve all columns. Listing specific column names ...