1

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?

  • You can certainly try both queries, right? You'll see that you get same results from them. And certainly not any rows with nulls (in the id columns) – ypercubeᵀᴹ Nov 14 '17 at 11:42
  • 1
    Think about any (one) row from each table, A, B and C. If B.ID = A.ID and C.ID = A.ID, doesn't that mean that C.ID = B.ID? – ypercubeᵀᴹ Nov 14 '17 at 11:45
  • well I was thinking that you could have A.ID = B.ID and A.ID = C.ID and B.ID != C.ID (or rather that B and C have a different intersection with A). But I have found that this is an intersection of all 3 – Zach Smith Nov 14 '17 at 11:55
  • @ypercubeᵀᴹ - I was wondering if there could be nulls in columns other than the id. i.e. if A has all IDs, and B and C each have a partial list of those IDs I was wondering if this would result in what appears to be a 'left' join in terms of the – Zach Smith Nov 14 '17 at 11:58
5

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 <condition1> RIGHT OUTER JOIN C ON <condition2>

    This would first join A and B (requiring a match on <condition1> 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 <condition2> is met.

  • A INNER JOIN (B RIGHT OUTER JOIN C ON <condition1>) ON <condition2>

    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 <condition1>. 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 <condition2>. 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 <condition1> INNER JOIN C ON <condition2>

    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 <condition1>; 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 <condition2>.

  • A LEFT JOIN (B INNER JOIN C ON <condition1>) ON <condition2>

    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 <condition1>; 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 <condition2>

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

  • Thanks. What if the join to C is a right outer join? Will that union be relative to A or B? – Zach Smith Nov 14 '17 at 20:01
  • If you're doing A INNER JOIN B RIGHT OUTER JOIN C, then you'll get the intersection of A and B, but when you join to C, you'll get rows from C even if there's no matching row in the intersection between A and B. In those cases, you'll get NULL for the A and B columns. – RDFozz Nov 14 '17 at 20:12
  • Ah yes. Sorry I should have realized that. One more question.. What about A LEFT OUTER JOIN B INNER JOIN C? Would that result in a join of A and B, then inner joined to C? I could imagine nulls for B if that were the case – Zach Smith Nov 14 '17 at 20:30
  • 1
    Yes, as the left outer join would be resolved first, you'd have NULLs for B where the row in A had no match. I've edited the answer to cover how it works at that level; it can actually change based on the placement of the ON clause. – RDFozz Nov 14 '17 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.