Using EXISTS to Find Intersection and Difference

Subqueries introduced with EXISTS and NOT EXISTS can be used for two set theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the two original sets. The difference contains the elements that belong only to the first of the two sets. (See “Going Beyond Joins: UNION, INTERSECT, MINUS” in Chapter 7 for more information.)

The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located:

select distinct city
from authors
where exists
  (select *
   from publishers
   where authors.city = publishers.city)
city
====================
Berkeley
[1 row]

The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located (that is, all the cities except Berkeley):

select distinct city
from authors
where not exists
  (select *
   from publishers
   where authors.city = publishers.city)
city
====================
Oakland
Salt Lake City
Gary
Rockville
Vacaville
Palo Alto
Walnut Creek
San Jose
Covelo
Nashville
Menlo Park
Ann Arbor
San Francisco
Corvallis
Lawrence
[15 rows]