Combination Queries

There is a set of queries sometimes referred to as combination queries. There are three set operators used to create them...

• UNION
• INTERSECT
• EXCEPT

We looked at the Union in our basic class. Below we will present the other two. Each is based on the bookstore database which can be downloaded here.

INTERSECT
The intersect operator retrieves all unique rows found in both tables.

Example
The following query will find all cities in common between the sources and customers tables.

Script
use bookstore -- MySQL and SQL Server
select customer_city, customer_state, customer_zip
from customers
intersect
select source_city, source_state, source_zip
from sources;

EXCEPT
The except operator will retrieve all rows found in the first table but not in the second.

Example
Find all cities where the source is located in a city that does not have a customer.

Script
use bookstore -- MySQL and SQL Server
select source_city, source_state, source_zip
from sources
except
select customer_city, customer_state, customer_zip
from customers;