CODEWITHSUNDEEP
sql-server-2008left-joininner-join
zs2020
zs2020

Reputation: 54514

How sql server evaluates the multiple different joins?

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!

Upvotes: 0

Views: 378

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294247

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.

Upvotes: 1

David Espart
David Espart

Reputation: 11780

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.

Upvotes: 2

Related Questions