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 |