What's the difference between a standard INNER JOIN and an INNER JOIN of an INNER JOIN?

Question

Can someone please explain the difference between this:

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

And this:

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

Example:

SELECT A.Something, B.Something, C.Something
INTO #TempTable
FROM TableA A
INNER JOIN TableB B
    INNER JOIN TableC C ON C.FK = B.PK
ON A.JoinField = B.ID

Answer 1

The results will be the same.
If only INNER JOIN is used anyway.

But the first way is the standard and proper way to do it.
It's more readable and less confusing.

How it affects the Explain Plan? That's up to the database.
SQL is a declarative language. Where we ask what we want, not how to pull it out.
(at least if one doesn't use T-SQL hints)

Here's an example T-SQL to demonstrate that the results are the same.

declare @A table (ID_A int, FK_B int);
declare @B table (ID_B int, FK_C int);
declare @C table (ID_C int);
insert into @A (ID_A, FK_B) values (11,21),(12,22),(13,null);
insert into @B (ID_B, FK_C) values (21,null),(22,32),(23,33);
insert into @C (ID_C) values (31),(32),(33);

-- A to B, B to C
select a.ID_A, b.ID_B, c.ID_C
from @A a
inner join @B b on (b.ID_B = a.FK_B)
inner join @C c on (c.ID_C = b.FK_C);

-- (B to C) to A
select a.ID_A, b.ID_B, c.ID_C
from @A a
inner join @B b inner join @C c on (c.ID_C = b.FK_C) 
on (b.ID_B = a.FK_B); 

Both result in:

ID_A ID_B ID_C
---- ---- ----
12   22   32

Answer 2

JOINs aren't always between tables. They're frequently between previous results of JOINs. Therefore, join order is important.

In the first query, we join table A to table B. In the first ON clause, we may only reference tables A or B. Lets say this produces a result that we'll informally call A_B. The second join then occurs between A_B and C. The second ON clause may contain references to A, B or C, but be clear that the A or B references are actually references to the A_B result produced by the first JOIN.

In the second query, we first join table B to table C. In that first ON clause (which is related to the second JOIN clause), we may only reference tables B or C. We'll call that result B_C. The second ON clause then completed the first JOIN, joining A to B_C and this second ON clause may again reference A, B or C, but this time B or C are references to B_C.¹

The clearest way to visualize how JOIN and ON clauses are matched up is to treat JOIN the same way as you would an opening parenthesis and ON as if it's a closing parenthesis. Then matching parenthesis indicate which ONs are matched to which JOIN and the nesting tells you what order the joins occur in.

---

¹Though these differences aren't often important when only using INNER joins and when each ON, in fact, only references one of two tables.

Answer 3

With an INNER JOIN there isn't a difference. The differences is when an OUTER JOIN comes into the play.

Let's say you have these tables:

Table A:

ID          BID
----------- -----------
1           1
2           NULL
3           2

Table B:

ID          BVAL
----------- ----------
1           X
2           Y

Table C:

BID         CVAL
----------- ----------
1           ABC

If you then query

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

you would get exactly one record:

ID          BID         ID          BVAL       BID         CVAL
----------- ----------- ----------- ---------- ----------- ----------
1           1           1           X          1           ABC

but this query

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

would give three:

ID          BID         ID          BVAL       BID         CVAL
----------- ----------- ----------- ---------- ----------- ----------
1           1           1           X          1           ABC
2           NULL        NULL        NULL       NULL        NULL
3           2           NULL        NULL       NULL        NULL

So as you can see, the nested JOIN is evaluated before the other.