SQL in a Nutshell, 2nd Edition

Name

EXISTS Operator

Synopsis

The EXISTS operator tests a subquery for the existence of rows.

Platform	Command
DB2	Supported
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

SELECT ...
WHERE [NOT] EXISTS (subquery)

The parameters and keywords are as follows:

WHERE [NOT] EXISTS

Tests the subquery for the existence of one or more rows. If even one row satisfies the subquery clause, it returns a Boolean TRUE value. The optional NOT keyword returns a Boolean TRUE value when the subquery returns no matching rows.

subquery

Retrieves a result set based on a fully formed subquery.

Rules at a Glance

The EXISTS operator checks a subquery for the existence of one or more records against the records in the parent query.

For example, we want to see if we have any jobs where no employee is filling the position:

SELECT *
FROM jobs
WHERE NOT EXISTS
   (SELECT * FROM employee
     WHERE jobs.job_id = employye.job_id)

This example tests for the absence of records in the subquery using the optional NOT keyword. The next example looks for specific records in the subquery to retrieve the main result set:

SELECT au_lname
FROM authors
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)

This query returns the last name of authors who live in the same city as their publishers. Note that the asterisk in the subquery is acceptable, since the subquery only needs to return a single record to provide a Boolean TRUE value. Columns are irrelevant in these ...