CODEWITHSUNDEEP
sqlsql-server
user1612851
user1612851

Reputation: 1214

SQL Server Outer joins

When doing an outer join, is it the order of the tables that matter or the order of the ON clause?

For example is 

FROM TABLEA A 
LEFT JOIN TABLEB B ON A.id = B.id

the same as 

FROM TABLEA A 
LEFT JOIN TABLEB B ON B.id = A.id

What about if you have multiple tables? Is it a LEFT JOIN if the first table out of many is the one you want all rows from regardless of the ON clause?

For example, 

FROM TABLEA A 
LEFT JOIN TABLEB B ON A.id = B.id 
LEFT JOIN TABLEC C ON C.ID = A.ID

Does it take all the rows from TABLEA because it is to the left in the table list or the rows from C because it is on the left in the ON clause?

Upvotes: 0

Views: 77

Answers (2)

HLGEM
HLGEM

Reputation: 96572

FROM TABLEA A LEFT JOIN TABLEB B ON A.id=B.id LEFT JOIN TABLEC C ON C.ID=A.ID

FROM TABLEA A LEFT JOIN TABLEB B ON B.id=A.id LEFT JOIN TABLEC C ON A.ID=C.ID

These both would return the same results.

FROM TABLEA A LEFT JOIN TABLEB B ON B.id=A.id LEFT JOIN TABLEC C ON B.ID=C.ID

This might or might not return the same results depending on what data is actually in table b and table C because table C is related to table b not directly related to table A. Personally I would always treat this as being different by definition than the first set of joins and that if is is the same that is accidental at this point in time.

When writing multiple joins especially when you have Outer joins, I personally find it helpful to start with the parent table (that usually being the one you want on the left side of the join) first and add any other other inner join tables before doing the left joins. If I have child and grandchild tables (vice multiple child tables), then I try to do in descending order of parent, child, grandchild to be clear what is related to what.

Upvotes: 0

Sean Adkinson
Sean Adkinson

Reputation: 8605

LEFT JOIN means take the table on the left (the first one specified), and join the rows from the table on the right (the second one specified). It will join them up based on the ON condition being true. Since the ON condition just needs to be true, the way it is written doesn't matter at all, it's just an expression that is evaluated.

LEFT JOIN ensures that every row from the table on the left is retained, and joined with NULLs if there is no row to join up to it from the table on the right. So that means that order of the tables is certainly significant.

If the table ordering was reversed, and there were only two tables, a RIGHT JOIN would have the same effect (i.e. keep the rows from the second table specified).

Upvotes: 1

Related Questions