JOIN physical location vs ON location

[LuluDev]

Why does the physical order of the JOIN... vs the ON.... matter? This first sql statment executes just fine, but the second SQL statement gives the errors:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "s.stno" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "g.student_number" could not be bound.

I am working with Microsoft SQL 2005

SELECT DISTINCT(s.sname)
FROM Student s
JOIN Grade_report g
ON s.stno = g.student_number
JOIN Section sec
ON g.section_id = sec.section_id
JOIN Course c
ON sec.course_num = c.course_number
AND c.course_name like 'ACC%'


SELECT DISTINCT(s.sname)
FROM Student s
JOIN Grade_report g
JOIN Section sec
JOIN Course c
ON s.stno = g.student_number
ON g.section_id = sec.section_id
ON sec.course_num = c.course_number
AND c.course_name like 'ACC%'

I am more familiar with the older WHERE clause style, and would likely most of the time use the format of the second select statement above, but it produces errors! So in general should the JOIN and the related ON clause stay physically close together. Or what tips or concepts should I have or know!

Thank-you!

[Phil Weber]

It matters because that's what the SQL parser expects to receive:

SELECT field1, field2, field3
FROM first_table
[INNER] JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

If you send SQL a JOIN without an ON immediately following it, it can't parse it and doesn't know how what to JOIN.

The older WHERE clause style is fine, by the way. If you're more comfortable with it, why not use it?

[LuluDev]

Thank-you for your help on the proper order. It seems that some book examples group all of the ONs together and all of the JOINs together in the physical order. But as you responded and as my example shows, I might have more success doing JOIN/ON, JOIN/ON etc.

Why use the ANSI style(s)? I am refreshing my SQL skills and the two SQL 2005 books that I have all use the JOIN method. So on my end I am learn-mode and trying to get familiar with the ANSI style. It might be a good idea for me to know both methods because I might run into both methods in existing code. I am sure under pressure I will revert back to my comfortable 'WHERE' method!

Also a book that I have suggest that using the ANSI style is the best approach. The book is called 'Professional SQL 2005 Programming' by Vieira and he says 'I highly recommend that you use the ANSI method since it has much better portability between systems and is also much more readable'.... The book goes on to say '... Microsoft has indicated that it may not continue to support the old syntax indefinitely. I find it very hard to believe, given the amount of legacy code out there, that Microsoft will dup the old syntax any time soon, but you never know'. The book goes on with other various reasons.