Getting the difference between two sets of like data

A question came up on the forums the other day that a person wanted to compare two tables of data.  Doing this is pretty easy using several techniques:

• Using EXCEPT
• Using NOT EXISTS
• Using a FULL OUTER JOIN

First two sample tables which represent two sets of data that you would get from a statement, from clause, etc.  Hopefully this is not two different tables in the same database that have the exact same structure, as this would be a design problem most likely.  It that is the case, you are probably storing data in the name of the table, which is a bad idea!  Okay, back down off the soapbox...

create table tableA
(
    column1 int,
    column2 int
)
insert into tableA
select 1,1
union all
select 1,2
union all
select 1,3
union all
select 2,1

create table tableB
(
    column1 int,
    column2 int
)
insert into tableB
select 1,1
union all
select 1,3
union all
select 2,2
go

Using the new EXCEPT operator from 2005, you can find out which rows in tableA do not exist in tableB using the following statement:

select *
from tableA
EXCEPT
select *
from tableB

This will return:

column1     column2
----------- -----------
1           2
2           1

And vice versa:

select column1, column2
from tableB
EXCEPT
select column1, column2
from tableA

Which returns:

column1     column2
----------- -----------
2           2

This can also be done in 2000 using a not exists operator (though certainly not nearly as elegant):

select column1, column2
from   tableA
where not exists (select  *
                             from     tableB
                             where   tableA.column1 = tableB.column1
                                 and   tableA.column2 = tableB.column2)

Which returns the same set as:

select column1, column2
from tableA
EXCEPT
select column1, column2
from tableB

Sometimes though, it can be useful to get back the results in one result set.  The following query uses a full outer join to join, getting back all of the data from tableA and tableB, with matches. In the where clause, I get only the cases where the values for the two columns in one of the tables are both null:

select  tableA.column1 as column1_A, tableA.column2 as column2_A,
          tableB.column1 as column1_B, tableB.column2 as column2_B
from   tableA
              full outer join tableB
                  on tableA.column1 = tableB.column1
                       and tableA.column2 = tableB.column2
where  (tableA.column1 is null and tableA.column2 is null)
   or     (tableB.column1 is null and tableB.column1 is null)

This returns:

column1_A   column2_A   column1_B   column2_B
----------- ----------- ----------- -----------
1           2           NULL        NULL
2           1           NULL        NULL
NULL        NULL        2           2

crossposted to: http://drsql.spaces.live.com