Sql And Me

An easiest way to compare two tables is by using UNION, EXCEPT and INTERSECT operators.

These operators takes two row sets as input, the output depends on the operator:

UNION : produces a new row set by joining both row sets, identifying common rows in input row sets and duplicates are removed,

EXCEPT : produces a row set by removing all rows from first row set which are present in second row set,

INTERSECT : produces a row set by identifying common rows in both row sets.

For example, I have below two tables with common data:

SELECT      ProductID, Name, Price

FROM        ProductsA

 

SELECT      ProductID, Name

FROM        ProductsB

Result Set:

 

You can compare these two tables using UNION, EXCEPT and INTERSECT. Even though the structure of these tables do not match, a comparison can be done on common columns.

1. Removing duplicates from result set (UNION):

Common rows can be identified using UNION as below:

SELECT      ProductID, Name FROM ProductsA

UNION

SELECT      ProductID, Name FROM ProductsB

This statement first removes all rows from result set of ProductsB which are already present in result set of ProductsA, removes duplicates from first result set, and then joins both result sets to produce the final result set:

Result Set:

ProductID     Name

1             Adjustable Race

2             Bearing Ball

3             BB Ball Bearing

23            Bike Stand

64            Cable

77            Bike Wash

87            All-Purpose Bike Stand

92            Chain

316           Blade

316           Blade Runner

324           Chain Stays

712           AWC Logo Cap

843           Cable Lock

877           Bike Wash

952           Chain Lock

 

(15 row(s) affected)

2. Removing common rows from result set (EXCEPT):

You can remove rows from a result set which are already in another result set by using EXCEPT as:

SELECT      ProductID, Name FROM ProductsA

EXCEPT

SELECT      ProductID, Name FROM ProductsB

This will produce a result set by returning all rows from ProductsA, and then removing all rows which are returned by ProductsB.

Result Set:

ProductID     Name

77            Bike Wash

87            All-Purpose Bike Stand

92            Chain

316           Blade

843           Cable Lock

 

(5 row(s) affected)

3. Identifying common rows (INTERSECT):

INTERSECT identifies common rows in the input result sets and returns a result set which is a set of common rows for both result sets:

SELECT      ProductID, Name FROM ProductsA

INTERSECT

SELECT      ProductID, Name FROM ProductsB

Result Set:

ProductID     Name

1             Adjustable Race

2             Bearing Ball

3             BB Ball Bearing

324           Chain Stays

712           AWC Logo Cap

 

(5 row(s) affected)

 

Comparing Two Tables:

To compare two tables we need a combination of all these operators, Above tables can be compared using below T-SQL:

SELECT      ProductID, Name, 'Only in ProductsA' FROM ProductsA

EXCEPT

SELECT      ProductID, Name, 'Only in ProductsA' FROM ProductsB

 

UNION ALL

 

SELECT      ProductID, Name, 'Only in ProductsB' FROM ProductsB

EXCEPT

SELECT      ProductID, Name, 'Only in ProductsB' FROM ProductsA

 

UNION ALL

 

SELECT      ProductID, Name, 'In Both Tables' FROM ProductsB

INTERSECT

SELECT      ProductID, Name, 'In Both Tables' FROM ProductsA

First two SELECTs identify unique rows in ProductsA, second two SELECTs identify unique rows in ProductsB, and last two statements identify common rows in both, and final result set is produced by UNION ALLs.

Result Set:

ProductID     Name  

87            All-Purpose Bike Stand     Only in ProductsA

77            Bike Wash                  Only in ProductsA

316           Blade                      Only in ProductsA

843           Cable Lock                 Only in ProductsA

92            Chain                      Only in ProductsA

23            Bike Stand                 Only in ProductsB

64            Cable                      Only in ProductsB

316           Blade Runner               Only in ProductsB

877           Bike Wash                  Only in ProductsB

952           Chain Lock                 Only in ProductsB

1             Adjustable Race            In Both Tables

2             Bearing Ball               In Both Tables

3             BB Ball Bearing            In Both Tables

324           Chain Stays                In Both Tables

712           AWC Logo Cap               In Both Tables

 

(15 row(s) affected)

 

Comparing Table Structure:

Above logic can also be applied to compare table structures using sys.columns as below:

SELECT      name, system_type_id, max_length, precision, 'In ProductsA'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsA')

EXCEPT

SELECT      name, system_type_id, max_length, precision, 'In ProductsA'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsB')

 

UNION ALL

 

SELECT      name, system_type_id, max_length, precision, 'In ProductsB'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsB')

EXCEPT

SELECT      name, system_type_id, max_length, precision, 'In ProductsB'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsA')

 

UNION ALL

 

SELECT      name, system_type_id, max_length, precision, 'In Both'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsA')

INTERSECT

SELECT      name, system_type_id, max_length, precision, 'In Both'

FROM        sys.columns WHERE object_id = OBJECT_ID(N'ProductsB')

Result Set:

Name          system_type_id       max_length    precision    

Price         60                   8             19            In ProductsA

ProductID     56                   4             10            In Both

Name          231                  100           0             In Both

 

(3 row(s) affected)

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe