JOIN ON Clause - Left side vs Right Side, which goes first?? 2

[kjv1611]

So this should be a simple, elementary, question, that I should know like the back of my hand. I've been assuming one thing or else learned it early on, but now find myself wondering, does it even matter at all.


So If I have 2 tables, #Table1 and Table2, and I want to do a LEFT Join where I look for all records in table1 and only records from table2 that match table1, does it matter if I put the T1_ID on the left or right in the ON portion of the JOIN statement?

So consider:

SELECT	COUNT(1)
FROM	Table1 a WITH (NOLOCK)
LEFT JOIN	Table2 ci WITH (NOLOCK) ON ci.AcctID = a.AcctID

SELECT	COUNT(1)
FROM	Table1 a WITH (NOLOCK)
LEFT JOIN	Table2 ci WITH (NOLOCK) ON a.AcctID = ci.AcctID

So when I tested this on a production server, I got the same count either way I ran it. So, does this tell me that the location in the ON Clause does not matter, even if I use a LEFT JOIN? Does it only matter where I have the table/view/object name in the FROM Clause? It seems to be that way, but I wanted to verify.

Historically, I've always thought you should put the first table/object in the JOIN first, so T1_ID = T2_ID, and not the other way around. But perhaps that's just good form, but not required.

Can anyone shed any light on this? Sorry for this basic, low level question.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

The order of the column names does not matter. I usually put the column from the left table first (like you do), but it is not required. I do it for readability and consistency.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Cool! Thanks for the help!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Olaf Doschke]

Math, commutativity. a=b and b=a have the same boolean result.
RIGHT vs LEFT join make a difference in which table (or subselect) is fully put into the result and which is (outer) joined, not how to put the join condition.

For future readers: What's often overseen in outer join situations is, that putting conditions into where specifying conditions for the joined data make it an inner join. If you don't understand this, you can be a victim of unexpected results.

There are other databases, for which the commutativity of comparisons doesn't hold true, in case of string comparisons. Also MSSQL has a behaviour depending on settings. Especially when you have constant length char fields padded with spaces to the right, you still want to be able to write a condition like lastname='SMITH' instead of needing to write lastname='SMITH ', that's automatically adjusted with ANSI_PADDING on, which is therefore recommended. In another database I'm expert on you can nowadays also switch ANSI behaviour on, the native behaviour and solution to this comparisons is to only compare a string field up to the length of the right side, eg lastname 'SMITHSONIAN' = 'SMITH' would lead to true, because the first 5 chars are equal. SMITH is not padded, but SMITHSONIAN is trimmed.

All this doesn't matter for numeric comparisons, though, or uniqueidentifier comparisons or other binary comparisons.

So there can be cases it makes a difference whether you write a=b or b=a, but that is not dependent on LEFT or RIGHT join, but on value types and comparison settings.

Bye, Olaf.

 

[kjv1611]

Thanks for the notes, Olaf. I've forgotten about the OUTER becoming an INNER Join based on WHERE clause criteria a couple of times. Thankfully I noticed it in the query, and corrected it. Good reminder, though.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57