### Outer join operations

• Outer-join

• Outer-joins:

• An outer join is used to preserve the tuples in one of both relations

• The result of an outer join will contain all tuples from:

 The left relation operand       or       The right relation operand         or Both relations

• The 3 flavors of outer-joins:

• 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 | +-----+-----+-----+-----+ ```

• Comment:

• I personally dislike NULL values....

 Because NULL values can make query formulation difficult.

• Therefore, I will avoid using outer joins in my solutions....