An Illustrated Guide to Multiple Join

So far, our articles in the "An Illustrated Guide" series have explained several join types: INNER JOINs, OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN, self-join and non-equi join. In this final article of the series, we show you how to create SQL queries that match data from multiple tables using one or more join types.

Join Types in SQL Queries

Before we start discussing example SQL queries that use multiple join types, let's do a short recap of the join types we've covered so far, just to be sure you understand the differences. To that end, here's a short summary in the form of a table. Take a look:

Type of JOIN	Matching records from tables	Explanation
INNER JOIN(JOIN) SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue	An INNER JOIN returns records that match in both tables.
LEFT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t LEFT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL	A LEFT JOIN returns all records from the left table, even when they do not match in the right table. Missing values become NULLs.
RIGHT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t RIGHT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue NULL NULL NULL 2 NULL	A RIGHT JOIN returns all records from the right table, even when they do not match those in the left table. Missing values become NULLs.
FULL JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t FULL JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL NULL NULL NULL 2 NULL	A FULL JOIN returns all records from both tables: left and right, even when rows do not match. Missing values become NULLs.
CROSS JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t CROSS JOIN color c ;	Result: id size color_id id color 2 M 1 1 yellow 2 M 1 2 NULL 2 M 1 3 blue 3 NULL 3 1 yellow 3 NULL 3 2 NULL 3 NULL 3 3 blue 1 S NULL 1 yellow 1 S NULL 2 NULL 1 S NULL 3 blue	A CROSS JOIN returns the Cartesian product of the records from both tables. This means that each record from the left table is joined with each record from the right table. Missing values become NULLs.

Note that the result of each join type contains data that come from tables tshirt and color. The specific join type determines the contents of the table to be returned.

What is a Multiple Join in SQL?

Each query may comprise zero, one, or more joins. A multiple join is a use of more than one join in a single query. The joins used may be all of the same type, or their types can differ. We'll begin our discussion by showing an example query that uses two joins of the same type. Take a look at the query below.

SELECT v.name, c.name,  p.lastname
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
INNER JOIN person p ON v.person_id = p.id ;

The query invokes two INNER JOINs in order to join three tables: vehicle, person and color. Only those records that have a match in each table will be returned. First, take a look at the sets of data that were joined.

There are three sets of data that correspond to three tables in the database: vehicle, color and person, represented below.

person

color

vehicle

You can see that each vehicle in the vehicle table has a color, except for motorcycle. Each vehicle has an owner assigned, except for bicycle, which has no owner. One of the colors (blue) in the color table is not assigned to any vehicle. Further, motorcycle does not have a color available in the database. On the other hand, bicycle has an assigned color, but no owner. Lastly, the person named Miller is assigned no vehicle.

In the above query we used multiple-join to retrieve only those vehicles assigned both a color and owner. The vehicle table has the color_id column which identifies the color in the color table, as well as the person_id column which identifies the person in the person table.

Query result:

It turns out that only two records match the criteria defined by the two inner joins.

The picture below presents the sequence in which records from the respective tables were joined.

Note that all JOIN operations are performed from left to right. In step one, the tables in the first JOIN are matched (tables vehicle and color). As a result, an intermediate table is created. In step two, this intermediate table (treated as the left table) is joined with another table (table person) using the second JOIN.

Remember that a single JOIN of any type produces a single intermediate table (commonly called a derived table) during a multi-join query.

Mixed Left and Right Join With Inner Join

It is also possible to combine different types of joins in a multi-join query. Let's take an example with an INNER JOIN and LEFT JOIN. Suppose we want to query our database for all people who own a colored vehicle or don’t own a vehicle at all.

Intuitively, we would start with the person table and join it with the vehicle table using a LEFT JOIN. In that case, the LEFT JOIN would match each record from the person table with a record from the vehicle table, and for any person for whom a matching record was not found, it would fill missing values with NULLs. This join will produce a list of all people in the database with any associated vehicle data, even if they do not own one. But we're interested in seeing only vehicles with colors assigned. This means we must use an INNER JOIN on tables vehicle and color. Here's a query that meets that particular requirement, but does it do the job?

SELECT v.name vehicle_name, c.name color_name,  p.lastname
FROM person p
LEFT JOIN vehicle v ON  v.person_id = p.id
INNER JOIN color c ON v.color_id = c.id ;

No! This query returns the same result we obtained from our earlier query (which only used INNER JOINs). Our list does not include those without a vehicle.

Result:

But what happened? The INNER JOIN skipped those results which did not match in both tables, i.e. in the derived table (created by joining tables person and vehicle) and the color table. How can we solve this problem?

The following query presents one of a few possible solutions. Here the derived table returns vehicles with colors only, and is then RIGHT JOINed with the person table in order to obtain all of the people.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
RIGHT JOIN person p ON v.person_id = p.id ;

Result:

Now we have a list of all the people: those with colored vehicles and those without vehicles. We started with an INNER JOIN of tables vehicle and color. Each vehicle included in the derived table must have a color assigned, which is why this join type is appropriate. Having selected the colored vehicles, we could now use a RIGHT JOIN on the derived table with the person table, which is how we obtained people who were not vehicle owners alongside those (from the derived table) who owned a colored vehicle.

Another method to solve this problem is to use a LEFT JOIN on the person table and a subquery in which we used an INNER JOIN on tables vehicle and color.

Take a look at the query below.

SELECT p.lastname, o.vehicle_name, o.color_name
FROM person p LEFT JOIN
(  SELECT v.name vehicle_name, c.name color_name, v.person_id
    FROM vehicle v
    INNER JOIN color c ON v.color_id=c.id
) o ON  o.person_id = p.id;

Mixed JOINs with Full JOIN

Yet another multi-join type uses full joins. First, let's take a look at a multiple-join with full joins only.

SELECT p.lastname, v.name, c.name
FROM vehicle v
FULL JOIN color c ON  v.color_id = c.id
FULL JOIN person p ON v.person_id = p.id ;

The query above matches the records from three tables: person, vehicle and color in such a way that even records without a match in the other two tables will appear in the result table. Empty columns will be filled with NULL values. That is why the query returns all people regardless of whether they have a vehicle, all vehicles regardless of whether they have a color assigned, and all colors regardless of whether they are assigned to any vehicle.

Result:

We used full joins to join all records, even those that do not match. Remember that full joins return all records, while inner joins return only those that match.

The picture below explains the sequence in which tables were joined.

FULL JOIN can also appear in a query with another join type, thus creating a multiple-join with mixed types. The query below makes use of a FULL JOIN with an INNER JOIN.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER  JOIN color c ON  v.color_id = c.id
FULL  JOIN person p ON v.person_id = p.id ;

This query enables us to retrieve a list of all people, whether or not they are vehicle owners, and all vehicles that have a color assigned.

Here's how the two joins work:

First, tables vehicle and color are combined using an INNER JOIN. Next the derived table is combined with the person table using a FULL JOIN. Here's the result:

Summary

A single SQL query can join two or more tables. When there are three or more tables involved, queries can use a single join type more than once, or they can use multiple join types. When using multiple join types we must carefully consider the join sequence in order to produce the desired result. The examples presented in this article clearly demonstrate how a minor change in the type of join, (or, in the case of multiple joins, the order in which they appear in the query) can completely change the query result, making or breaking the success of the query.

To which join combinations should we pay particular attention to? INNER JOINs with OUTER JOINs, and OUTER JOINs with OUTER JOINs. Each of these combinations can produce erroneous query results when used inappropriately.

Learn More About SQL

Basic knowledge of SQL joins is an absolute must, but most SQL beginners feel intimidated by JOIN statements. The truth is there's absolutely nothing to fear!

In this article, we discussed how to use multiple-joins in a single query: either LIKE or mixed JOIN types. More information about joins can be found in LearnSQL.com's SQL comprehensive material. In the courses, you will greatly increase your expertise, testing and honing your new skills through the provided interactive practice exercises. Start from SQL Basics course if you have no prior knowledge of SQL. Hit the SQL JOINs course to gain hands-on practice querying multiple tables through tons of interactive exercises on JOIN statements. Try it now for free!