Phases of the SQL JOIN

Photo by maggie hung on Unsplash
  1. Outer Join
  2. Unions
  3. Self-Join

INNER JOIN

An inner join is similar to an intersection in set theory. The common values between two datasets/tables are selected to form an inner joined table.

Image by Author: Inner Join
SELECT * 
FROM table_A
INNER JOIN table_B
ON table_A.matching_column = table_B.matching_column

OUTER JOIN

There are a few different types of outer joins. Outer joins define how the values present in one of the tables being joined is managed.

Image by Author: Full Outer Join
SELECT * 
FROM table_A
FULL OUTER JOIN table_B
ON table_A.matching_column = table_B.matching_column
Image by Author: Full Outer Join with WHERE clause
SELECT * 
FROM table_A
FULL OUTER JOIN table_B
ON table_A.matching_column = table_B.matching_column
-- further filter after the join to return unique values--WHERE table_A.id IS null OR
table_B.id IS null
Image by Author: Left Outer Join
SELECT * 
FROM table_A
LEFT OUTER JOIN table_B
ON table_A.matching_column = table_B.matching_column
Image by Author: Left Outer Join with WHERE clause
SELECT * 
FROM table_A
FULL OUTER JOIN table_B
ON table_A.matching_column = table_B.matching_column
-- filter after the join to return unique values to left table--WHERE table_B.id IS null
Image by Author: Right Outer Join
SELECT * 
FROM table_B
RIGHT OUTER JOIN table_A
ON table_B.matching_column = table_A.matching_column
Image by Author: Right Outer Join with WHERE clause
SELECT * 
FROM table_B
RIGHT OUTER JOIN table_A
ON table_B.matching_column = table_A.matching_column
-- filter after the join to return unique values to right table--WHERE table_A.id IS null

UNION

The UNION operator allows us to concatenate resulting outputs from two or more SELECT statements. The outputs to be pasted together needs to make logical sense and should match up in such a way that they can be stacked on top of each other (essentially, do the column headings match in a meaningful way).

SELECT column_name(s) FROM table_A
UNION
SELECT column_name(s) FROM table_B

SELF-JOIN

A self join is a special case where the tables being joined can be seen as copies of the same table. The table is not actually copied but SQL performs the command as if it were two copies. The syntax follows similarly like a standard JOIN syntax with the same table referenced in both parts

SELECT table_copy_A.col, table_copy_B.col
FROM table AS table_copy_A
JOIN table AS table_copy_B
ON table_copy_A.some_column = table_copy_B.other_column

Data Science Student

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store