Different Ways of Writing Joins
LEFT OUTER JOIN(OUTER is an optional). There is no LEFT INNER JOIN.
Type 1: INNER JOIN – only where both tables match
- INNER JOIN aka JOIN
Type 2: OUTER JOINS where either one or both tables match
-
LEFT OUTER JOIN aka LEFT JOIN
-
RIGHT OUTER JOIN aka RIGHT JOIN
-
FULL OUTER JOIN aka FULL JOIN
Type 3: CROSS JOIN – Cartesian product(all possible combos of each table)
- Cross Join
left join
SELECT <select_list> FROM TableA a LEFT JOIN TableB b ON a.key = b.key;
right join
SELECT <select_list> FROM TableA a RIGHT JOIN TableB b ON a.key = b.key;
Inner join
SELECT <select_list> FROM TableA a INNER JOIN TableB b ON a.key = b.key;
Full Outer join
SELECT <select_list> FROM TableA a Full Outer JOIN TableB b ON a.key = b.key;