CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
clause_yl
clause_yl

Reputation: 21

What is the difference between writing sql join on together and separately?

My question is what's the difference between

SELECT id, name
FROM table_a a
JOIN table_b b
LEFT JOIN table_c c
ON a.id = b.id and a.name = c.name

and

SELECT id, name
FROM table_a a join table_b b on a.id=b.id
left join table_c on a.name = c.name

Upvotes: 0

Views: 42

Answers (1)

Akina
Akina

Reputation: 42844

CREATE TABLE table_a (id INT, name INT) 
SELECT 1 id, 1 name UNION SELECT 2,2 UNION SELECT 4,4;
CREATE TABLE table_b (id INT) 
SELECT 1 id UNION SELECT 2 UNION SELECT 3;
CREATE TABLE table_c (name INT) 
SELECT 1 name UNION SELECT 3 UNION SELECT 4;

SELECT *
FROM table_a a
JOIN table_b b
LEFT JOIN table_c c
ON a.id = b.id and a.name = c.name

a.id | a.name | b.id | c.name
-:   | ---:   | -:   | ---:
 4   |    4   |  1   | null
 2   |    2   |  1   | null
 1   |    1   |  1   |    1
 4   |    4   |  2   | null
 2   |    2   |  2   | null
 1   |    1   |  2   | null
 4   |    4   |  3   | null
 2   |    2   |  3   | null
 1   |    1   |  3   | null

All pairs from rows of first two tables are generated (JOIN without ON acts as CROSS JOIN), then third table is joined to matched pairs only.

SELECT *
FROM table_a a join table_b b on a.id=b.id
left join table_c c on a.name = c.name

a.id | a.name | b.id | c.name
-:   | ---:   | -:   | ---:
 1   |    1   |  1   |    1
 2   |    2   |  2   | null

Only matched pairs from rows of first two tables are generated, then third table is joined to matched pairs.

db<>fiddle here

Upvotes: 1

Related Questions