Differences between forms of LEFT JOIN

Question

What is the difference between these query

SELECT
    A.AnyField
FROM
    A
    LEFT JOIN B ON B.AId = A.Id
    LEFT JOIN C ON B.CId = C.Id 

and this another query

SELECT
    A.AnyField
FROM
    A
    LEFT JOIN
    (
        B
        JOIN C ON B.CId = C.Id
    ) ON B.AId = A.Id

Answer 1

Original answer:

They are not the same.

For example a left join b left join c will return a rows, plus b rows even if there are no c rows.

a left join (b join c) will never return b rows if there are no c rows.

Added later:

SQL>create table a (id int); 
SQL>create table b (id int);
SQL>create table c (id int);
SQL>insert into a values (1);
SQL>insert into a values (2);
SQL>insert into b values (1);
SQL>insert into b values (1);
SQL>insert into c values (2);
SQL>insert into c values (2);
SQL>insert into c values (2);
SQL>insert into c values (2);
SQL>select a.id from a left join b on a.id = b.id left join c on b.id = c.id;
         id
===========
          1
          1
          2

                  3 rows found

SQL>select a.id from a left join (b join c on b.id = c.id) on a.id = b.id;
         id
===========
          1
          2

                  2 rows found

Answer 2

The first query is going to take ALL records from table a and then only records from table b where a.id is equal to b.id. Then it's going to take all records from table c where the resulting records in table b have a cid that matches c.id.

The second query is going to first JOIN b and c on the id. That is, records will only make it to the resultset from that join where the b.CId and the c.ID are the same, because it's an INNER JOIN.

Then the result of the b INNER JOIN c will be LEFT JOINed to table a. That is, the DB will take all records from a and only the records from the results of b INNER JOIN c where a.id is equal to b.id

The difference is that you may end up with more data from b in your first query since the DB isn't dropping records from your result set just because b.cid <> c.id.

For a visual, the following Venn diagram shows which records are available