SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Wed May 2, 2007 by Jeff Smith in t-sql, sql-server-2005

Previously, I had written that UNION ALL (combined with a GROUP BY) is a really quick and easy way to compare two tables. You don't need to worry about NULLS, the code is fairly short and easy to follow, and you can view exceptions from both tables at the same time.

Well, now in SQL 2005, we have another option: using EXCEPT and INTERSECT. And these are even easier!

To return all rows in table1 that do not match exactly the rows in table2, we can just use EXCEPT like this:

select * from table1 except select * from table2

To return all rows in table2 that do not match exactly in table1, we reverse the EXCEPT:

select * from table2 except select * from table1

And to return all rows in table1 that match exactly what is in table2, we can use INTERSECT:

select * from table1 intersect select * from table2

In all of the above examples, the columns must match between the two tables, of course.

Thus, we can return a listing of all rows from either table that do not match completely by using UNION ALL to combine the results of both EXCEPT queries:

select 'table1' as tblName, * from
(select * from table1
   except
   select * from table2) x
union all
select 'table2' as tblName, * from
(select * from table2
   except select *
   from table1) x

And we can now write a very simple stored procedure that compares any two tables (assuming the schemas match, of course) like this:

create procedure CompareTables @table1 varchar(100), @table2 varchar(100)
as
declare @sql varchar(8000)
set @sql = 'select ''' + @table1 + ''' as tblName, * from
    (select * from ' + @table1 + '
       except
       select * from ' + @table2 + ') x
    union all
    select ''' + @table2 + ''' as tblName, * from
    (select * from ' + @table2 + '
       except
       select * from ' + @table1 +') x'

exec(@sql)

Of course, both tables must have primary keys in place; duplicate values in these tables will not make logical sense when trying to determine which rows match or not.

So, EXCEPT and INTERSECT are pretty handy. Does anyone else have any suggestions for ideas where these operators can make things shorter, quicker or more efficient compared to older (pre SQL 2005) methods?

see also:

Legacy Comments


John Kandrovy 2007-05-07	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Hi I know that SQL2k has the same functions named EXCEPT and INTERSECT... just they are declared differently. Except(«Set1», «Set2»[, ALL]) and Intersect(«Set1», «Set2»[, ALL]). My question is can this concept be modified to work in SQL2K or is this just something for 2k5? Currently we use unions for large data comparisons to return a result in our application. I'm wondering if it will help speed them up by using these functions.

Rachana 2007-07-20	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables thanks.Very useful Notes

Faiz Shaikh 2007-12-07	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables I need to compare records in two different table and need to set status about any un matched records in two different table

Faizan 2007-12-07	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables I need to compare two or more then two columns of two different tables and need to show those records which are not matched.

santosh k 2008-02-19	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Really helpful for me for comparing the exact columns we can add the coluimns name instead of * is that select column name1,column name 2 ........... is this correct? - Santosh K

S 2008-03-04	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Cool........It saved a lot of time for me. I was looking for something like this and it came nicely to me...... Good work. Just a short explanation: We can use column names for duplicate check. I used it where i wanted to compare values of two tables TableA and TableB and insert new rows in TableA if the row exist in TableB but not in TableA. I was thinking to create a temp table but now i need not to........ Thanks a lot.

Paul 2008-07-23	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you !!!!!!!!!!!!!! HUGE time saver

sruthi 2008-10-14	Thank you for giving us easy quries very gooddddddddddd

Vincent 2008-11-11	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Thank you It saves me huge amount of time!

Fredda 2009-03-23	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables May be a dumb question, but what is the function of the 'x' ? I get an error if I is not there, but I do not know what it is for.

Jeff S 2009-03-25	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Hi -- the "x" is an alias for the derived table. Any time you create or use a derived table, you must give it an alias or a "name", so that you can reference it in the outer query.

Fredda 2009-03-26	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Thanks.

srikanth 2009-04-20	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables thanks. it helped me a lot to find my result.

snout 2009-06-30	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables This is awesome! Does anyone know how to save the results into another table (I tried select into, no good)?

natty 2009-07-02	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables snout, Select Into actually works perfectly with the Except keyword. Just make sure you pointing to the right database. I bet your table is now created in the master database.

Bhargav Patel 2009-07-09	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables I was going to create complex stored procedure but this will become very handy. Thanks for posting it. Bhargav Patel Senior DBA @ Cigna yobhargav@yahoo.com

Bob 2009-09-01	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables This looks pretty cool, will definitely come in handy. Would you be able to take it one step further, or would that be too much to ask :-) I have a list of keywords/phrases in a "keywords" table and a list of subjects in another table named "subjects". I need to find all the keywords that match the subject, but its not an exact match (ie: I might look for the keyword "bill" in the subject "I have a problem with my bill".) I am basically trying to build a report based on keyword hits. Thanks in advance! Bob

Phaneesh 2009-09-03	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables result set of the above SP will return a listing of all rows from either table that do not match completely, Can any one help me to fetch the records from table 1 which are similear to table 2 using like function. eg: table one has companies name "red india", "red India ltd" and table 2 has comany name as "Red India" where both the company of table 1 are same, I need to fetch all teh companies names with like function, please help me to fetch all the records of table 1 which are similar using like in table2

Reana 2009-10-15	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Thanks for posting this. What a great explanation!

Prachi 2009-12-22	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Hi, I would also like to compare two tables based on LIKE functionality (as mentioned in 2 of the posts above). Basically if table1 has value "Test" and table2 has value"Testing" I get a match. Is it possible in SQL in one query without looping through either of the tables and using a tradition LIKE comparison ? Thanks Prachi

Stephan 2010-01-07	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Can someone help me to solve my problem? I have 1 table containing user and ordered_product (beside other columns) Now I want to find out - based on a view on product 1 - which user has ordered only product 1 and not product 2 and as a second view which user had ordered both. User with only product 2 will not be looked at. Has someone a good idea? Thanks Stephan

Nitin Mankar 2010-04-05	compare table Dear All, I tried the above but not getting expect result. I required help. I required to compare the tables i.e supplier master and supplier contact In both the table supid is common. Thanks and Regards Nitin Mankar

ketan patel 2010-04-21	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables If i have multiple records suppose more then 1000 then the query execution time taken more then 8 second.. so please guide me what to do...

will 2010-06-15	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables This is perfect though how would it be modified to return only the columns that do not match as opposed to all.

Kamran 2010-06-30	How to calculate similarties between two tables. I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C). For Example I have two tables Table_A and Table_B as below: Table_A ---------------------- RecordID StudentID Dept BookID 1 123 CS 456 2 123 CS 345 3 223 TE 190 Table_B ---------------------- RecordID StudentID Dept BookID 1 123 CS 456 2 223 TE 345 3 223 TE 190 and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows: Table_C ---------------------- Sim_RecordID Sim_StudentID Sim_Dept SimBookID 1 1 1 1 1 0 0 1 1 1 1 1 Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.

Bala 2010-07-26	re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables Can't this be used with different tables, in other words, can we use in places where NOT IN opertor is used