How to INNER JOIN 3 Tables in MySQL
mysql inner join 3 tables, Real-world databases rarely keep everything in one table.
A customer might be stored in one table, their orders in another, and payment information in a third. In a sports database, player details, team assignments, and conference information might all be stored separately.
So what happens when you need information from three different tables at once?
That’s where a MySQL INNER JOIN with 3 tables becomes useful.
Instead of trying to squeeze everything into one table, you can connect related tables using multiple INNER JOIN statements and retrieve the exact information you need.
MySQL INNER JOIN with 3 Tables Syntax
The basic syntax looks like this:
SELECT *
FROM athletes1
INNER JOIN athletes2
ON athletes1.id = athletes2.id
INNER JOIN athletes3
ON athletes2.team_id = athletes3.team_id;
This query connects three tables in two steps:
athletes1is joined toathletes2using theidcolumn.- The resulting data is then joined to
athletes3using theteam_idcolumn.
The important idea is that you don’t need a special JOIN command for three tables.
You simply add another INNER JOIN with its own ON condition.
A Basketball Example
Let’s make this easier to understand with a small basketball database.
Imagine that we want to store player information, player statistics, and conference information separately.
Our first table, athletes1, will contain basic player information.
CREATE TABLE athletes1 (
id INT NOT NULL,
position TEXT NOT NULL,
points INT NOT NULL
);
Now insert some sample players:
INSERT INTO athletes1 VALUES (1, 'Guard', 13);
INSERT INTO athletes1 VALUES (2, 'Forward', 25);
INSERT INTO athletes1 VALUES (3, 'Center', 10);
INSERT INTO athletes1 VALUES (4, 'Guard', 28);
INSERT INTO athletes1 VALUES (5, 'Forward', 16);
INSERT INTO athletes1 VALUES (6, 'Center', 20);
The table looks like this:
+----+----------+--------+
| id | position | points |
+----+----------+--------+
| 1 | Guard | 13 |
| 2 | Forward | 25 |
| 3 | Center | 10 |
| 4 | Guard | 28 |
| 5 | Forward | 16 |
| 6 | Center | 20 |
+----+----------+--------+
The first table tells us who the players are and how many points they scored.
But it doesn’t tell us which team they belong to.
That’s where the second table comes in.
Creating the Second Table
Let’s create athletes2:
CREATE TABLE athletes2 (
id INT NOT NULL,
team_id INT NOT NULL,
assists INT NOT NULL
);
Insert the player statistics:
INSERT INTO athletes2 VALUES (2, 11, 4);
INSERT INTO athletes2 VALUES (5, 12, 2);
INSERT INTO athletes2 VALUES (1, 13, 10);
INSERT INTO athletes2 VALUES (4, 14, 9);
INSERT INTO athletes2 VALUES (6, 15, 13);
INSERT INTO athletes2 VALUES (3, 16, 7);
The resulting table is:
+----+---------+---------+
| id | team_id | assists |
+----+---------+---------+
| 2 | 11 | 4 |
| 5 | 12 | 2 |
| 1 | 13 | 10 |
| 4 | 14 | 9 |
| 6 | 15 | 13 |
| 3 | 16 | 7 |
+----+---------+---------+
Now we have a connection between the first and second tables.
The id column appears in both tables:
athletes1.id = athletes2.id
This allows us to match each player with their team and assist information.
But we’re still missing one piece.
What conference does each team belong to?
Creating the Third Table
Let’s create athletes3:
CREATE TABLE athletes3 (
team_id INT NOT NULL,
conf TEXT NOT NULL
);
Insert the conference information:
INSERT INTO athletes3 VALUES (11, 'West');
INSERT INTO athletes3 VALUES (12, 'East');
INSERT INTO athletes3 VALUES (13, 'East');
INSERT INTO athletes3 VALUES (14, 'West');
INSERT INTO athletes3 VALUES (15, 'West');
INSERT INTO athletes3 VALUES (16, 'East');
The table now contains:
+---------+------+
| team_id | conf |
+---------+------+
| 11 | West |
| 12 | East |
| 13 | East |
| 14 | West |
| 15 | West |
| 16 | East |
+---------+------+
Now our three tables contain different pieces of information:
athletes1→ player position and pointsathletes2→ team and assistsathletes3→ conference
The information is separated, but the tables are connected through common columns.
That’s exactly what SQL JOINs are designed to handle.
Joining All 3 Tables
Now let’s bring everything together.
SELECT athletes1.id,
athletes1.points,
athletes2.team_id,
athletes3.conf
FROM athletes1
INNER JOIN athletes2
ON athletes1.id = athletes2.id
INNER JOIN athletes3
ON athletes2.team_id = athletes3.team_id;
The result is:
+----+--------+---------+------+
| id | points | team_id | conf |
+----+--------+---------+------+
| 2 | 25 | 11 | West |
| 5 | 16 | 12 | East |
| 1 | 13 | 13 | East |
| 4 | 28 | 14 | West |
| 6 | 20 | 15 | West |
| 3 | 10 | 16 | East |
+----+--------+---------+------+
We’ve successfully combined information from three separate tables into one result.
How Does the 3-Table JOIN Actually Work?
This is easier to understand if we break the query into stages.
First, MySQL joins athletes1 and athletes2:
FROM athletes1
INNER JOIN athletes2
ON athletes1.id = athletes2.id
For example:
athletes1.id = 1
athletes2.id = 1
These rows match.
The player with ID 1 therefore gets the corresponding team_id and assists information.
Next, MySQL connects that result to athletes3:
INNER JOIN athletes3
ON athletes2.team_id = athletes3.team_id
If the player’s team_id is 13, MySQL looks for team_id = 13 in athletes3.
It finds:
team_id = 13
conf = East
The final result can therefore contain:
Player → Team → Conference
This is the fundamental idea behind joining multiple tables.
Why Is It Called an INNER JOIN?
An INNER JOIN only returns rows where a matching record exists in both tables being joined.
This matters when your tables contain incomplete information.
For example, suppose athletes3 didn’t contain team_id = 16.
The player with ID 3 could still exist in athletes1 and athletes2, but the three-table INNER JOIN would not return that player because there would be no matching conference record.
In other words:
INNER JOIN keeps the connections that actually exist.
This makes it useful when you only want complete matching records.
Using Table Aliases
When joining multiple tables, table names can quickly make your query difficult to read.
Instead of writing:
athletes1.id
athletes2.team_id
athletes3.conf
we can give each table a short alias:
SELECT a1.id,
a1.points,
a2.team_id,
a3.conf
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.id = a2.id
INNER JOIN athletes3 AS a3
ON a2.team_id = a3.team_id;
This produces the same result but is much easier to read.
For larger queries, aliases are almost essential.
Selecting Columns from All Three Tables
You don’t have to use SELECT *.
In fact, selecting only the columns you need is usually better.
For example:
SELECT a1.id,
a1.position,
a1.points,
a2.assists,
a2.team_id,
a3.conf
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.id = a2.id
INNER JOIN athletes3 AS a3
ON a2.team_id = a3.team_id;
Now the result contains information from all three tables:
+----+----------+--------+---------+---------+------+
| id | position | points | assists | team_id | conf |
+----+----------+--------+---------+---------+------+
| 2 | Forward | 25 | 4 | 11 | West |
| 5 | Forward | 16 | 2 | 12 | East |
| 1 | Guard | 13 | 10 | 13 | East |
| 4 | Guard | 28 | 9 | 14 | West |
| 6 | Center | 20 | 13 | 15 | West |
| 3 | Center | 10 | 7 | 16 | East |
+----+----------+--------+---------+---------+------+
This is often more useful than returning every column from every table.
Adding a WHERE Clause
Once you know how to join three tables, you can also filter the final results.
For example, suppose we only want players from the Western Conference:
SELECT a1.id,
a1.position,
a1.points,
a2.team_id,
a3.conf
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.id = a2.id
INNER JOIN athletes3 AS a3
ON a2.team_id = a3.team_id
WHERE a3.conf = 'West';
Now only players whose teams belong to the Western Conference are returned.
You can also combine conditions:
WHERE a3.conf = 'West'
AND a1.points > 20;
This would return Western Conference players who scored more than 20 points.
The Most Important Part: The ON Conditions
When joining three tables, one of the easiest mistakes is using the wrong columns in the ON clauses.
In our example, the first relationship is:
ON a1.id = a2.id
The second relationship is:
ON a2.team_id = a3.team_id
These relationships create a chain:
athletes1
|
| id
↓
athletes2
|
| team_id
↓
athletes3
Before writing a multi-table JOIN, identify which column connects each table.
That simple habit can prevent many SQL errors.
A Real-World Data Analytics Example
The same technique appears far beyond sports databases.
Imagine an online store with three tables:
customers
customer_id | name
orders
order_id | customer_id | product_id
products
product_id | product_name | price
You could combine all three:
SELECT c.name,
o.order_id,
p.product_name,
p.price
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
INNER JOIN products AS p
ON o.product_id = p.product_id;
Now one query can answer a much more useful business question:
Which customer ordered which product, and how much did that product cost?
This is why multi-table JOINs are so important in data analysis. Real datasets are frequently normalized across several related tables.
Common Mistake: Joining the Wrong Relationship
Consider this incorrect pattern:
INNER JOIN athletes3
ON athletes1.id = athletes3.team_id
This doesn’t represent the relationship in our database.
athletes1.id identifies a player, while athletes3.team_id identifies a team.
Those are different concepts.
The correct relationship is:
ON athletes2.team_id = athletes3.team_id
Always ask:
“What does this column represent?”
A column name alone isn’t enough. Understanding the relationship between tables is just as important as knowing SQL syntax.
INNER JOIN with 3 Tables: The General Pattern
For most three-table INNER JOIN problems, you can start with this structure:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column
INNER JOIN table3
ON table2.another_column = table3.another_column;
Then add filtering when necessary:
WHERE condition;
And sorting when required:
ORDER BY column;
You can also add aggregation:
GROUP BY column;
So a more advanced query might look like:
SELECT a3.conf,
COUNT(*) AS players,
AVG(a1.points) AS avg_points
FROM athletes1 AS a1
INNER JOIN athletes2 AS a2
ON a1.id = a2.id
INNER JOIN athletes3 AS a3
ON a2.team_id = a3.team_id
GROUP BY a3.conf;
Now the three-table JOIN becomes the foundation for an actual analytical question: How do player statistics compare across conferences?
Final Takeaway
Joining three tables in MySQL is not fundamentally different from joining two tables.
You simply add another INNER JOIN and specify how the new table connects to the existing result.
The basic pattern is:
SELECT ...
FROM table1
INNER JOIN table2
ON table1.key = table2.key
INNER JOIN table3
ON table2.key = table3.key;
The key things to remember are:
- Use
INNER JOINto return matching records. - Use
ONto define the relationship between tables. - Add another
INNER JOINwhen you need information from another table. - Use table aliases to keep complex queries readable.
- Use
WHEREto filter the final results. - Make sure the columns used in each
ONcondition represent a genuine relationship.
Once you understand the chain Player → Team → Conference, the idea becomes much easier to apply to real-world databases such as Customer → Order → Product, Employee → Department → Location, or Student → Course → Instructor.
Mastering multi-table JOINs is one of the biggest steps toward writing SQL queries that can handle real-world data rather than just simple single-table examples.
