April 22, 2016 at 4:18 am
Guys,
I am trying to tidy up some code (for readability) that does not meet our coding guidelines. Historically one developer used the coding pattern
Select A.ID,B.ID,C.ID from A Join B Join C ON C.B_ID = B.ID ON B.A_ID = A.ID
I am rewriting this to the 'modern' format
Select A.ID,B.ID,C.ID FROM A JOIN B ON B.A_ID = A.ID JOIN C ON C.B_ID = B.ID
The following blocks of code are not returning me the same results:
Block 1
LEFT OUTER JOIN Contact_Group_Details_T AS ConDet (NOLOCK)
INNER JOIN Shared_Addresses_T AS ConAd (NOLOCK)
ON ConDet.Address_ID = ConAd.Address_ID
AND (ConDet.Expiry_Date_DT IS NULL -- Address is still current...
OR (CONVERT(DATETIME,ConDet.Expiry_Date_DT,106)) >= (CONVERT(DATETIME,GETDATE(), 106)))
AND ConDet.Address_Code_ID = (SELECT Contact_Correspondence_Address_Code_ID FROM Shared_Options_T (NOLOCK))
ON ConGrp.Group_ID=ConDet.Group_ID
Block 2
LEFT OUTER JOIN Contact_Group_Details_T AS ConDet (NOLOCK) ON ConGrp.Group_ID=ConDet.Group_ID
INNER JOIN Shared_Addresses_T AS ConAd (NOLOCK)
ON ConDet.Address_ID = ConAd.Address_ID
AND (ConDet.Expiry_Date_DT IS NULL -- Address is still current...
OR (CONVERT(DATETIME,ConDet.Expiry_Date_DT,106)) >= (CONVERT(DATETIME,GETDATE(), 106)))
AND ConDet.Address_Code_ID = (SELECT Contact_Correspondence_Address_Code_ID FROM Shared_Options_T (NOLOCK))
The code blocks are identical except that Block 2 has the ON clause for the LOJ moved to match the join
Block 1 returns me one record for the ConDet and NULL for the ConAd
Block 2 returns me NULL
It is as if the Shared addresses has been treated as a subquery linked with the parent LOJ which means it returns in outer member even though the inner member is NULL.
If I change the CodAd to be a LEFT Join, I get three records in both formats - not sure why; need to investigate the records further
What is the safest way to refactor this code. Should I explicity make the CodAd a sub-select?
April 22, 2016 at 4:44 am
There are a couple of ways to rewrite this code, discussed here. Choose one, document it, and encourage folks to stick with it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 22, 2016 at 5:15 am
Thanks Chris,
Looking again at the code, I had a lightbulb moment and realised that the question I had posed was a correct assumption confirmed by your linked post.
When you use the nested join syntax, INNER JOINS nested inside OUTER JOINs effectively inherit their join type from the outer join. You might get some very strange results if you have IJ inside LOJ inside ROJ 😀
FWIW I think the nested syntax is very hard to read and to debug. Does anyone actually use that syntax when writing new code. Surely if you have that sort of logic to process you would farm it out to a CTE where it is more explicit what is going on.
April 26, 2016 at 6:29 am
I've always written it as ...TableA JOIN TableB ON... adding LEFT/RIGHT as needed. Clarity suggests pretty strongly that you want the defining elements of the JOIN to be right next to the JOIN, not arrayed somewhere else in the query. I'd say this is a holdover from the ANSI89 days of using ...TableA, TableB WHERE ... syntax. In short, don't do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply