Use the FULL OUTER JOIN EXCEPT when it doesn’t work

After a simple ETL process where all I was doing was transferring data from one place to another. I found myself wanting to validate the transfer. The problem that I was facing was having to write the FULL OUTER JOIN sql statements  for each of the tables that I had just transferred. Did I mention that there were hundreds of tables.

No problem. I’ll write some dynamic sql ( I do love dynamic sql). After what felt like an eternity I came to realization that using the FULL OUTER JOIN statement was wrong. I had created a monster. A FULL OUTER JOIN can only compare some of the data types available to us. As such I was having to identify the type of each column and decide whether I wanted to cast that data type to a more friendly data type, i.e one that i could compare or whether I wanted to use a default value to represent the really un-friendly data types. Things were not going well.

Enter EXCEPT. At this point I realized that I was going about this all wrong, I needed to be using EXCEPT. I’ve never really needed to use except before. I’ve always been happy with LEFT OUTER or FULL OUTER joins, but here EXCEPT could really excel. EXCEPt does not suffer from the same limitations that the FULL OUTER JOIN suffers from, namely data type comparison. Also the dynamic sql that was generated is relatively maintainable whereas where I was going with solution 1 it would have been a un-maintainable overly complex kludge. See for yourself

Solution 1 the full outer join

Solution 2 the except