- Left outer-join:
A ⟕ B
- The result of the
left outer join (or simply
left join)
A ⟕ B
always
contains all records
of the "left" table (A),
even if the
join-condition
does not find any matching record
in the "right" table (B).
- A unmatched tuple from A
is processed "normally"
(discuss in the join operation)
- An matched tuple from A
is combined with a row of NULL value
(so that the resulting tuple have the
same columns as an
matched tuple)
- Example:
A: B:
X Y U V
+-----+-----+ +-----+-----+
| 1 | 7 | | 1 | 8 |
+-----+-----+ +-----+-----+
| 2 | 5 | | 2 | 3 |
+-----+-----+ +-----+-----+
| 3 | 4 | | 4 | 9 |
+-----+-----+ +-----+-----+
A ⟕A.X=B.U B:
X Y U V
+-----+-----+-----+-----+
| 1 | 7 | 1 | 8 |
+-----+-----+-----+-----+
| 2 | 5 | 2 | 3 |
+-----+-----+-----+-----+
| 3 | 4 | NULL| NULL|
+-----+-----+-----+-----+
|
|
- Right outer-join:
A ⟖ B
- The result of the
right outer join (or simply
right join)
A ⟖ B
always
contains all records
of the "right" table (B),
even if the
join-condition
does not find any matching record
in the "left" table (A).
- A matched tuple from B
is processed "normally"
(discuss in the join operation)
- An unmatched tuple from B
is combined with a row of NULL value
(so that the resulting tuple have the
same columns as an
matched tuple)
- Example:
A: B:
X Y U V
+-----+-----+ +-----+-----+
| 1 | 7 | | 1 | 8 |
+-----+-----+ +-----+-----+
| 2 | 5 | | 2 | 3 |
+-----+-----+ +-----+-----+
| 3 | 4 | | 4 | 9 |
+-----+-----+ +-----+-----+
A ⟖A.X=B.U B:
X Y U V
+-----+-----+-----+-----+
| 1 | 7 | 1 | 8 |
+-----+-----+-----+-----+
| 2 | 5 | 2 | 3 |
+-----+-----+-----+-----+
| NULL| NULL| 4 | 9 |
+-----+-----+-----+-----+
|
|
- Full outer-join:
A ⟗ B
- The result of the
full outer join (or simply
outer join)
A ⟗ B
always
contains all records
of both tables,
even if the
join-condition
does not find any matching record...
- A matched tuple
is processed "normally"
(discuss in the join operation)
- An unmatched tuple in
either table
is combined with a row of NULL value
(so that the resulting tuple have the
same columns as an
matched tuple)
- Example:
A: B:
X Y U V
+-----+-----+ +-----+-----+
| 1 | 7 | | 1 | 8 |
+-----+-----+ +-----+-----+
| 2 | 5 | | 2 | 3 |
+-----+-----+ +-----+-----+
| 3 | 4 | | 4 | 9 |
+-----+-----+ +-----+-----+
A ⟗A.X=B.U B:
X Y U V
+-----+-----+-----+-----+
| 1 | 7 | 1 | 8 |
+-----+-----+-----+-----+
| 2 | 5 | 2 | 3 |
+-----+-----+-----+-----+
| 3 | 4 | NULL| NULL|
+-----+-----+-----+-----+
| NULL| NULL| 4 | 9 |
+-----+-----+-----+-----+
|
|
|