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 ...
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.