SQL: Visual QuickStart Guide

Testing Existence with EXISTS

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 ...