Consecutive joins without on clause

Question

I am reviewing a SQL stored procedure and ran into an issue understanding a section of joins. Can anyone please advise on how these joins work without using a "ON" clause?

 FROM
       SQL.TableA A LEFT JOIN SQL.TableB B LEFT JOIN SQL.TableC C 
               ON B.ITEMID = C.ITEMID

It is valid to do joins this way, but it looks like there's a missing `On` clause after `On B.ItemId = C.ItemId`. Can you show the full statement? — Siyual, Jul 25 '14 at 14:30
@GordonLinoff has discussed [this previously](http://stackoverflow.com/a/16471286/2589202) — crthompson, Jul 25 '14 at 14:34
What @RBarryYoung is looking for is something like sql server, mysql, sqlite, oracle... something like that. — crthompson, Jul 25 '14 at 14:35
Did you try testing it on some test tables to analyze the results? Did you check the manual of your DB product to see what the default behavior is for JOINs with no ON clause? — Tab Alleman, Jul 25 '14 at 14:41

score 1 · Accepted Answer · answered Jul 25 '14 at 15:29

Your query could be translated to

FROM
   SQL.TableA A LEFT JOIN 
   SQL.TableB B ON B.ITEMID = C.ITEMID LEFT JOIN
   SQL.TableC C ON B.ITEMID = C.ITEMID

which as you can see does not make a lot of sense as A isn't being joined
I would expect something like:

FROM
   SQL.TableA A LEFT JOIN 
   SQL.TableB B LEFT JOIN
   SQL.TableC C ON B.ITEMID = C.ITEMID
   ON A.ITEMID = C.ITEMID

Or it would be valid query written in this form

FROM
   SQL.TableA A LEFT JOIN 
   SQL.TableB B LEFT JOIN
   SQL.TableC C ON ColumnThatAandBTablesHave = C.ITEMID

Consecutive joins without on clause

1 Answers1