So far, our articles in the “An Illustrated Guide” series have explained several join types:
OUTER JOINs (
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|
SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t JOIN color c ON t.color_id = c.id ;
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 ;
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 ;
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 ;
SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t CROSS JOIN color c ;
Note that the result of each join type contains data that come from tables
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:
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:
person, represented below.
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.
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
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
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
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.
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
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 ;
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
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
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:
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.
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
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:
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:
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 Vertabelo Academy’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!