How sql server evaluates the multiple different joins?

Question

Hi, i have a general question about how sql server evaluates the joins.The query is

SELECT * 
FROM TableA 
INNER JOIN TableB ON TableB.id = TableA.id
LEFT JOIN TABLEC ON TABLEC.id = TABLEB.id

Q1: What tables is the left join based on? I know it will based on the TABLEC but what is the other one? Is it the result of the first inner join or the TABLEB specified in the left join condition?

Q2: Is "LEFT JOIN TABLEC ON TABLEC.id = TABLEB.id" equivalent to "LEFT JOIN TABLEC ON TABLEB.id = TABLEC.id"

Q3: Is the query equivalent to the following one? (with TABLEB.id replaced by TABLEA.id?)

SELECT * 
FROM TableA 
INNER JOIN TableB ON TableB.id = TableA.id
LEFT JOIN TABLEC ON TABLEC.id = TABLEA.id

Thank you!

Answer 1

Q1: It is based on the result of the inner join, therefore it will only LEFT JOIN with items that are in TableA AND TableB.

Q2: Yes

Q3: Yes, it's a consequence of question Q1.

Answer 2

SQL is a declarative language. When you declare 'A JOIN B JOIN C' there is no order of join involved. The end result has to match the required criteria, but the underlying implementation is free to choose any actual implementation order.

At a logical level the inner JOIN operator is associative so the order does not matter: 'A JOIN B JOIN C' is identical with 'A JOIN C JOIN B' which is identical with 'B JOIN A JOIN C' and so on and so forth.