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 |