Geek Answers Handbook

A query with a union equivalent?

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

Request 1:

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

Request 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;
+5
source share
3 answers

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 |
----------------------

How this happens is a LEFT JOIN b INNER JOIN cparsed as (a LEFT JOIN b) INNER JOIN c, which is equivalent (a INNER JOIN b) INNER JOIN c, because the condition in the join INNERcancels the join LEFT.

You can also record the second request in this form - without a subquery - which is analyzed as a a LEFT JOIN (b INNER JOIN c)result of the different placement of offers ON:

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 |
----------------------
+7

INNER JOIN , /. tableb , tablec, .

LEFT JOIN (tablea), (tableb sub).

, , . , , .

, . select * from / :

  • Query 1 tablea, tableb tablec
  • Query 2 tablea sub ( tableb tablec) = tablea, tableb tablec

.

So NO, .

+3

.

, , A:

  • B C;
  • B, C;
  • C, B;
  • B C.

1.

- B C , C .

+3
source

More articles:


All Articles