EXCEPT operation in SQL server 2005

The EXCEPT operation is a new feature that is introduced in SQL Server 2005

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

In general, except operations is used to find difference between two identical tables.
The basic rules for combining the result sets of two queries that use EXCEPT are the following:
1. The number and the order of the columns must be the same in all queries. 

2. The data types must be compatible. 

Consider the situation where we have to compare two tables for the correctness of their data or maybe they are tables in Test and Production databases, and you want to see if they are in sync or not. This can be done Using EXCEPT Operation on these two tables.
Let us try it out using two tables Table_A and Table_B. Consider that these tables have same schema.


Example 1:
Select * from Table_A

Except

Select * from Table_B

This example, compares Table_B with Table_A and give the distinct values from Table_A which are not present in Table_B. i.e. Any additional rows that are present in Table_A but not present in Table_B will be displayed along with the rows in Table_A which does not match in Table_B. But this does not display Vice-Versa.


Example 2:

SELECT * FROM

 (

 SELECT * FROM Table_A

 EXCEPT

 SELECT * FROM Table_B

 ) AS DifferenceFromLeft

 UNION

 SELECT * FROM

 (

 SELECT * FROM Table_B

 EXCEPT

 SELECT * FROM Table_A

 ) AS DifferenceFromRight

This example, give the complete differences between the two tables. i.e. any non matching records in any of the two tables as well as any new records in any of the two tables will be displayed. But the drawback of this query is that it does not show from which table the mismatch is from.


Example 3:
To overcome the drawback of Example 2, we will tune the query of Example 2 to include the source table names which will be helpful in identifying the source of the mismatch.
SELECT 'Table_A' as Source, * FROM

 (

 SELECT * FROM Table_A

 EXCEPT

 SELECT * FROM Table_B

 ) AS DifferenceFromLeft

 UNION

 SELECT 'Table_B' as Source, * FROM

 (

 SELECT * FROM Table_B

 EXCEPT

 SELECT * FROM Table_A

 ) AS DifferenceFromRight
To get the correct and expected results out of EXCEPT operation, it is mandatory to specify the columns in both the queries in the same order. If the order of the columns mismatch, then the result from the EXCEPT operation will mislead (you will not get the correct results).

This article is also available in pdf format for downloading.

Please Click here to get your copy.