What does it mean to have several ON clauses from INNER JOINS together

Question

I stumbled upon this T-SQL query created on a SQL Server 2005 database:

Select s_bs.ScheduleHeaderId, 
       s_bs.ScheduleBatchScheduleId, 
       FlowRateOperational.FlowRate, 
       BatchScheduleFlowRateOperational.EffectiveStartTime, 
       BatchScheduleFlowRateOperational.EffectiveEndTime
From BatchSchedule as bs 
Inner Join ConnectionPoint as cp on bs.ConnectionPointId = cp.ConnectionPointId 
Inner Join ScheduleBatch as s_b 
Inner Join ScheduleConnectionPoint as s_cp 
Inner Join ScheduleBatchSchedule as s_bs 
            on s_cp.ScheduleConnectionPointId = s_bs.ScheduleConnectionPointId 
            on s_b.ScheduleBatchId = s_bs.ScheduleBatchId 
            on cp.ConnectionPointName = s_cp.ConnectionPointName and bs.BatchID = s_b.BatchID 
Inner Join BatchScheduleFlowRateOperational on bs.BatchScheduleId = BatchScheduleFlowRateOperational.BatchScheduleId 
Inner Join FlowRateOperational on BatchScheduleFlowRateOperational.FlowRateOperationalId = FlowRateOperational.FlowRateOperationalId

I am not a SQL expert by far but at least I think I know how to join tables and I had never seen this way of joining tables before.

Is having several ON clauses together after their JOINS producing different results or increasing performance?

Why couldn't this person just move the joins around and keep the ON clauses beside their corresponding JOIN?

Thanks for any light you can shed on this "mystery" :)

Answer 1

When you have only inner joins, the order of joining does not matter. The result sets are the same. The two versions below are equivalent.

The common way of joining:

FROM 
    tableA AS a
  JOIN
    tableB AS b
      ON b.aid = a.aid
  JOIN
    tableC AS c
      ON c.bid = b.bid

And the not so common (notice the parentheses are only there for clarity of the precedence, they can be removed safely, as in your query):

FROM 
    tableA AS a
  JOIN
      (   tableB AS b
        JOIN
          tableC AS c
            ON c.bid = b.bid
      )
    ON b.aid = a.aid

---

When you have outer joins though (LEFT, RIGHT, or FULL), the situation is different. If you replace the JOIN with LEFT JOIN in the queries above, then they are not equivalent and may produce different results sets.

Even one of the (many) joins matters. The following two are not equivalent:

FROM 
    tableA AS a
  LEFT JOIN
    tableB AS b
      ON b.aid = a.aid
  JOIN
    tableC AS c
      ON c.bid = b.bid

FROM 
    tableA AS a
  LEFT JOIN
          tableB AS b
        JOIN
          tableC AS c
            ON c.bid = b.bid

    ON b.aid = a.aid

Answer 2

The ON clause can be placed right after each INNER JOIN (which looks nicer to me). But sometimes it's inevitable to put the ON clauses for the first couple of joins after a later join. For example the table which provides the join condition is not yet available in the JOIN chain, for example:

SELECT * FROM 
A INNER JOIN 
B INNER JOIN 
C 
   ON B.Col1 = C.Col1 
   ON A.Col2 = C.Col2

Look that both ON clauses are referring to C so the associated ON clause could not appear earlier. It can be prevented by putting C first, but depending on the usage, one may find it less readable.

The above is just from the syntax point of view. There should be some performance considerations as well, which I'm not aware of.