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

T-SQL
1
2
3
4
5
6
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 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

T-SQL
1
2
3
4
5
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') 
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test2
UNION
SELECT *
FROM Test1
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test2
UNION ALL
SELECT *
FROM Test1
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test1
EXCEPT
SELECT *
FROM Test2
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test2
EXCEPT
SELECT *
FROM Test1
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test1
INTERSECT
SELECT *
FROM Test2
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

T-SQL
1
2
3
4
5
SELECT *
FROM Test2
INTERSECT
SELECT *
FROM Test1
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

T-SQL
1
2
3
4
5
6
7
8
SELECT *
FROM Test1
INTERSECT
SELECT *
FROM Test2
UNION
SELECT *
FROM Test1
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

T-SQL
1
2
3
SELECT * FROM model.sys.sysobjects WHERE xtype = 'S'
EXCEPT
SELECT * FROM tempdb.sys.sysobjects WHERE xtype = 'S'
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

T-SQL
1
2
3
SELECT * FROM model.sys.sysobjects WHERE xtype = 'S'
INTERSECT
SELECT * FROM tempdb.sys.sysobjects WHERE xtype = 'S'
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

T-SQL
1
2
3
SELECT name FROM db1.sys.procedures 
EXCEPT
SELECT name FROM db2.sys.procedures
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

T-SQL
1
2
3
SELECT name FROM db1.sys.procedures 
INTERSECT
SELECT name FROM db2.sys.procedures
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