Different SQL Joins Explained

In a previous blog post, I covered the basics of an SQL JOIN statement. I'd like to further elaborate on the 4 different types of Joins that exist.

The Venn diagrams below give a great overview of how they work, which I recommend you use as a reference to my explanations below.

I will also reuse from my previous post the -

Pets and

Owners tables to illustrate each JOIN statement.

INNER JOIN

Executing a JOIN statement without specifying the type will always default to an INNER JOIN. This type will only return rows that have matching values (i.e. pet IDs) in both tables.

SELECT * FROM Pets JOIN Owners ON Pets.Id=Owners.Pet_Id;

Query output:

LEFT JOIN

In a LEFT JOIN, the entire left-hand table is included even if a row doesn't have a match. Consequently, NULL values are used on the right-hand side to represent this missing match.

SELECT * FROM Pets LEFT JOIN Owners ON Pets.Id=Owners.Pet_Id;

Query output:

RIGHT JOIN

A RIGHT JOIN is identical to a LEFT JOIN, but only reversed. That means the entire right-hand table is included along with matching (empty) rows on the left-hand side.

SELECT * FROM Pets RIGHT JOIN Owners ON Pets.Id=Owners.Pet_Id;

Query output:

FULL OUTER JOIN

A FULL OUTER JOIN, or simply OUTER JOIN, is basically a combination of the previous two. Here, all the records on both tables are included along with empty rows for any missing matches.

SELECT * FROM Pets OUTER JOIN Owners ON Pets.Id=Owners.Pet_Id;

Query output: