Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

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:

Print | posted on Wednesday, May 02, 2007 2:47 PM | Filed Under [ T-SQL SQL Server 2005 ]

Feedback

Gravatar

# 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.
5/7/2007 10:00 AM | John Kandrovy
Gravatar

# re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

thanks.Very useful Notes
7/20/2007 5:12 AM | Rachana
Gravatar

# 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
12/7/2007 4:30 AM | Faiz Shaikh
Gravatar

# 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.
12/7/2007 4:46 AM | Faizan
Gravatar

# 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
2/19/2008 4:21 AM | santosh k
Gravatar

# 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.
3/4/2008 4:31 PM | S
Gravatar

# 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
7/23/2008 2:31 AM | Paul
Gravatar

# Thank you for giving us easy quries

very gooddddddddddd
10/14/2008 6:18 AM | sruthi
Gravatar

# re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Thank you
It saves me huge amount of time!
11/11/2008 4:27 AM | Vincent
Gravatar

# 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.
3/23/2009 5:05 PM | Fredda
Gravatar

# 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.
3/25/2009 8:57 AM | Jeff S
Gravatar

# re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Thanks.
3/26/2009 8:40 AM | Fredda
Gravatar

# re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

thanks.

it helped me a lot to find my result.
4/20/2009 8:35 AM | srikanth
Gravatar

# 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)?
6/30/2009 8:46 PM | snout
Gravatar

# 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.
7/2/2009 8:07 AM | natty
Gravatar

# 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
7/9/2009 12:53 AM | Bhargav Patel
Gravatar

# 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
9/1/2009 1:46 PM | Bob
Gravatar

# 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
9/3/2009 10:54 PM | Phaneesh
Gravatar

# re: SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Thanks for posting this. What a great explanation!
10/15/2009 5:32 PM | Reana
Gravatar

# 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

12/22/2009 11:44 PM | Prachi
Gravatar

# 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
1/7/2010 10:24 AM | Stephan
Gravatar

# 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
4/5/2010 12:06 AM | Nitin Mankar
Gravatar

# 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...
4/21/2010 8:43 AM | ketan patel
Gravatar

# 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.
6/15/2010 4:57 PM | will
Gravatar

# 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.
6/30/2010 7:19 AM | Kamran
Gravatar

# 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
7/26/2010 12:37 PM | Bala
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET