What intersection do multiple inner joins return? If I have a query:

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = B.ID


Would that return a different result to this query?

SELECT * FROM A
INNER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = A.ID


It seems to me that in the first query I would get the intersection of A,B,C. And in the second query I would get the intersection of A,B, and the intersection of A,C.

If that were the case, would I find NULL values in the columns for B/C where there is no intersection between B,C?
No, the two queries are the same.

Since A.ID = B.ID and A.ID = C.ID, then by definition B.ID = C.ID.

For any given ID x, if there's a row in A with the ID and a row in B with the ID, but no row in C with the ID, then the row(s) from A and B will be ignored, because both joins are INNER JOINs. Same holds true for A-C-B and B-C-A, of course.

So, when all the JOINs are INNER JOINs, the only way to get NULL for a given column is if that column is NULL in an existing row - you can't fill in a row of NULLs in one table because there's no matching row, as the lack of a matching row in one INNER JOINed table means the rows in the other tables aren't valid.

In other words - For a given row in B, if there's no matching row in C, then it's also true that there's no match for the rows in A JOIN B that use that B row. That B row (and any A rows that match it) won't be included because a row has to exist for all INNER JOINed tables.



You asked if anything changed if the query was A INNER JOIN B RIGHT OUTER JOIN C or A LEFT OUTER JOIN B INNER JOIN C.

It can change.

Under normal circumstance, joins will be evaluated from left to right (that is, from the first listed join to the last).

If for some reason you must modify that behavior, you do so by moving the position of the ON clause. Depending on your DBMS, you may also have to add parentheses to indicate the order in which the clauses should be evaluated (I tried MS SQL and MySQL; MySQL required the parentheses to work, MS SQL allowed them but did not require them). I've included the parentheses in my examples below that don't evaluate left to right, to help make that clear.

Examples:


A INNER JOIN B ON &lt;condition1&gt; RIGHT OUTER JOIN C ON &lt;condition2&gt;

This would first join A and B (requiring a match on &lt;condition1&gt; to associate rows from the two tables), then perform the right outer join to C (where all rows from C that meet the WHERE condition will be returned, and will be associated with any rows from the A-B join where &lt;condition2&gt; is met.
A INNER JOIN (B RIGHT OUTER JOIN C ON &lt;condition1&gt;) ON &lt;condition2&gt;

This, on the other hand, would join B and C (including all rows from C, that meet the WHERE clause, associated with any rows from B that meet &lt;condition1&gt;. Then, the results of the B-C join are inner joined with A, associated where there is a row from A and a row from B-C that match based on &lt;condition2&gt;. Note that here, you will get different results for A.id - B.id and A.id = C.id, as all rows will have a non-NULL C.id, but B.id will be NULL where there was no matching row from B.
A LEFT JOIN B ON &lt;condition1&gt; INNER JOIN C ON &lt;condition2&gt;

With the ON clauses in the usual places, this is evaluated left to right. So, first we taken all rows from A, associating them with any matching rows from B based on &lt;condition1&gt;; then, we do an inner join of the results of the A-B join with C, returning only the rows where a row from A-B matches a row from C based on &lt;condition2&gt;.
A LEFT JOIN (B INNER JOIN C ON &lt;condition1&gt;) ON &lt;condition2&gt;

Here, we're again forcing the B-C inner join to happen first, so we get rows when a row from B matches a row from C based on &lt;condition1&gt;; then, we take the results of the B-C inner join and match that to A (returning all rows from A, with any matching rows from B-C based on &lt;condition2&gt;


Here's a couple of SQLFiddle links showing the results of these joins as you move around the ON clauses: one for MySQL and one for MS SQL Server.

NOTE: I've said nothing about how this works with non-ANSI join syntax. If you don't know what I'm talking about - thank your lucky stars and move on. I don't know precisely how the precedence works with that syntax. I'd expect it to go through the list of tables, left to right (first mentioned to last). Obviously, since there are no ON clauses, those have no impact. There may be ways to force the order there to (perhaps parentheses again, presumably in the FROM clause?), but I don't know the answer. As most people (myself included) would strongly recommend against trying to use that syntax for your JOINs, I'll simply use that to avoid the question :-).