Joins

Last updated: January 18, 2021

The purpose of SQL joins is to combine the contents of two (or more) tables into a single results table.

There are a number of different types of joins. Each type lets you combine tables in a different way. This page will go through the most common in detail.

You can try out these queries yourself using the code below on SQL Fiddle.

I made a video version of this webpage (embedded below). Scroll down past the video for the text version.


If you’re looking for a quick reference on joins, this visualizer may be helpful.


Table of Contents



The PDF of the slides in the video is here, or you can view them on Speaker Deck.


Setup

We will use two tables for this example:

friends table:

friend_id friend_name
1 John
2 Sarah
3 Rachel
4 Sam

pets table:

pet_id owner_id pet_type pet_name
1 1 goldfish Fishy
2 1 goldfish Nemo
3 1 dog Fido
4 2 cat Samwise
5 2 bird Feathers
6 3 chinchilla Fuzzy
7 NULL iguana Scales

Here’s the SQL to get this set up:

CREATE TABLE friends
(
  friend_id INT,
  friend_name VARCHAR(100)
);

CREATE TABLE pets
(
 pet_id INT,
 owner_id INT,
 pet_type VARCHAR(100),
 pet_name VARCHAR(100)
);

INSERT INTO friends values(1, 'John');
INSERT INTO friends values(2, 'Sarah');
INSERT INTO friends values(3, 'Rachel');
INSERT INTO friends values(4, 'Sam');


INSERT INTO pets values(1, 1,    'goldfish',   'Fishy'    );
INSERT INTO pets values(2, 1,    'goldfish',   'Nemo'     );
INSERT INTO pets values(3, 1,    'dog',        'Fido'     );
INSERT INTO pets values(4, 2,    'cat',        'Kitty'    );
INSERT INTO pets values(5, 2,    'bird',       'Feathers' );
INSERT INTO pets values(6, 3,    'chinchilla', 'Fuzzy'    );
INSERT INTO pets values(7, NULL, 'iguana',     'Scales'   );

Inner join (and implicit join)

SELECT * FROM friends
INNER JOIN pets
ON friends.friend_id = pets.owner_id;

This inner join will combine the rows from friends that match up with at least one row from pets.

“Match up” is defined by ON friends.friend_id = pets.owner_id in the query. This connects friends and pets based on friend_id (this is matched with the foreign key owner_id in the pets table).

If a friend has multiple pets, there will be multiple rows in the results—one for each of their pets.

If a friend doesn’t have any pets, they won’t be included in the results.

Here’s the output from this join query:

friend_id friend_name pet_id owner_id pet_type pet_name
1 John 1 1 goldfish Fishy
1 John 2 1 goldfish Nemo
1 John 3 1 dog Fido
2 Sarah 4 2 cat Kitty
2 Sarah 5 2 bird Feathers
3 Rachel 6 3 chinchilla Fuzzy

If you look back to the friends table and the pets table, you should be able to identify which of those tables each cell in the results comes from.

There are a few things to note about the results:

Implicit joins

Inner joins can also be done with implicit join syntax. This will give you the same results as the query above:

SELECT * FROM friends, pets
WHERE friends.friend_id = pets.owner_id;

Outer joins

There are a few types of outer joins:

Left outer join

SELECT * FROM friends
LEFT OUTER JOIN pets
ON friends.friend_id = pets.owner_id

This left outer join will give you all the rows from friends, and will try to match them with rows from pets.

If there is no match with pets, the pet_id, owner_id, pet_type, and pet_name columns in the results will be NULL.

pets with no owner will not be included (sorry, Scales the iguana).

Here’s the output from this join query:

friend_id friend_name pet_id owner_id pet_type pet_name
1 John 1 1 goldfish Fishy
1 John 2 1 goldfish Nemo
1 John 3 1 dog Fido
2 Sarah 4 2 cat Kitty
2 Sarah 5 2 bird Feathers
3 Rachel 6 3 chinchilla Fuzzy
4 Sam (null) (null) (null) (null)

Things to note about the results:

Right outer join

SELECT * FROM friends
RIGHT OUTER JOIN pets
ON friends.friend_id = pets.owner_id

This query will give you almost the same results as the left outer join. The only difference is that pets with no owners are in the results, and friends with no pets are left out.

Here are the results:

friend_id friend_name pet_id owner_id pet_type pet_name
1 John 1 1 goldfish Fishy
1 John 2 1 goldfish Nemo
1 John 3 1 dog Fido
2 Sarah 4 2 cat Kitty
2 Sarah 5 2 bird Feathers
3 Rachel 6 3 chinchilla Fuzzy
(null) (null) 7 (null) iguana Scales

See how Scales the iguana is in the results table, but Sam is left out?

Full outer join

These don’t exist in MySQL but do in PostgreSQL and other databases.

If your database does support full outer joins, the syntax should look like this:

SELECT * FROM friends
FULL OUTER JOIN pets
ON friends.friend_id = pets.owner_id;

You can emulate a full outer join in MySQL like this:

SELECT * FROM friends
LEFT JOIN pets ON friends.friend_id = pets.owner_id
UNION
SELECT * FROM friends
RIGHT JOIN pets ON friends.friend_id = pets.owner_id;

Here’s what the results table looks like:

friend_id friend_name pet_id owner_id pet_type pet_name
1 John 1 1 goldfish Fishy
1 John 2 1 goldfish Nemo
1 John 3 1 dog Fido
2 Sarah 4 2 cat Kitty
2 Sarah 5 2 bird Feathers
3 Rachel 6 3 chinchilla Fuzzy
4 Sam (null) (null) (null) (null)
(null) (null) 7 (null) iguana Scales

You can see that this is like a left or right outer join, but with no rows left out. Both Sam and Scales the iguana are in the results.

Cross join

A cross join is different from the other types of joins described above in that there’s no matching between tables.

Instead, the cross join below will take every row from friends and mash it together with every row from pets. See what the results table looks like here.

SELECT * FROM friends
CROSS JOIN pets;

Implicit syntax:

SELECT * FROM friends, pets;

Other resources