Database Joins

1. Inner (Equi)
2. Outer
   1. Left Outer
   2. Right Outer
   3. Full Outer
3. Cross
4. Self
5. Natural
6. non-equi-join

LEFT JOIN = (cross join w/where) UNION (non-matching || nulls from LEFT)
RIGHT JOIN = (cross join w/where) UNION (non-matching || nulls from RIGHT)
FULL JOIN = (LEFT JOIN) UNION ALL (RIGHT JOIN)

Join Type	ON?	WHERE?
CROSS JOIN	Must Not Have	Optional
INNER JOIN	Required	Optional
LEFT JOIN	Required	Optional
RIGHT JOIN	Required	Optional

A1    B1
A2    B2
A3    B2E
---------------
CROSS JOIN
A1    B1
A1    B2
A1    B2E

A2    B1
A2    B2
A2    B2E

A3    B1
A3    B2
A3    B2E
---------------
INNER JOIN = CROSS JOIN + condition:
                            met -> keep entire row
                            not met -> drop entire row
A1    B1

A2    B2
A2    B2E
------------------
LEFT JOIN = CROSS JOIN + condition:
                            met -> keep entire row
                            not met -> keep distinct left
A1    B1

A2    B2
A2    B2E

A3    []
-------------------