A Visual Guide to SQL Joins

When I was a budding programmer and was requested to query data that involved more than one table, my stomach would drop and I would think to myself, “Well it was nice being a software developer while it lasted.”

This is unnecessarily dramatic. But let’s be honest, knowing how to use and distinguish joins in SQL can be a little confusing. However, with a bit of practice and some basic knowledge about how they work, they really aren’t bad at all!

So let’s jump into the four most common SQL joins.

Let’s say we have a group of cats and pet owners.

We choose to represent a few attributes for these groups in two SQL tables: cats and pet_owners. A pet owner can have many pets, therefore we put the foreign key (FK) in the cats table as pet_owner_id. This FK refers to the id, or primary key (PK), of pet_owners.

Now that we have some data set up, let’s take a look at the four most common types of joins.

Inner join

An inner join will return rows where the PK and FK in both tables are present. Below is an example query where all fields on pet_owners are selected for those that have a cat.

SELECT pet_owners.*
FROM pet_owners
INNER JOIN cats
ON pet_owners.id = cats.pet_owner_id

Below are two representations of what would be returned from running the query above (I’m displaying a “colorful” table beside a more traditional table to make it easier to map the values to the pictures above). Since there were no cats with a pet_owner_id of 4, Hana is not returned as a row in the results.

So what would happen if we flipped the tables in the query?

SELECT pet_owners.*
FROM cats
INNER JOIN pet_owners
ON pet_owners.id = cats.pet_owner_id

The results would be the exact same as before since an inner join grabs data at the intersection of both tables.

What if we selected all rows from the cats table instead?

SELECT cats.*
FROM cats
INNER JOIN pet_owners
ON pet_owners.id = cats.pet_owner_id

Now we would see that Bo is missing from the output since he does not have a pet owner.

Left outer join

Left outer joins are used when you want to grab data across two tables and include all rows from the first table (i.e. “leftmost” table) specified in the query. Here’s an example:

SELECT pet_owners.id, cats.name, cats.fur_color
FROM pet_owners
LEFT OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id

In the output, all pet owner fields are present. But if the pet owner did not have a cat (like Hana with id of 4), then NULL is returned for the corresponding cat related cells.

Right outer join

Right outer join operates the same as left outer join except all rows in the second table specified (i.e. “rightmost” table) are included in the query output. Let’s look at the exact same example from before but use a right outer join instead of a left:

SELECT pet_owners.id, cats.name, cats.fur_color
FROM pet_owners
RIGHT OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id

Now we can see data for all cats, but we see NULL in pet owner cells for cats that are not associated with an owner.

Full outer join

Full outer joins include the selection of fields from all rows in both tables. Just like inner join, the order of the tables in the query doesn’t matter since it represents the union of the entities. In the example below, we are grabbing the ids and names of pet owners and cats and specifying the column headers to make them more distinguishable.

SELECT
  pet_owners.id AS pet_owner_id,
  pet_owners.name AS pet_owner_name,
  cats.id AS cat_id,
  cats.name AS cat_name
FROM pet_owners
FULL OUTER JOIN cats
ON pet_owners.id = cats.pet_owner_id

From this query, a table with an output of 5 rows is returned since all entries in cats and pet_owners are represented.

Clarifications

What about “left join”?

In some SQL versions, you can omit the “outer” part of the syntax and perform the exact same query. In other words, “left join” is semantically equivalent to “left outer join” (same would apply to “right” and “full”).

Not all SQL databases support every type of join

For example, MySQL does not support “full outer join” while PostgreSQL does. Check specific database API documentation to see what commands are available.

What other joins exist?

In some SQL APIs, cross joins are supported which allows you to create a combination of every row in each table. These are not super common and something I have never personally had to deal with.

If you would like to experiment and deepen your comfortability with these queries, feel free to use this DB Fiddle which has the schema and entries pre-defined.