web-profile

MySQL join

TableA:

id name
1 Newton
2 Einstein
3 da Vinci
4 Galilei

TableB:

id name
1 Tesla
2 Newton
3 Darwin
4 da Vinci

inner join

select * from TableA
inner join TableB
on TableA.name = TableB.name
id name id name
1 Newton 2 Newton
3 da Vinci 4 da Vinci

full outer join

select * from TableA
full outer join TableB
on TableA.name = TableB.name
id name id name
1 Newton 2 Newton
2 Einstein null null
3 da Vinci 4 da Vinci
4 Galilei null null
null null 1 Tesla
null null 3 Darwin

left outer join

select * from TableA
left outer join TableB
on TableA.name = TableB.name
id name id name
1 Newton 2 Newton
2 Einstein null null
3 da Vinci 4 da Vinci
4 Galilei null null

left outer join (only left)

select * from TableA
left outer join TableB
on TableA.name = TableB.name
where TableB.id is null
id name id name
2 Einstein null null
4 Galilei null null

left outer join (left and right)

select * from TableA
full outer join TableB
on TableA.name = TableB.name
where TableA.id is null
or TableB.id is null
id name id name
2 Einstein null null
4 Galilei null null
null null 1 Tesla
null null 3 Darwin

Leave a Reply

Your email address will not be published. Required fields are marked *