May 3, 2011 at 12:57 pm
I never saw this before until recently.
A large query with many joins has several sections where "INNER JOIN" or "OUTER JOIN" will list the ON keyword several times during the join.
It appears to be happening where they are joining a new table to the group and they are joining the new table on multiple columns of that single new table to multiple columns of other tables already in the query.
I Googled but didn't have much luck finding any examples of this, "ON" being too common a word....
May 3, 2011 at 1:05 pm
you have to use the AND for subsequent AND join criteria:
SELECT *
FROM EMPLOYEE
INNER JOIN GEOGRAPHY
ON EMPLOYEE.CITYTBLKEY = GEOGRAPHY.CITYTBLKEY
AND EMPLOYEE.COUNTYTBLKEY= GEOGRAPHY.COUNTYTBLKEY
AND EMPLOYEE.STATETBLKEY= GEOGRAPHY.STATETBLKEY
Lowell
May 3, 2011 at 1:06 pm
Something like this?
Select *
From sometable st
LEFT JOIN stable2 ac
LEFT JOIN stable3 nc
ON ac.somefield = nc.somefield
AND ac.somefield + 1 = nc.somefield
ON st.somefield = ac.somefield
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 1:07 pm
SQLRNNR:
Yes, I went and looked at it in more detail and realized it seemed to have nested joins going on.
It was just not formatted in a "friendly way".
Although I don't think I've seen THAT done before either... wow, I must have been living under a rock or something???
It is setup as:
FROM table1
INNER JOIN table2
LEFT JOIN TABLE 3
ON TABLE3 = TABLE2
LEFT JOIN blah blah blah
ON TABLE2 ......
Is that a nested join, or something else entirely?
May 3, 2011 at 1:10 pm
I wouldn't say that it is a common thing. I hadn't seen anything like it until 8-9 months ago. It is done because of some criteria in the business. It works and I haven't delved into it enough to better understand it. It is a legit join though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 1:15 pm
Looks like maybe this is a GROUPED JOIN.... http://www.bennadel.com/blog/1059-Grouping-JOIN-Clauses-In-SQL.htm
Though the code I'm looking at doesn't have any opening or closing ().
May 3, 2011 at 1:22 pm
That would be it. Parentheses are optional.
From the link you posted.
In approach four, I talk about "processing groups" and intermediary results, but I don't actually know what is going on behind the scenes. For all I know, the join grouping just gets translated into backend logic that the SQL server is following when it joins all three tables together.
I love that statement. Let me check with somebody who might understand it better and see if he can explain it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 1:35 pm
SQLRNNR (5/3/2011)
Something like this?
Select *
From sometable st
LEFT JOIN stable2 ac
LEFT JOIN stable3 nc
ON ac.somefield = nc.somefield
AND ac.somefield + 1 = nc.somefield
ON st.somefield = ac.somefield
I've seen this off and on over the years. I think (though I'm not sure) it doesn't make any difference to SQL Server. I think it is a leftover construction from when it used to be *= instead of LEFT JOIN ON. Since what's being joined is explicitly referenced it shouldn't make a difference where the ON statement is in this kind of nesting.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 3, 2011 at 1:35 pm
This MSDN link might help explain it a bit better
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 3, 2011 at 1:40 pm
WayneS (5/3/2011)
This MSDN link might help explain it a bit better
That was very handy.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2011 at 2:03 pm
I seem to recall reading that there is an example of this in the AdventureWorks database... it's also used when you are joining multiple tables, and the tables don't have a straight join path. I'll have to look for this later on tonight...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 3, 2011 at 2:18 pm
So what is the real benefit of doing this....
I ran across this: http://blog.effiproz.com/2011/03/grouping-join-clauses.html
Which was interesting, is this a sort of hold over from the older style of putting your criteria in the WHERE clauses?
(Which I think someone else mentioned above)
I don't suppose anyone found any other real write ups about this?
That MSDN forum was interesting and they were basically saying in that case it was just a more difficult way to write your TSQL?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply