Using FULL JOINs to Compare Datasets

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mark Murphy » Using FULL JOINs to Compare Datasets

24 posts, Page 2 of 3

««1 2 3 »»

Using FULL JOINs to Compare Datasets

Rate Topic

Display Mode

Topic Options

Author

Message

rchantler

Posted Monday, September 27, 2010 1:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 104, Visits: 304

Good article, but are you actually creating random numbers in your example, using NewID()? Random and pseudo-random numbers have properties that this technique may not create.

Post #993961

markoos1

Posted Monday, September 27, 2010 2:06 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:31 PM
Points: 18, Visits: 173

I'm just using the NEWID() function as a way to create unique random numbers for each row. When you use RAND() in a query like this, it generates the same number for each row.

This is just for illustration in this example. In real situations, the numeric measures come from the source and destination queries, and aren't random numbers.

Post #993995

le_billy

Posted Monday, September 27, 2010 6:19 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:39 PM
Points: 19, Visits: 225

Thank you for the great article, I am seeing some work cut out for me to improve my previously written query BigGrin

Billy

Post #994134

Hardy21

Posted Monday, September 27, 2010 10:37 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:11 AM
Points: 877, Visits: 1,159

Nice article and good examples. Thanks

Thanks

Post #994181

mishaluba

Posted Tuesday, September 28, 2010 7:17 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, June 15, 2013 2:08 PM
Points: 525, Visits: 624

I love the articles with examples which work "out of the box". This is one of those. These very helpful techniques are going into my tool box. Thank you!

Post #994404

gpatterson-825050

Posted Wednesday, September 29, 2010 7:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 10:04 AM
Points: 48, Visits: 260

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

Post #995212

markoos1

Posted Wednesday, September 29, 2010 7:59 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:31 PM
Points: 18, Visits: 173

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.

Post #995236

ashishtiwarivds

Posted Friday, November 26, 2010 9:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, August 25, 2012 8:39 AM
Points: 2, Visits: 18

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!!

Post #1026924

markoos1

Posted Sunday, November 28, 2010 9:24 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:31 PM
Points: 18, Visits: 173

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.

Post #1027058

astmart

Posted Thursday, December 23, 2010 6:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 3, Visits: 270

Great Article..and well laid out..

Post #1038757

« Prev Topic | Next Topic »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions