How can an Inner Join be done with 3 Tables in MySQL?

---

You can use the following syntax in MySQL to perform an inner join with 3 tables:

SELECT *
  FROM athletes1
  INNER JOIN athletes2
  ON athletes1.id = athletes2.id
  INNER JOIN athletes3
  ON athletes2.team_id = athletes3.team_id;

This particular example performs an inner join based on matching values in the following columns:

• id column of athletes1 and id column of athletes2
• team_id column of athletes2 and team_id column of athletes3

The following example shows how to use this syntax in practice.

Example: How to Do an Inner Join with 3 Tables in MySQL

Suppose we create the following table named athletes1 that contains information about various basketball players:

-- create table
CREATE TABLE athletes1 (
  id INT NOT NULL,
  position TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
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);

-- view all rows in table
SELECT * FROM athletes1;

Output:

+----+----------+--------+
| id | position | points |
+----+----------+--------+
|  1 | Guard    |     13 |
|  2 | Forward  |     25 |
|  3 | Center   |     10 |
|  4 | Guard    |     28 |
|  5 | Forward  |     16 |
|  6 | Center   |     20 |
+----+----------+--------+

Then suppose we create another table named athletes2 that contains more information about various basketball players:

-- create table 
CREATE TABLE athletes2 (
  id INT NOT NULL,
  team_id INT NOT NULL,
  assists INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes2 VALUES (2, 011, 4);
INSERT INTO athletes2 VALUES (5, 012, 2);
INSERT INTO athletes2 VALUES (1, 013, 10);
INSERT INTO athletes2 VALUES (4, 014, 9);
INSERT INTO athletes2 VALUES (6, 015, 13);
INSERT INTO athletes2 VALUES (3, 016, 7);

-- view all rows in table
SELECT * FROM athletes2;

Output:

+----+---------+---------+
| id | team_id | assists |
+----+---------+---------+
|  2 |      11 |       4 |
|  5 |      12 |       2 |
|  1 |      13 |      10 |
|  4 |      14 |       9 |
|  6 |      15 |      13 |
|  3 |      16 |       7 |
+----+---------+---------+

Then suppose we create another table named athletes3 that contains more information about various basketball players:

-- create table 
CREATE TABLE athletes3 (
  team_id INT NOT NULL,
  conf TEXT NOT NULL
);

-- insert rows into table
INSERT INTO athletes3 VALUES (011, 'West');
INSERT INTO athletes3 VALUES (012, 'East');
INSERT INTO athletes3 VALUES (013, 'East');
INSERT INTO athletes3 VALUES (014, 'West');
INSERT INTO athletes3 VALUES (015, 'West');
INSERT INTO athletes3 VALUES (016, 'East');

-- view all rows in table
SELECT * FROM athletes3;

Output:

+---------+------+
| team_id | conf |
+---------+------+
|      11 | West |
|      12 | East |
|      13 | East |
|      14 | West |
|      15 | West |
|      16 | East |
+---------+------+

Suppose that we would like to perform an INNER JOIN between these three tables and return specific fields from each table.

We can use the following syntax to do so:

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;

Output:

+----+--------+---------+------+
| 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 |
+----+--------+---------+------+

Notice that we’re able to successfully perform an inner join using all three tables.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL: