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

• Video/slides
• Setup
• Inner join (and implicit join)
  • Implicit joins
• Outer joins
  • Left outer join
  • Right outer join
  • Full outer join
• Cross join
• Other resources

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:

pets table:

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:

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:

• The first two columns (friend_id and friend_name) are from friends. The rest of the columns are from pets.
• Scales the iguana is not included in the results. He does not have an owner (owner_id is NULL in the pets table), so he was never matched with a row in friends.
• Sam (friend_id=4) is not included in the results because he does not have any matches in the pets table.

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: all rows from friends, all matching rows from pets
• Right outer join: all rows from pets, all matching rows from friends
• Full outer join: combines all rows from friends and pets, regardless of whether they match

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:

Things to note about the results:

• For friends with multiple pets, there are again multiple rows, so all their pets are listed in the results.
• Sam has NULL for his pet_id, owner_id, pet_type, and pet_name columns because he does not have a pet.
• Scales the iguana is not included in the table because he has a NULL owner.

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:

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:

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

• SQL joins article on Wikipedia
• A Visual Explanation of SQL Joins from Jeff Atwood’s excellent Coding Horror blog
• SQL Fiddle – a great way to try this out for yourself
• Markdown Tables Generator – you can copy from SQL Fiddle results into this and get nice Markdown-syntax tables
• Deckset is a great way to make slides from Markdown. It’s how I made the slides for the video.