In SQL, What's the difference a ON condition following a Join vs at the end of multiple JOINS

Question

I have been having a hard time googling an answer for this, but.... can someone explain to me the difference between putting the ON condition of a JOIN with the the JOIN itself vs putting the ON at the end of all the other JOINs.

here is an example http://sqlfiddle.com/#!3/e0a0f/3

CREATE TABLE TableA (Email VARCHAR(100), SomeNameA VARCHAR(100))
CREATE TABLE Tableb (Email VARCHAR(100), SomeNameB VARCHAR(100))
CREATE TABLE Tablec (Email VARCHAR(100), SomeNameC VARCHAR(100))

INSERT INTO TableA SELECT '[email protected]', 'JoeA'
INSERT INTO TableA SELECT '[email protected]', 'JaneA'
INSERT INTO TableA SELECT '[email protected]', 'DaveA'
INSERT INTO TableB SELECT '[email protected]', 'JoeB'
INSERT INTO TableB SELECT '[email protected]', 'DaveB'
INSERT INTO TableC SELECT '[email protected]', 'JoeC'
INSERT INTO TableC SELECT '[email protected]', 'DaveC'


SELECT TOP 2 a.*,
             b.*,
             c.*
FROM   TableA a
       LEFT OUTER JOIN TableB b
                    ON a.email = b.email
       INNER JOIN TableC c
                    ON c.Email = b.email;

SELECT TOP 2 a.*,
             b.*,
             c.*
FROM   TableA a
       LEFT OUTER JOIN TableB b
       INNER JOIN TableC c
                    ON c.Email = b.email
                    ON a.email = b.email;

I don't understand why these two SELECT statements produce different results.

Answer 1

What matters is orders of joins. Treat your expressions as if every join produced temporary "virtual" table.

So when you write

FROM TableA a 
LEFT OUTER JOIN TableB b ON a.email = b.email
INNER JOIN TableC c ON c.Email = b.email ;

then order is as follows:

1. TableA is left joined to TableB producing temporary relation V1
2. V1 is inner joined to TableC.

Meanhwile when you write:

FROM TableA a 
LEFT OUTER JOIN TableB b 
INNER JOIN TableC c ON c.Email = b.email ON a.email = b.email;

then order is as follows:

1. TableB is inner joined to TableC producing temporary relation V1.
2. TableA is left joined to V1.

Thus results are different. It is generally recommended to use parenthesis in such situations to improve readability of the query:

FROM TableA a 
LEFT OUTER JOIN
  (TableB b INNER JOIN TableC c ON c.Email = b.email)
ON a.email = b.email;