In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at EXCEPT and INTERSECT SET Operations. This stuff is pretty simple so this post won’t be that long.

First create this simple table with 5 rows, notice id 5 is missing

CREATE TABLE Test1(id int, SomeVal char(5), OtherVal char(3))
INSERT Test1 VALUES(1,'11111','111') 
INSERT Test1 VALUES(2,'22222','222') 
INSERT Test1 VALUES(3,'33333','333') 
INSERT Test1 VALUES(4,'44444','444') 
INSERT Test1 VALUES(6,'66666','666') 

Create this table with 4 rows, notice id 3 is missing but it has id 5 which the previous created table does not have

CREATE TABLE Test2(id int, SomeVal char(5), OtherVal char(3))
INSERT Test2 VALUES(1,'11111','111') 
INSERT Test2 VALUES(2,'22222','222') 
INSERT Test2 VALUES(4,'44444','444') 
INSERT Test2 VALUES(5,'55555','555') 

UNION SET Operation

This is just a quick look at the UNION SET operation first. When you do a UNION, you will get all the rows that exists in either table. It will return a distinct set, if the row exists in both tables, you will only get back the row once. If the row exists more than once in a table, you will also get it back just once

Run the following code

SELECT *
FROM Test2
UNION
SELECT *
FROM Test1

Output

id SomeVal OtherVal
1   11111   111
2   22222   222
3   33333   333
4   44444   444
5   55555   555
6   66666   666

As you can see, each value from both tables is returned once

When you change the UNION to a UNION ALL, you will get all rows from both tables

SELECT *
FROM Test2
UNION ALL
SELECT *
FROM Test1

Output

id SomeVal OtherVal
1   11111   111
2   22222   222
4   44444   444
5   55555   555
1   11111   111
2   22222   222
3   33333   333
4   44444   444
6   66666   666

As you can see whatever is in both tables was returned

EXCEPT SET Operation

The EXCEPT SET operation returns a distinct set of rows from the top table that do not exists in the bottom table. Duplicates just like with UNION are eliminated

Run this query

SELECT *
FROM Test1
EXCEPT
SELECT *
FROM Test2

Output

id SomeVal OtherVal
3   33333   333
6   66666   666

As you can see the two rows from Test1 that do not exists in table Test2 are returned

If you flip the tables around, you will see that the one row from Test2 that does not exists in table Test1 is returned

SELECT *
FROM Test2
EXCEPT
SELECT *
FROM Test1

Output

id SomeVal OtherVal
5   55555   555

INTERSECT SET Operation

The INTERSECT SET operation returns a distinct result of rows that exists in both tables.

In this case the rows that exists in both table Test1 and table Test2 are returned

SELECT *
FROM Test1
INTERSECT
SELECT *
FROM Test2

Output

id SomeVal OtherVal
1   11111   111
2   22222   222
4   44444   444

Flipping the tables around makes no difference, the same result as in the previous query is returned

SELECT *
FROM Test2
INTERSECT
SELECT *
FROM Test1

Output

id SomeVal OtherVal
1   11111   111
2   22222   222
4   44444   444

Some more stuff

You are not just limited to 2 tables, you can also combine these SET operations. Here is a silly example that will return everything which exists in table Test1 that also exists in the set of table Test1 and table Test2. Of course everything in table Test1 will be returned

SELECT *
FROM Test1
INTERSECT
SELECT *
FROM Test2
UNION
SELECT *
FROM Test1
id SomeVal OtherVal
1   11111   111
2   22222   222
3   33333   333
4   44444   444
6   66666   666

Here are two more example

This query does not return any rows on my system, this means that all 67 rows in each table exist in both databases

SELECT * FROM model.sys.sysobjects WHERE xtype = 'S'
EXCEPT
SELECT * FROM tempdb.sys.sysobjects WHERE xtype = 'S'

The query below will give me all the rows that exists in both table, this is of course 67

SELECT * FROM model.sys.sysobjects WHERE xtype = 'S'
INTERSECT
SELECT * FROM tempdb.sys.sysobjects WHERE xtype = 'S'

You could use a query like that to find for example tables, procedures or other objects that do or do not exists in two different databases

This query will give me the names of all the procedures that exists in db1 but not in db2

SELECT name FROM db1.sys.procedures 
EXCEPT
SELECT name FROM db2.sys.procedures

This query will give me the names of all the procedures that exists in db1 and also in db2

SELECT name FROM db1.sys.procedures 
INTERSECT
SELECT name FROM db2.sys.procedures

That is it for today, come back tomorrow for post number 15 in this series