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

Get SQL: Visual QuickStart Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.