How to Use Multiple Inner Joins in SQL

In this guide, you’ll learn how to use multiple inner joins in SQL, why you may want to do this, and see some examples.

Can You Use Multiple Inner Joins in SQL?

Yes, you can. Using an INNER JOIN with two, three, four, or many more tables is possible. You simply add the INNER JOIN keyword to the end of the join criteria for the previous join.

The syntax looks like this:

SELECT your_columns
FROM table1
INNER JOIN table2 ON table1.col1 = table2.col1
INNER JOIN table3 ON table2.col2 = table3.col2;

Learn how to do it in this guide and see some examples.

 

How to Join Two Tables in SQL

To join two tables in SQL, you add the first table to the FROM clause, then add a join keyword (such as Inner Join), and then the second table. You then specify the ON keyword, and then the criteria that the two tables should be joined on.

Let’s see an example.

If you want to learn more about joins in SQL, check out my Ultimate Guide to SQL Joins.

 

Sample Data

We’ll use these two tables: a product_item table and a product_category table. You can find the SQL to create these tabes in my GitHub repository here.

product_category

id	category_name
1	Furniture
2	Electronics

product_item

id	item_name	category_id
1	MacBook	2
2	Desk Chair	1
3	Couch	1
4	Speakers	2
5	Queen Bed	1

 

Example

Let’s say we want to see a list of all product names and the names of their categories.

To do this, we will need to join these two tables.

Our query would look like this.

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id;

We start by selecting some columns from our tables.

We then have the FROM keyword, and then one of our tables. For this query it doesn’t matter which one we start with: both will produce the same results.

Next, we add the INNER JOIN keyword. After that keyword, we add our second table, which is product_item.

We then need to specify how the data in the two tables are related. We do this using the ON keyword.

After the ON keyword, we specify which columns have matching values in each table. We have said the id column in the product_category table is the same as the category_id in the product_item table.

We can run this query, and see these results:

item_name	category_name
MacBook	Electronics
Desk Chair	Furniture
Couch	Furniture
Speakers	Electronics
Queen Bed	Furniture

We can see that our results contain data from two tables.

So that’s how you can join two tables in SQL.

What about joining three tables?

 

How to Join Three Tables in SQL

To join three (or more) tables in SQL, you can add another join to the end of the criteria of the previous join.

 

Sample Data

Let’s expand our example. We have our product_category and product_item tables. We can add a third table called supplier which contains information about the supplier of each product.

Here’s our table:

supplier

id	supplier_name
1	Ikea
2	Fantastic Furniture
3	Apple
4	Best Buy

We also need to update our product_item table to use a supplier_id.

product_item

id	item_name	category_id	supplier_Id
1	MacBook	2	3
2	Desk Chair	1	2
3	Couch	1	1
4	Speakers	2	4
5	Queen Bed	1	1

Our product_category table is unchanged.

product_category

id	category_name
1	Furniture
2	Electronics

 

Example

Let’s say we want to see all product names, their category names, and their supplier names.

We can start with our earlier query that joins two tables:

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id;

Here’s how to join to a third table.

Step 1: Remove the semicolon and add another INNER JOIN to the end of the query.

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN

Step 2: Add the third table you want to join to.

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier

Step 3: Add the ON keyword

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON

Step 4: Specify which column from any of the tables already in your query that you want to join to the suppliers table.

In our query so far, we have:

• product_category
• product_item

How are they related to supplier?

There’s a supplier_id column in the product_item table.

So, let’s add that in:

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON product_item.supplier_id = 

Step 5: Add the column in the new table that is related to the column you just specified.

In this example, it’s the supplier.id column.

SELECT
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON product_item.supplier_id = supplier.id;

Step 6: Add any columns from the third table to the SELECT clause if you want to see them in the results.

Let’s add in the supplier_name.

SELECT
product_item.item_name,
product_category.category_name
supplier.supplier_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON product_item.supplier_id = supplier.id;

Our query is ready! If we run it, we can see these results.

item_name	category_name	supplier_name
MacBook	Electronics	Apple
Desk Chair	Furniture	Ikea
Couch	Electronics	Ikea
Speakers	Electronics	Best Buy
Queen Bed	Furniture	Ikea

We can see all five products, their categories, and their supplier name.

 

Using Table Aliases

You can clean up your query by using table aliases. A table alias is a short name you can give to a table in your query. This makes it easier to write queries and read queries.

Here’s the same query using table aliases.

SELECT
pi.item_name,
pc.category_name
s.supplier_name
FROM product_category pc
INNER JOIN product_item pi ON pc.id = pi.category_id
INNER JOIN supplier ON pi.supplier_id = s.id;

The table aliases are pc, pi, and s. They are used in the JOIN clause and the SELECT clause in this query.

 

How to Join Three Tables with Different Join Types

You don’t have to use the same join type, such as INNER JOIN, for all of your joins. You can add in whatever join type you want.

Let’s say you want to see all of the suppliers, all of their products, and their categories.

Your query may look like this:

SELECT
product_category.category_name,
supplier.supplier_name,
product_item.item_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON product_item.supplier_id = supplier.id;

The results are:

supplier_name	item_name	category_name
Ikea	Queen Bed	Furniture
Ikea	Desk Chair	Furniture
Ikea	Couch	Electronics
Apple	MacBook	Electronics
Best Buy	Speakers	Electronics

We can see three suppliers in this list.

But we’re missing a supplier: Fantastic Furniture.

Why is this missing?

It’s because there are no product item records for that supplier.

We still want to see that supplier in our list. We can solve this by using an outer join in our query.

We can change the join between the supplier and product_item to an outer join.

Here’s our updated query.

SELECT
supplier.supplier_name,
product_item.item_name,
product_category.category_name
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
RIGHT OUTER JOIN supplier ON product_item.supplier_id = supplier.id;

We use RIGHT OUTER JOIN instead of an INNER JOIN. This means we will see all records for the supplier table (the table on the right side of the JOIN keyword), and if there are no matches on the left side, there will be a NULL value.

Here are our results.

supplier_name	item_name	category_name
Ikea	Queen Bed	Furniture
Ikea	Desk Chair	Furniture
Ikea	Couch	Electronics
Fantastic Furniture	(null)	(null)
Apple	MacBook	Electronics
Best Buy	Speakers	Electronics

We can see the Fantastic Furniture record in the results, and it has a null value for the item_name and category_name columns.

We could also write this query using a LEFT JOIN, if we rearranged the tables in the FROM clause to make the supplier table come before the product_item table:

SELECT
supplier.supplier_name,
product_item.item_name,
product_category.category_name
FROM supplier
LEFT OUTER JOIN product_item ON supplier.id = product_item.supplier_id
INNER JOIN product_category ON product_item.category_id = product_category.id;

If we run this query, we’ll get these results:

supplier_name	item_name	category_name
Ikea	Queen Bed	Furniture
Ikea	Desk Chair	Furniture
Ikea	Couch	Electronics
Apple	MacBook	Electronics
Best Buy	Speakers	Electronics

However, the Fantastic Furniture row is missing. This is because an INNER JOIN is included later in the query, which will only match on rows between the tables.

So, if you want the same results, you could either:

• change both joins to a LEFT OUTER JOIN; or
• keep the RIGHT OUTER JOIN

 

How to Join Four Tables in SQL

To join four tables in SQL, you can add another table to the end of the JOIN clause after your previous join criteria. You can join a fourth table to any of the tables already in the query.

Here’s an example. Let’s say we had a status_lookup table that stored whether a product was active or inactive or something else.

Our query would look like this:

SELECT
product_item.item_name,
product_category.category_name
supplier.supplier_name,
status_lookup.status_value
FROM product_category
INNER JOIN product_item ON product_category.id = product_item.category_id
INNER JOIN supplier ON product_item.supplier_id = supplier.id
INNER JOIN status_lookup ON product_item.status_id = status_lookup.id;

We simply add in the last INNER JOIN keyword, and specify the new table and columns we want to join on.

We can then add any columns from this table into our SELECT clause.

You can use the same techniques in this guide to join to as many tables as you need.

 

Conclusion

Multiple inner joins in SQL are a common requirement for queries and are possible in SQL. You can add a join keyword to your query, specify the table and then the columns to join on.

You can join many tables into a single query to get all of the data you need.