Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 5 Subqueries: Using Queries Within Other Queries

Types of subqueries

Finding intersection and difference with exists

You can use subqueries that are introduced with exists and not exists for two set theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains the elements that belong only to the first set.

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 affected)

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 
-------------------- 
Gary
Covelo 
Oakland 
Lawrence
San Jose 
Ann Arbor 
Corvallis 
Nashville 
Palo Alto
Rockville 
Vacaville 
Menlo Park
Walnut Creek 
San Francisco 
Salt Lake City 
 
(15 rows affected)


	Expand Search