Join Three Tables in SQL

One of the major features of relational databases is the ability to separate the related data into various components called “tables”. Unlike other databases, relational databases require gathering of data from various tables and assembling the results into meaningful data.

This is where the functionality of joins comes into play. In relational databases, joins is one of the most common operations that allows you to combine the data from two or more tables into a single result set.

However, you may find that most joins only cover two tables. However, joins can quickly extend to three or more tables. Although it may be more efficient to look into CTEs when working with more than three tables, joins can be very efficient and fast when it comes to three tables.

In this tutorial, we will go over the steps that you need to perform a join involving three tables in SQL.

Getting Started

Before we begin, we assume that you have three simple tables with the columns as shown in the following:

 
This layout allows us to quickly understand how joining three tables work and how we can configure them using an SQL query.

Join Three Tables in SQL

When performing any join in SQL, the main component starts by identifying a common column or a set of columns in each of the tables that you need to join. You can then use these shared columns to link the table together, allowing SQL to interpret and merge the data sensibly and efficiently.

Although there are various types of joins in SQL, the main component is the similarities in the tables.

For this tutorial, we will focus on an inner join that returns only the rows that match the values in the three tables.

In the case of the previous tables, we can perform an inner join on the three tables as demonstrated in the following query:

 
Let us explore the previous query step by step:

1. The first is the SELECT statement which allows us to select the columns that we wish to include in the result set. In this case, we are interested in the name column from Table 1, the city column from Table 2, and the salary column from Table 3.
2. Using the FROM statement, we specify the target tables from which we wish to fetch the set columns. In this case, we are fetching from Table 1.
3. Next comes the INNER JOIN clause. We use this clause to join Table 1 with Table 2. We also use the ON clause to specify the condition that links the two tables which, in this case, is the ID column.
4. The second INNER JOIN allows us to join the result set from the first join with Table 3. Using the ON clause, we tell SQL that we wish to join the result set and Table 3 based on the ID column.
5. The result set from all three tables is included in the selected columns.

Practical Example:

Although the previous example demonstrates how to join the three tables, let us take a more practical example and use the Sakila database.

We join the film, rental, and inventory tables in this case. The query is as follows:

 
Explanation:

1. 1. In this case, we use the SELECT statement to select the rental_id, rental_date and inventory_id columns from the rental table.
   2. The first JOIN clause links the rental table to the inventory table using the inventory_id column.
   3. The second JOIN clause joins the result set from the first join to the film table using the film_id column.
   4. Finally, we use the WHERE clause to filter the result set only to include the target title.

The resulting table is as follows:

Conclusion

Joining three tables in SQL requires identifying a shared column or set of columns in each table and using the appropriate type of join (inner, left, right, or full outer) to link the tables together. As shown in this tutorial, you should understand how to join three tables in SQL.