Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries Testing Existence

When a subquery is introduced with the keyword EXISTS, the subquery functions as an “existence test.” The EXISTS keyword in a WHERE clause tests for the existence or nonexistence of data that meets the criteria of the subquery.

A subquery introduced with EXISTS takes this general form:

					Start of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery
WHERE [NOT] EXISTS
     (subquery)
[End of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery]
				

To find the names of all the publishers that publish business books, the query is this:

SQL

select distinct pub_name
from publishers
where exists (select * from titles where pub_id = publishers.pub_id and type = 'business') pub_name ======================================== New ...