When it comes to joins, most of us mainly work with the limit of two tables and such. However, it is common to need to join three tables in order to gain a more meaningful data layout and insight. Take for example where you want to retrieve a list of movies, their corresponding rental inventory, and the actual rental details. Each of these units such as movies, inventory, and rental details are in individual tables.
In this tutorial, we will walk you through the various joins and techniques that you can use to join three tables in SQL.
Requirements:
For demonstration purposes, we will use MySQL version 80 and the Sakila sample database. To follow along, you can download and set up the Sakila database in your MySQL server. Feel free to use any other dataset that you deem applicable.
Types of Joins in SQL
Before we get to the application of the joins, let us start by discussing the various types of joins that are available in SQL databases.
INNER JOIN
The first type of join is an INNER JOIN. This type of join returns only the rows that contains a matching value in both tables. It is a very common type of join and is the most simplistic when joining two tables.
The syntax is as follows:
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
LEFT JOIN
In the case of a LEFT JOIN, it returns all the rows from the left table and the matched rows from the right table. If there are no matching values from the right table, the join adds the NULL values in their place.
The syntax is as follows:
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
RIGHT JOIN
As you can guess, the RIGHT JOIN is the opposite of the LEFT JOIN. This type of joins returns all the rows from the right table and only the matching rows from the left table. If there are no matching rows on the left table, the join adds the NULL values to it.
The following is the syntax of a RIGHT JOIN:
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
FULL OUTER JOIN
The next type of join that you will encounter in SQL databases is a FULL OUTER JOIN. This type of join returns all rows when there is a match in either the right or left tables. If there is no matching value in either of the two, it returns NULL for the columns from the table without the match.
The following demonstrates the syntax of a FULL OUTER JOIN:
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
It is good to keep in mind that not all database engines support the FULL OUTER JOINS. To achieve such, you may need to work with other types of JOINS or subqueries.
Examples:
Let us explore some examples on how we can use these types of joins to join three tables in SQL.
Example 1: Using the INNER JOIN
We start with an INNER JOIN. Suppose we want to retrieve a list of movies, the rental inventory, and the corresponding rental details.
We can use multiple INNER JOINS on the associated tables as shown in the following example:
film.title,
inventory.inventory_id,
rental.rental_date
FROM
film
INNER JOIN inventory ON
film.film_id = inventory.film_id
INNER JOIN rental ON
inventory.inventory_id = rental.inventory_id;
In the given example query, we start by joining the film and inventory tables based on the “film_id” column. We then take the resulting set and join it with the rental table based on the “inventory_id” column.
This ensures that we join three tables with a basic INNER JOIN. The resulting set is as follows:
Example 2: Using the INNER JOIN and LEFT JOIN
Let us say that we now want the list of movies, the list of rental inventories (if there is any), and the associated rental details.
We also want to make sure that even if a movie does not have a rental inventory, we still include it in the result. This is where the INNER JOIN and LEFT JOIN come into play.
Consider the following example:
film.title,
inventory.inventory_id,
rental.rental_date
FROM
film
INNER JOIN inventory ON
film.film_id = inventory.film_id
LEFT JOIN rental ON
inventory.inventory_id = rental.inventory_id;
In this example, we use an INNER JOIN to join the film and inventory table to ensure that we get the titles with the available inventory. We then use the LEFT JOIN to join the rental table to fetch the rental details if available and NULL for any title that does not have a rental history.
The resulting set is as follows:
Conclusion
In this tutorial, we learned about the various types of JOINS in SQL, how they work, and how we can use them to combine three tables.
