inner join and outer join
In this blog, we use two tables: TableA and TableB
inner join
Inner join produces only the set of records that match in both Table A and Table B.
The inner keyword can be left out from SQL statements.
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
3 Ninja 4 Ninja
outer join
There are 3 kinds of outer join.
The outer keyword can be left out from SQL statements.
left outer join
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
right outer join
Right outer join produces a complete set of records from Table B, with the matching records (where available) in Table A. If there is no match, the left side will contain null.
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
id name id name
-- ---- -- ----
null null 1 Rutabaga
1 Pirate 2 Pirate
null null 3 Darth Vader
3 Ninja 4 Ninja
full outer join
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader
cross join (cartesian product)
SELECT * FROM TableA
CROSS JOIN TableB
This joins “everything to everything”, resulting in 4 x 4 = 16 rows, far more than we had in the original sets.