Query with join equivalency?

Question

Are these two queries equivalent (assuming varying/any kinds of data in the table)? Are there any scenarios in which they would return different results?

Query 1:

select * from tablea a
left join tableb b on a.keyacol = b.keybcol
inner join tablec c on c.keyccol = b.keybcol;

Query 2:

select * from tablea a
left join (
select b.*, c.* from tableb b
inner join tablec c on c.keyccol = b.keybcol
) sub on a.keyacol = sub.keybcol;

Answer 1

No, they are not equivalent. Example:

CREATE TABLE a
( keya int ) ;

CREATE TABLE b
( keyb int ) ;

CREATE TABLE c
( keyc int ) ;

INSERT INTO a
  VALUES
  (1) ;

INSERT INTO b
  VALUES
  (1),(2) ;

INSERT INTO c
  VALUES
  (2) ;

Results:

SELECT * 
FROM  a
  LEFT JOIN b 
    ON a.keya = b.keyb
  INNER JOIN c 
    ON c.keyc = b.keyb ;

Result
----------------------
| keya | keyb | keyc |
----------------------


SELECT * 
FROM a
  LEFT JOIN 
    ( SELECT b.*, c.* 
      FROM  b
        INNER JOIN c 
          ON c.keyc = b.keyb
    ) sub 
    ON a.keya = sub.keyb ;

Result
----------------------
| keya | keyb | keyc |
----------------------
|   1  | NULL | NULL |
----------------------

As to why this happens, a LEFT JOIN b INNER JOIN c is parsed as (a LEFT JOIN b) INNER JOIN c which is equivalent to (a INNER JOIN b) INNER JOIN c because the condition on the INNER join cancels the LEFT join.

You can also write the second query in this form - without subquery - which is parsed as a LEFT JOIN (b INNER JOIN c) because of the different placing of the ON clauses:

SELECT * 
FROM a
  LEFT JOIN 
        b
      INNER JOIN c 
        ON c.keyc = b.keyb
    ON a.keya = b.keyb ;

Result
----------------------
| keya | keyb | keyc |
----------------------
|   1  | NULL | NULL |
----------------------