SQL Joins Visualized

A beginner friendly guide using graphics.

We can all agree Joins are some of the most inevitable statements in SQL regardless of where you find yourself in terms of processes surrounding data merging, data retrieval, data manipulation, ETL, and so on. There are two components, one is understanding the different types of joins, and the other, is distinguishing which to apply according to specific needs. I was once in those tragic shoes of numerous trials and errors before selecting the best fit (very inefficient practice).

The sole purpose of these visual images, is to thrust a long-lasting memory of the different types of joins without needing to flip through text books and articles.

Holy grail, right???

Let’s jump right into it!

FULL JOIN Also known as Full Outer Join — Returns all records when there is a match in either the left table or right table. Simply say, full join return records from both tables no matter if there are NULL values.

SELECT * FROM Table_x FULL JOIN Table_y ON Table_x.ID = Table_y.ID

Image Credit: Steve Nouri, Garrick Aden-Buie, Tyler Smith, Mara Averick Result:

INNER JOIN Inner Join return records that have matching values in both tables. Rows that do not satisfy the matching conditions are neglected. We get a cleaner combination of tables since this eliminates all NULLS.

SELECT * FROM Table_x INNER JOIN Table_y ON Table_x.ID = Table_y.ID

Image Credit: Steve Nouri, Garrick Aden-Buie, Tyler Smith, Mara Averick

Result:

LEFT JOIN Also known as Left Outer Join — Returns all records from the left table and matched records from the right. Here, all rows on the leftmost table are maintained disregarding whether they match up with a unique value on the right table. I personally call this “Left Priviledge”.

SELECT * FROM Table_x LEFT JOIN Table_y ON Table_x.ID = Table_y.ID

Image Credit: Steve Nouri, Garrick Aden-Buie, Tyler Smith, Mara Averick

Result:

ANOTHER EXAMPLE — LEFT JOIN (Tables differ from first example, but same Query as above)

Image Credit: Steve Nouri, Garrick Aden-Buie, Tyler Smith, Mara Averick

Result:

RIGHT JOIN On the flip side, this is also known as Right Outer Join — Returns all records from the right table and matched records from the left. Here, all rows on the rightmost table are maintained disregarding whether they match up with a unique value on the left table. I personally call this “Right Priviledge”.

SELECT * FROM Table_x RIGHT JOIN Table_y ON Table_x.ID = Table_y.ID

Image Credit: Steve Nouri, Garrick Aden-Buie, Tyler Smith, Mara Averick

Result:

What next?

Expand your horizons by learning how to join three or more tables, and how to apply these different join types in a single query. Stay tuned for more data operations tips! Request topic(s) by leaving a comment!! Good luck!!!