JOINs and ONs

I have worked with various forms of SQL over many years and have recently started using SQL Server for the first time. In the time I've used, I've created various reports, programs etc. mainly performing selections of data for reporting purposes. Joins have, of course, been necessary in many of these.

On using SQL Server, I have seen one thing though that I haven't seen before when using Joins and that is where the ONs appear in a list and seemingly not directly attached to the JOIN references listed before them. I must admit I have always quoted the JOIN followed immediately by the ON setting.

Does SQL Server re-sort the commands itself while processing so that it fits the JOINs and ONs or is there a more definite reason for this way of creating the SELECT.

Regards

Jim Jackson

What you describe is often a form of 'nesting' JOINs.

It would be so much easier to read if folks would use parentheses.

For Example:

SELECT {ColList}

FROM TableA ta

JOIN Table B tb

JOIN Table C tc

ON tb.PKCol = tc.FKCol

ON ta.PKCOl = tb.FKCol

WHERE {Criteria}

This would be easier to read and understand by simply using parentheses.

SELECT {ColList}

FROM TableA ta

JOIN ( Table B tb

JOIN Table C tc

ON tb.PKCol = tc.FKCol

)

ON ta.PKCOl = tb.FKCol

WHERE {Criteria}

Without the parentheses, the Query Processor is left to its own imagination in working out what it should do. Many times that will be what was desired -BUT sometimes, there can be unexpected results.

With simple consecutive JOINS, it is best to follow each JOIN with its matching ON condition.

|||

Thanks, Arnie

I must admit I'd figured the nesting answer and have tried a couple of test ones myself to get the feel for them.

But you are right about using parentheses especially when you are using a couple of right outer joins in the middle of a set of approx. 6 nested joins separated by another inner join or two. I'm trying to figure one out that was provided for a report in our CRM application which is just being implemented. And I'm still not sure if I've got the brackets in the places they should be appearing!

Regards

Jim