SQL Server Outer joins

Question

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?

When you tested this before asking, was there something ambiguous about the results that you didn't understand? — Tab Alleman, Sep 17 '15 at 19:24
If in doubt, or to make it explicit even if it is the default action, I suggest the use of parentheses. — Andrew Morton, Sep 17 '15 at 19:53
possible duplicate of [Does the join order matter in SQL?](http://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql) — Andrew Morton, Sep 17 '15 at 19:55

Sean Adkinson · Accepted Answer · 2015-09-18T19:23:38.287

1

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).

edited Sep 18 '15 at 19:23

answered Sep 17 '15 at 18:25

Sean Adkinson

8,425
3
45
64

But to the left doesn't necessarily mean immediately to the left if there are multiple tables, right? Like in my 3 table example TableA is to the left of TableC, but not immediately – user1612851 Sep 17 '15 at 18:41
@user1921849 That's basically correct, but you might be thinking about it weird. Each JOIN builds on whatever tables have already been "joined up" previously, so each JOIN always has a table on the left (specified first), and a table on the right (specified second). If I say " A left join B left join C", that is the same as "(A left join B) left join C", where "A left join B" is created first, then C is joined next. So the second LEFT JOIN has the combined AB table on the left, and C on the right. Make sense? – Sean Adkinson Sep 18 '15 at 19:28

score 0 · Answer 2 · edited Sep 18 '15 at 21:18

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.

SQL Server Outer joins

2 Answers2