CODEWITHSUNDEEP
mysqlsqldatabase
Tang3
Tang3

Reputation: 133

What is the difference between left join (table1, table2) and left join table1 left join table2

What is the difference between the two sql queries?

select * from a
left join (b, c)
on a.id = b.uid and a.id = c.uid

select * from a
left join b on a.id = b.uid
left join c on a.id = c.uid

Upvotes: 1

Views: 896

Answers (2)

Jiri Tousek
Jiri Tousek

Reputation: 12440

Lets have this data:

A       B        C
id      uid      uid
--      ---      ---
 1        1        2
 2

First, the second query:

select * from a
left join b on a.id = b.uid
left join c on a.id = c.uid

 ID UID  UID
 -- ---- ----
  1    1 NULL
  2 NULL    2

This should come as no surprise - second column is joined from b and where there's no data in b, NULL is used (outer join); third column behaves the same, just for c.

The first query, rewritten with CROSS JOIN (which it is equivalent to) to be ANSI-compliant:

select * from a
left join (b CROSS JOIN c)
on a.id = b.uid and a.id = c.uid

 ID UID  UID
 -- ---- ----
  2 NULL NULL
  1 NULL NULL

Why there are all NULLs?

First, the CROSS JOIN is performed, but that results in a resultset with just one row:

b.UID   c.UID
-----   -----
    1       2

Then, the left join is performed, but there's no row in the result of the cross join that would have same uid for both b and c, so no row can be matched for either row in a.

Upvotes: 1

Noor A Shuvo
Noor A Shuvo

Reputation: 2807

 select * from a
 left join (b, c)
 on a.id = b.uid and a.id = c.uid

is equivalent to

select * from a 
left join (b cross join c)
on (a.id = b.uid and a.id = c.uid)

Here you can find the details https://dev.mysql.com/doc/refman/5.7/en/join.html

Upvotes: 0

Related Questions