Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the example below, comparing every column? Assume there are 30 columns in the tables, many of which are NULLable.

When there is no PK or there could be duplicates per PK, joining on just PK columns isn't enough, and it would be a disaster to have to do a FULL JOIN with 30 join conditions that properly handle NULLs, plus a nasty WHERE condition to exclude the matched rows.

Usually it is when I'm writing a new query against unscrubbed or not-fully-understood data that the problem is worst and the likelihood of a PK being logically available is extremely low. I cook up two different ways to solve the problem and then compare their results, the differences highlighting special cases in the data that I was unaware of.

The result needs to look like this:

Which   Col1   Col2   Col3   ... Col30
------  ------ ------ ------     ------
TableA  Cat    27     86               -- mismatch
TableB  Cat    27     105              -- mismatch
TableB  Cat    27     87               -- mismatch 2
TableA  Cat    128    92               -- no corresponding row
TableB  Lizard 83     NULL             -- no corresponding row

If [Col1, Col2] does happen to be a candidate primary key and we order by them in our final result, then we can easily see that A and B have one row different that should be the same, and each has one row that is not in the other.

In the above example, seeing the first row twice is not desirable.

share|improve this question
4  
You've made a couple of comments about wanting answers that produce the exact result you've specified in your question. If you want respondents to put that effort into an answer, you should put equal effort into your question. In this case, that would be DDL for the tables and INSERT scripts for sample data. –  Mark Storey-Smith Feb 10 '12 at 9:43
1  
So are you saying you've asked this question so that you can swoop in and answer it yourself with a far superior answer than those provided? Were it me wanting to do this (if I didn't want to blog about it instead), I would probably post the question and the approach I'm using, and ask if there is a better way. Imagine if I wasted an hour of my time coming up with a solution that was very close or identical to the one you've already implemented? What a waste of my time that would be! For 15 rep points on a volunteer site? Thanks but no thanks... NEXT! –  Aaron Bertrand Feb 10 '12 at 18:21
1  
Ok, so you didn't post the question to come in and save the answer, you posted the question so you could point out how inferior the other solutions are? Is there a way to unsubscribe from certain people's questions? –  Aaron Bertrand Feb 10 '12 at 18:26
    
Nope, I disagree. Ask questions with full disclosure. Here is what I tried, here is what didn't work, and why, etc. This is to me is a big, ugly horse-and-pony show. –  Aaron Bertrand Feb 10 '12 at 18:28

4 Answers 4

up vote 6 down vote accepted

You don't need 30 join conditions for a FULL OUTER JOIN here.

You can just Full Outer Join on the PK, preserve rows with at least one difference with WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*) and use CROSS APPLY (SELECT A.* UNION ALL SELECT B.*) to unpivot out both sides of the JOINed rows into individual rows.

WITH TableA(Col1, Col2, Col3) 
     AS (SELECT 'Dog',1,1     UNION ALL 
         SELECT 'Cat',27,86   UNION ALL 
         SELECT 'Cat',128,92), 
     TableB(Col1, Col2, Col3) 
     AS (SELECT 'Dog',1,1     UNION ALL 
         SELECT 'Cat',27,105  UNION ALL 
         SELECT 'Lizard',83,NULL) 
SELECT CA.*
FROM   TableA A 
       FULL OUTER JOIN TableB B 
         ON A.Col1 = B.Col1 
            AND A.Col2 = B.Col2 
/*Unpivot the joined rows*/
CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL
             SELECT 'TableB' AS what, B.*) AS CA     
/*Exclude identical rows*/
WHERE  EXISTS (SELECT A.* 
               EXCEPT 
               SELECT B.*) 
/*Discard NULL extended row*/
AND CA.Col1 IS NOT NULL      
ORDER BY CA.Col1, CA.Col2

Gives

what   Col1   Col2        Col3
------ ------ ----------- -----------
TableA Cat    27          86
TableB Cat    27          105
TableA Cat    128         92
TableB Lizard 83          NULL

Or a version dealing with the moved goalposts.

SELECT DISTINCT CA.*
FROM   TableA A 
       FULL OUTER JOIN TableB B 
         ON EXISTS (SELECT A.*  INTERSECT  SELECT B.*) 
CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL
             SELECT 'TableB' AS what, B.*) AS CA     
WHERE NOT EXISTS (SELECT A.*  INTERSECT  SELECT B.*) 
AND CA.Col1 IS NOT NULL
ORDER BY CA.Col1, CA.Col2  
share|improve this answer
    
I'm curious, how would this work with more columns on TableA and TableB?, would you need n-1 join conditions? (being n the number of columns on both tables) –  Lamak Feb 10 '12 at 12:55
    
@Lamak - No. The full outer join is on the 2 column PK stated in the question. The WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*) excludes the rows where all the columns match for any amount of columns. –  Martin Smith Feb 10 '12 at 13:02
    
Ah, I missed the part where OP defined col1 and col2 being PK. –  Lamak Feb 10 '12 at 13:10
    
Of course you are right. The PK thing was an afterthought. Often there is no PK or there are duplicate rows. I shouldn't have mentioned it. Sigh. I will have more time later to respond. –  ErikE Feb 10 '12 at 17:45
    
Thanks for your efforts. You have some good techniques in there. –  ErikE Feb 10 '12 at 18:37

This can be handled using EXCEPT and/or INTERSECT. http://msdn.microsoft.com/en-us/library/ms188055.aspx

First find all records that are in table1 that are not in table 2, then find all records that are in table 2 that are not in table one.

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

UNION

SELECT * FROM table2
EXCEPT
SELECT * FROM table1

There is undoubtedly a more efficient way to do this, but it is the first "quick and dirty" solution off the top of my head. Also, I do not recommend using a * wildcard, but it suits here for brevity.

Alternately, you could use an INTERSECT operator and exclude all the results from it.

share|improve this answer
4  
Wrap the SELECT ... EXCEPT ... SELECT with another SELECT statement and add the table name, i.e. SELECT "table1", T1.* FROM (SELECT ... EXCEPT ... SELECT) T1 then UNION that with the other half of the query. –  Simon Righarts Feb 10 '12 at 1:26
4  
Hint Hint Hint: @Erike, he answered your question, and deserves and upvote. You should be able to do the rest. We are not here to do the work for you. –  datagod Feb 10 '12 at 3:56
1  
If its a "super-easy" question, then why did you need to ask it? –  Lamak Feb 10 '12 at 12:47
    
@ErikE - That post meant to ask and answer your own question, and not leading people to believe you have a problem and help you. So far, you haven't answered it, you are just pointing what is wrong with the answers that you are getting. If you'd posted your answer already, then I would support your approach –  Lamak Feb 10 '12 at 18:32

It is easy to accomplish with a third party tool like Data Compare, or just do it on the client. In the context of unit testing stored procedures, we just wrote some C# code.

Here is the C# code we are using, quoted from an old article:Close those Loopholes - Testing Stored Procedures

   internal static class DataSetComparer
   {
      internal static bool Compare(DataSet one, DataSet two)
      {
         if(one.Tables.Count != two.Tables.Count)
            return false;

         for(int i = 0; i < one.Tables.Count; i++)
            if(!CompareTables(one.Tables[i], two.Tables[i]))
               return false;

         return true;
        }

      private static bool CompareTables(DataTable one, DataTable two)
      {
         if(one.Rows.Count != two.Rows.Count)
            return false;

         for(int i = 0; i < one.Rows.Count; i++)
            if(!CompareRows(one.Rows[i], two.Rows[i]))
               return false;

         return true;
      }

      private static bool CompareRows(DataRow one, DataRow two)
      {
         if(one.ItemArray.Length != two.ItemArray.Length)
            return false;

         for(int i = 0; i < one.ItemArray.Length; i++)
            if(!CompareItems(one.ItemArray[i], two.ItemArray[i]))
               return false;

         return true;
      }

      private static bool CompareItems(object value1, object value2)
      {
         if(value1.GetType() != value2.GetType())
            return false;

         if(value1 is DBNull)
            return true;

         if(value1 is DateTime)
            return ((DateTime) value1).CompareTo((DateTime) value2)
                                                              == 0;

         if(value1 is byte[])
         {
            if(((byte[]) value1).Length != ((byte[]) value2).Length)
               return false;

            for(int i = 0; i < ((byte[]) value1).Length; i++)
               if(((byte[]) value1)[i] != ((byte[]) value2)[i])
                  return false;

            return true;
         }

         return value1.ToString().Equals(value2.ToString());
      }
   }
share|improve this answer
    
Hey that code looks fantastic. Thank you! –  ErikE Feb 10 '12 at 18:33

Here's a way to show what was asked for:

SELECT 'A' Which, * FROM (
   SELECT * FROM TableA
   EXCEPT
   SELECT * FROM TableB
) X
UNION ALL
SELECT 'B', * FROM (
   SELECT * FROM TableB
   EXCEPT
   SELECT * FROM TableA
) X
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.