THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Using SQL Server 2008's EXCEPT and INTERSECT Statements

SQL Server 2008 shipped with several enhancements to the T-SQL language. In a recent blog, we looked at the new MERGE syntax; today we are going to look at the new EXCEPT and INTERSECT syntax. Both of these operators allow you to compare results of two queries. Using EXCEPT, you can find all the rows that exist in one query but not the other. Using INTERSECT, you can find all the rows that are the same in both tables. This makes more sense with an example so let’s take a look. I took a copy of the HumanResources.Employee table from AdventureWorks and made two copies called Employee1 and Employee2. Then to give us different results, I ran the following delete statement.

DELETE FROM Employee1 WHERE EmployeeID < 10

DELETE FROM Employee2 WHERE EmployeeID > 300

This gives us two tables that have matching records as well as unique records that the other table doesn’t have. Now we can explore the statements. First let’s run an EXCEPT comparison.

SELECT * FROM Employee1

EXCEPT

SELECT * FROM Employee2

This will return the rows in Employee1 that are not also found in Employee2, in this case all the records with an EmployeeID that is greater than 300. Reversing the location of the SELECT statements would return all the rows in Employee2 that were not also found in Employee1, all rows with an EmployeeID less than 10.

Next let’s use INTERSECT . The following statement will retun all the records that are contained in both queries, in our case all records with an EmployeeID between 10 and 300.

SELECT * FROM Employee2

INTERSECT

SELECT * FROM Employee1

These are great little additions to T-SQL that allow you to do some quick comparision work on your result sets. With a few twists, you could even write a query that returns all rows from both tables with no match in the other table. Next time you find yourself with data to compare, give EXCEPT and INTERSECT a shot.

Published Monday, March 01, 2010 3:29 PM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

CK said:

Not to be nitpicky, but this feature was available in 2005 as well

March 1, 2010 5:26 PM
 

Uri Dimant said:

Eric

I think that is worth to mention that EXCEPT/INTERSECT returns DISTINCT results, meaning if you have duplicate rows in

Employee2 table the row will be returned only once

March 2, 2010 12:13 AM
 

Uri Dimant said:

Eric

I think that is worth to mention that EXCEPT/INTERSECT returns DISTINCT results, meaning if you have duplicate rows in

Employee2 table the row will be returned only once

March 2, 2010 12:13 AM
 

Uri Dimant said:

Eric

I think that is worth to mention that EXCEPT/INTERSECT returns DISTINCT results, meaning if you have duplicate rows in

Employee2 table the row will be returned only once

March 2, 2010 12:13 AM
 

Adam Machanic said:

CK: Certainly not nitpicky to mention that. If anyone sees this and is still running 2005 it's definitely important for them to understand that these features are available in that version.

March 2, 2010 10:38 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement