Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Compare SQL Server Datasets with INTERSECT and EXCEPT

MSSQLTips author Greg Robidoux By:   |   Read Comments (4)   |   Related Tips: 1 | 2 | 3 | 4 | More > JOIN Tables

Problem
When joining multiple datasets you have always had the ability to use the UNION and UNION ALL operator to allow you to pull a distinct result set (union) or a complete result set (union all).  These are very helpful commands when you need to pull data from different tables and show the results as one unified distinct result set.  On the opposite side of this it would be helpful to only show a result set where both sets of data match or only where data exists in one of the tables and not the other. This could be done with using different join types, but what other options does SQL Server offer?

Solution
With SQL Server 2005, Microsoft introduced the INTERSECT and EXCEPT operators to further extend what you could already do with the UNION and UNION ALL operators.

  • INTERSECT - gives you the final result set where values in both of the tables match
  • EXCEPT - gives you the final result set where data exists in the first dataset and not in the second dataset

The advantage of these commands is that it allows you to get a distinct listing across all of the columns such as the UNION and UNION ALL operators do without having to do a group by or do a comparison of every single column.  

Like the UNION and UNION ALL operators the table structures need to be consistent as well as the columns need to have compatible data types.

Let's take for example we have two tables manager and customer.  Both of these tables have somewhat the same structure such as the following columns:

  • FirstName
  • LastName
  • AddressLine1
  • City
  • StateProvinceCode
  • PostalCode

Manager table sample data

Customer table sample data

We want to do two queries:

  1. Find the occurrences where a manager is a customer (intersect)
  2. Find the occurrences where the manager is not a customer (except)

INTERSECT
If we want to find out which people exist in both the customer table and the manager table and get a distinct list back we can issue the following command:

SELECT FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   MANAGER
INTERSECT 
SELECT 
FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   CUSTOMER

Here is the result set:

To do this same thing with a regular T-SQL command we would have to write the following:

SELECT   M.FIRSTNAME,
         
M.LASTNAME,
         
M.ADDRESSLINE1,
         
M.CITY,
         
M.STATEPROVINCECODE,
         
M.POSTALCODE
FROM     MANAGER M
         
INNER JOIN CUSTOMER C
           
ON M.FIRSTNAME C.FIRSTNAME
              
AND M.LASTNAME C.LASTNAME
              
AND M.ADDRESSLINE1 C.ADDRESSLINE1
              
AND M.CITY C.CITY
              
AND M.POSTALCODE C.POSTALCODE
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY,
         
M.STATEPROVINCECODE,M.POSTALCODE

EXCEPT
If we want to find out which people exists in the manager table, but not in the customer table and get a distinct list back we can issue the following command:

SELECT FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   MANAGER 
EXCEPT
SELECT 
FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   CUSTOMER

Here is the result set:

To do this same thing with a regular T-SQL command we would have to write the following:

SELECT   M.FIRSTNAME,
         
M.LASTNAME,
         
M.ADDRESSLINE1,
         
M.CITY,
         
M.STATEPROVINCECODE,
         
M.POSTALCODE
FROM     MANAGER M
WHERE    NOT EXISTS (SELECT *
                     
FROM   CUSTOMER C
                     
WHERE  M.FIRSTNAME C.FIRSTNAME
                            
AND M.LASTNAME C.LASTNAME
                            
AND M.ADDRESSLINE1 C.ADDRESSLINE1
                            
AND M.CITY C.CITY
                            
AND M.POSTALCODE C.POSTALCODE)
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY,
         
M.STATEPROVINCECODE,M.POSTALCODE

From the two examples above we can see that using the EXCEPT and INTERSECT commands are much simpler to write then having to write the join or exists statements.

 

To take this a step further if we had a third table (or forth...) that listed sales reps and we wanted to find out which managers were customers, but not sales reps we could do the following.

SalesRep table sample data

SELECT FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   MANAGER
INTERSECT 
SELECT 
FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   CUSTOMER 
EXCEPT
SELECT 
FIRSTNAME,
       
LASTNAME,
       
ADDRESSLINE1,
       
CITY,
       
STATEPROVINCECODE,
       
POSTALCODE
FROM   SALESREP

Here is the result set:

As you can see this is pretty simple to mix and match these statements.  In addition, you could also use the UNION and UNION ALL operators to further extend your final result sets.

Next Steps

  • Take a look at your existing code to see how the INTERSECT and EXCEPT operators could be used
  • Keep these new operators in mind next time you need to compare different datasets with like data


Last Update: 9/11/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, September 19, 2014 - 11:18:05 PM - Kore Read The Tip

Thank you for this article. Comparing T-Sql with the INTERSECT and EXCEPT what is the different in resource and time usage?

Thanks 


Wednesday, July 31, 2013 - 3:33:04 AM - nitin jain Read The Tip

i m always using mssqltips site for my queries or prblems, its very helpfull for me, for above artical i m able to understand the except oprator in sql, really thanks from bottom of by heart.


Wednesday, July 11, 2012 - 6:37:58 PM - Sean DeYoung Read The Tip

Really good article. Thanks!


Sunday, August 10, 2008 - 5:39:49 AM - naveentnk Read The Tip

Thanks for this article.Its a good one. its better to go for except and interset than to go for joins.

Thans

Naveen




 
Sponsor Information