In this programming tutorial you will learn how to compare tables in ms sql server using EXCEPT and INTERSECT. This feature comes from SQL SERVER 2005, returns distinct values by comparing the results of two queries. They are very easy to understand and work. So let's start.

Using EXCEPT and INTERSECT to compare tables

EXCEPT returns any distinct values from the left side query that are not also found on the right side query.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

The basic rules that you must have to follow for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

To return all rows in table1 that do not match exactly the rows in table2, we will 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 will use INTERSECT:

select * from table1 intersect select * from table2

Note:-  When you compare rows for determining distinct values, two NULL values are considered equal. The column names of the result set that are returned by EXCEPT or INTERSECT are the same names as those returned by the query on the left side of the operand. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
The nullability of any column in the result set returned by EXCEPT or INTERSECT is the same as the nullability of the corresponding column that is returned by the query on the left side of the operand.

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

We can also write a simple stored procedure that compares any two tables (Remember the schema of both tables must match) 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)

Definitely, 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 that's it, EXCEPT and INTERSECT to compare tables are pretty handy.

I hope you will find this tutorial very informative.

I love your feedback.