A Beginners Guide to SQL Joins

An efficient way to properly understand the basics of SQL joins is to illustrate its similarities to set theory by using Venn Diagrams.

SQL joins simply aim to connect rows from two distinct tables in the same database on a common/shared column and display/return the result as one table. There are two major kinds of SQL joins; Inner Join and Outer Join.

INNER JOIN

SELECT ---
FROM left_table
INNER JOIN right_table
ON left_table.field = right_table.field

This returns values that are common to both the left and right tables only.

OUTER JOIN

There are three kinds of outer joins, namely; Left join, Right join, Full join.

LEFT JOIN

SELECT ---
FROM left_table
LEFT JOIN right_table
ON left_table.field = right_table.field

This returns the values in the left table and the values in both the left and right tables.

RIGHT JOIN

SELECT ---
FROM right_table
RIGHT JOIN left_table
ON right_table.field = left_table.field

This is rarely used as it can be written as a left join. It returns the values in the right table and the values in both the left and right tables.

FULL JOIN

SELECT ---
FROM left_table
FULL JOIN right_table
ON left_table.field = right_table.field

A full join returns all the values in the left table and all the values in the right table.

It is important to note that position is essential in SQL joins as mixing up your left and right tables can give you very different results.

Not treated in this article is the cross join. If you are interested in an easy to understand in-depth study, check out this course from DataCamp.

https://learn.datacamp.com/courses/intermediate-sql