SQL: Inner Joins inside Outer Joins

[Eluros]

Greetings,

I'm a bit surprised that these two queries give different results:

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b
JOIN Ctable c ON c.number = b.number
ON b.number = a.number
ORDER BY a.number;

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b ON b.number = a.number
JOIN Ctable c ON c.number = b.number
ORDER BY a.number

My expectation is that both of these would return the results that the first query does. The first query returns every row from TableA; the second row only returns results from TableA if they also exist in TableC.

Why does the join from C to B restrict TableA in the second query but not in the first query?

Thanks!

 

[KB]

The order of the joins does matter. In one query you are joining c to b first and in the other you are joining b to a first. The JOIN truly occurs at the ON statement, not the join statement.

There is a bunch of discussion about it here:
http://stackoverflow.com/questions/...n-clauses-affect-the-query-plan-in-sql-server

 

[uclabachelor]

The results are different because SQL has a default order of precedence. In order to get the same results, you have to use parenthesis just like in mathematical expressions.

2 * 2 + 3 is different from 2 * (2 + 3).

 

[Eluros]

Thanks for the responses. I didn't realize that the order of operations required inner joins to be evaluated before outer joins. Appreciate the responses!

 

[Fallen Kell]

I'm a bit surprised that these two queries give different results:

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b
JOIN Ctable c ON c.number = b.number
ON b.number = a.number
ORDER BY a.number;

Well, in here you are saying join table c to b where c.number = b.number and left outer join those results to with table a where b.number = a.number.

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b ON b.number = a.number
JOIN Ctable c ON c.number = b.number
ORDER BY a.number

Here you are saying left outer join table b to a with b.number = a.number and join those results with table c with c.number = b.number.

It really is two different things, especially with using the outer joins since that returns all the results from one of the tables plus the intersected results from the two tables (or results from another query/operation).