CODEWITHSUNDEEP
sql
Eduardo Carísio
Eduardo Carísio

Reputation: 415

Differences between forms of LEFT JOIN

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

Upvotes: 3

Views: 97

Answers (3)

Todd Cunningham
Todd Cunningham

Reputation: 1

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

In this query you are LEFT JOINing C with B which will give you all records possible with B whether or not there is a match to any records in C.

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

In this query you are INNER JOINing C with B which will result in matching B and C records.

Both queries will give you the same result set as you are only pulling records from A so you will not see what records had matches and what did not in regards to B and C.

Upvotes: -1

JNevill
JNevill

Reputation: 50034

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

enter image description here

Upvotes: 2

jarlh
jarlh

Reputation: 44766

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

Upvotes: 3

Related Questions