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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
