Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using FULL JOINs to Compare Datasets Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 1:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:35 AM
Points: 121, Visits: 370
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
Posted Monday, September 27, 2010 2:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:32 PM
Points: 18, Visits: 203
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
Posted Monday, September 27, 2010 6:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 3, 2013 12:58 PM
Points: 21, Visits: 235
Thank you for the great article, I am seeing some work cut out for me to improve my previously written query

Billy
Post #994134
Posted Monday, September 27, 2010 10:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 1,101, Visits: 1,363
Nice article and good examples. Thanks

Thanks
Post #994181
Posted Tuesday, September 28, 2010 7:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 536, Visits: 756
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
Posted Wednesday, September 29, 2010 7:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:35 PM
Points: 49, Visits: 300
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
Posted Wednesday, September 29, 2010 7:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:32 PM
Points: 18, Visits: 203
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
Posted Friday, November 26, 2010 9:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, November 28, 2010 9:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:32 PM
Points: 18, Visits: 203
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
Posted Thursday, December 23, 2010 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:32 AM
Points: 3, Visits: 306
Great Article..and well laid out..
Post #1038757
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse