T-SQL 2005 Enhancements - INTERSECT and EXCEPT

INTERSECT and EXCEPT are not the most important changes in T-SQL for 2005, but when you need them they will save you tons of time.  If you have used UNION in the past, you will recognize how these funtions work. They take two like sets as input and combines them to make a single set. All of the columns in the sets must be equal in type and name.

So what do each of these do? They each work along the following lines:

<SET_A>
<SET_OPERATOR>
<SET_B>

Where the SET values are select statements, and the SET_OPERATOR is one of the following:

* UNION - includes all values in SET_A or SET_B.
* INTERSECT - includes values that exist in SET_A and SET B.
* EXCEPT - include values that exist in SET_A and not in SET_B

The addition of INTERSECT and EXCEPT really gives us the ability to manipulate sets in ways that are very interesting.  For example, say you are working with a non-profit organization, and you have a set of persons who have pledged to give money, and another set of persons who have given money.  The donations and pledges are not in the same table or you could do most of what we will demonstrate using WHERE clauses. (There is a simple table script building the tables we will use at the end of this entry.) First let's look at the data we have:

select donorId from donor.donation

donorId
----------
111111111
222222222
333333333
444444444
555555555

select donorId from donor.pledge

donorId
----------
333333333
444444444
666666666
777777777

So, let say we want to find all customers who have pledged or donated, perhaps to thank them:

select donorId
from   donor.pledge
UNION
select donorId
from   donor.donation

This returns:

donorId
----------
111111111
222222222
333333333
444444444
555555555
666666666
777777777

That was where things ended in SQL Server 2005, without going to fairly ugly SELECT statements.  But consider the question:  Which donors pledged money, and actually followed through and made a donation.  For this we use INTERSECT:

select donorId
from   donor.pledge
INTERSECT
select donorId
from   donor.donation

Which returns:

donorId
----------
333333333
444444444

These would be our best donors, those who pledged and gave money. (In reality we might do a bit more research to discover if they gave after they pledged.)  Finally, consider the very realistic query where we want to fine donors who have pledged but never gave any money whatsoever.  For this we use EXCEPT:

select donorId
from   donor.pledge
EXCEPT
select donorId
from   donor.donation

Which returns:

donorId
----------
666666666
777777777

So now with these new relational operators we are able to answer some very interesting questions between multiple sets that were not easy to do with very simple ease. And if you pledged money and didn't follow through, tsk, tsk, tsk (okay, I did it once and couldn't follow through, and if you are listening Georgia Public TV, sorry, but it has been nearly twenty years, give it up!)

so you might wonder how these work if you are using more than one operator.  For example if you had the following:

select donorId
from   donor.pledge
EXCEPT
select donorId
from   donor.donation
UNION
select 'Not In There'
INTERSECT
select 'Or Here'

The order is:

1. Any thing in parenthesis, so you could do:
<SET_A> <SET OPERATOR> (<SET_B> <SET OPERATOR> <SET_C>) and the parenthesis would come first, no matter what.

2. Any INTERSECTs would come next

3. Finally, from left to right, the UNION and EXCEPTs.

So in our query:

select 'Not In There'
INTERSECT
select 'Or Here'

Comes first, and it will have no values:

select donorId
from   donor.pledge
EXCEPT
select donorId
from   donor.donation
UNION
<Set with no values>

then the except is done:

<No Pledge Donors>
UNION
<Set with no Values>

Finally returning:

donorId
------------
666666666
777777777

I would strongly caution against chaining too many of these together without using parentheses, because most often you are going to want to do these in an order that you desire, not just based on precedence.

So there you have it, INTERSECT and EXCEPT.  Next up the APPLY operator.

-------------------------------------------------------------------------------------------
--Table Create Script

create schema donor
go
create table donor.donation
(
 donorId char(10)
)
create table donor.pledge
(
 donorId char(10)
)
go
insert into donor.donation
values ('111111111')
insert into donor.donation
values ('222222222')
insert into donor.donation
values ('333333333')
insert into donor.donation
values ('444444444')
insert into donor.donation
values ('555555555')

insert into donor.pledge
values ('333333333')
insert into donor.pledge
values ('444444444')
insert into donor.pledge
values ('666666666')
insert into donor.pledge
values ('777777777')

select * from donor.pledge
select * from donor.donation