Using FULL JOINs to Compare Datasets

Question

Using FULL JOINs to Compare Datasets

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

gpatterson-825050 SSC Enthusiast Points: 116 More actions · Answer 1

Thanks for the time and effort to produce this informative article.

One small thing I noticed is that this solution requires an assumption that a "missing" row in either destination or source implies a 0. Because of the way you are using ISNULL(), a particular ID that exists with a value of 0 on one side and does not exist at all on the other side will never be identified as a difference.

This is probably fine in many cases (e.g. daily sales data), but it might be conceivable to create a circumstance where it would matter. For example, if we are comparing a normalized (to z scores) data set, 0 would have a very different meaning and not be a logical default assumption.

Admittedly, my example is a poor and far fetched one. But I think this assumption is worth pointing out for those who may use this snippet of code.

Geoff

markoos1 SSChasing Mays Points: 653 More actions · Answer 2

GPatterson -- that's a very good point. I've run into that exact problem before. I was going to address that in part 2....but as a preview let me just say that the ISNULL() has to resolve to an impossible value, or else the full join will over-join. I had an ISNULL(<field>,0) once where the field had a possible value of 0, and this caused a problem.

Thanks for your input.

ashishtiwarivds Valued Member Points: 64 More actions · Answer 3

Nice article. Thanks for your efforts for putting this together.

Just wondering, what will happen if we don't use COALESCE() function? Generally when I am using LEFT JOIN to find delta at source I don't use this? Can you please elaborate?

Thanks again and appriciate your work.

Cheers!!

markoos1 SSChasing Mays Points: 653 More actions · Answer 4

Coalesce is needed because you don't know in advance if the value will be on the source side, the destination side, or both. If you only refer to one of the join keys, you will get nulls for the records that don't exist for that side. Coalesce solves this problem by using the first non-null value it finds. Does this make sense?

As an aside, you don't need coalesce for the a left join...only for a full join.

astmart SSC Journeyman Points: 75 More actions · Answer 5

astmart

SSC Journeyman

Points: 75

December 23, 2010 at 6:59 am

#1265907

Great Article..and well laid out..

53280comma1 Old Hand Points: 365 More actions · Answer 6

The other option of course is to build the tolerance into the cte that is then compared with intersect or except, as in the following pseudo code (i'm using a phone here, forgive me for not using the posted example tables)

With a as (

Select id , cast( randomnum as numeric(3,2) from table1

), b as (

Select id , cast( randomnum as numeric(3,2) from table2

)

Select * from a

Except

Select * from b

;

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 7

The calculation of the column

ISNULL(S.RandomNumber, 0) - ISNULL(D.RandomNumber, 0) AS RandomNumberDiff

requires some attention. It is important to choose a numeric value that is far from your threshold number or it will yeild incorrect results. For example if the RandomNumber value in one set is 0 and the key is missing in the other then the difference will calculate as 0, even though they actually are not equal. If no good dummy value is apparent then logic like the following is needed:

SELECT COALESCE(S.ID, D.ID) AS ID,

S.RandomNumber AS SrcRandomNumber,

D.RandomNumber AS DestRandomNumber,

S.RandomNumber - D.RandomNumber AS RandomNumberDiff

FROM SRC S

FULL JOIN DEST D ON S.ID = D.ID

WHERE ABS(S.RandomNumber - D.RandomNumber) > @Tolerance

or (S.RandomNumber is null and D.RandomNumber is not null)

or (S.RandomNumber is not null and D.RandomNumber is null);

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 8

tom.w.brannon (4/23/2013)
The calculation of the column
ISNULL(S.RandomNumber, 0) - ISNULL(D.RandomNumber, 0) AS RandomNumberDiff
requires some attention. It is important to choose a numeric value that is far from your threshold number or it will yeild incorrect results. For example if the RandomNumber value in one set is 0 and the key is missing in the other then the difference will calculate as 0, even though they actually are not equal. If no good dummy value is apparent then logic like the following is needed:
SELECT COALESCE(S.ID, D.ID) AS ID,
S.RandomNumber AS SrcRandomNumber,
D.RandomNumber AS DestRandomNumber,
S.RandomNumber - D.RandomNumber AS RandomNumberDiff
FROM SRC S
FULL JOIN DEST D ON S.ID = D.ID
WHERE ABS(S.RandomNumber - D.RandomNumber) > @Tolerance
or (S.RandomNumber is null and D.RandomNumber is not null)
or (S.RandomNumber is not null and D.RandomNumber is null);

+1 I actually prefer retaining the nulls, and testing for those conditions as well.

lunds007 Newbie Points: 3 More actions · Answer 9

EXCEPT Shouldn't really be used when comparing tables with lots of columns. Investigating which fields are actually different will take more time than using the FULL OUTER JOIN with Where clause to filter on only non matched records. If your compare query has an added column to tell you which column(s) are not matching, then you won't spend time investigating which fields are different.