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;