How to Join Multiple Tables in MySQL

Joining two tables is easy, but have you ever tried joining more than two tables? It’s a little bit tricky, right? When you try to join multiple tables, everything gets messed up, even if it looked easy. If you are a new programmer and not clear with the MySQL JOIN concepts, you will always get stuck if you try to join multiple tables.

In this tutorial, we will learn to join multiple tables and see a detailed explanation of how everything works. So, let’s get started!

Introduction and Prerequisites

You have been joining two tables for so long. But now, you might need normalization of the database in your project and that surely needs joining multiple tables. However, you didn’t come across a such situation and now you are stuck.

To perform joining multiple tables, you need to understand the SQL JOIN concepts clearly. You need to understand how different joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and FULL JOIN work.

Moreover, understanding table relationships such as one-to-one, one-to-many, and many-to-many as well as primary key and foreign key will help to understand this topic. So, make sure that you have a good understanding of these points.

Now, let’s get started with our topic. We will see examples to join three tables using INNER JOIN as well as LEFT JOIN. Based on that, you can try other joining types yourself.

Join Three Tables Using INNER JOIN

Before getting started with examples, let’s create three tables to store the employees’ information and insert data into them.

-- employees table
CREATE TABLE employees(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
salary FLOAT
);

INSERT INTO employees(name,salary)
VALUES('John Cena',4300),('Randy Orton',3000),
('Roman Reigns',5400);

-- employee deparments table
CREATE TABLE departments(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);

INSERT INTO departments(name)
VALUES('Sales'),('Development'),('Marketing');


-- registration table
CREATE TABLE registration(
empID INT NOT NULL,
deptID INT NOT NULL,
FOREIGN KEY(empID) REFERENCES employees(id),
FOREIGN KEY(deptID) REFERENCES departments(id)
);

 INSERT INTO registration(empId,deptId)
VALUES(1,1),(2,3),(3,3);Code language: SQL (Structured Query Language) (sql)

Here, the first table contains information about employees. The second table contains information about the departments. The third table contains information about which employee has joined which department.

Now let’s join these three tables using the INNER JOIN to display all information about each employees.

SELECT * FROM employees e
INNER JOIN registration r
ON e.id=r.empId
INNER JOIN departments d
ON d.id=r.deptId;Code language: SQL (Structured Query Language) (sql)

Here, we have joined the employees table with the registration table on the employee id column which will result in the employees’ names and their department IDs. Further in the query, we join the departments table to get the department name based on the department id.

Let’s run the query and see the result.

As you can see, the query shows the employee information from all tables.

Note that, the employee id must be present in the registrations table as well to get the information of all employees.

If the employee detail is only present in the employees table and not in the registration table then the INNER JOIN won’t show the result for that employee.

Let’s see it by example.

We will add one record in the employees table and execute the above query.

INSERT INTO employees(name,salary) VALUES('Hulk Hogan',6500);
SELECT * FROM employees;Code language: SQL (Structured Query Language) (sql)

Now let’s run the INNER JOIN query.

SELECT * FROM employees e
INNER JOIN registration r
ON e.id=r.empId
INNER JOIN departments d
ON d.id=r.deptId;Code language: SQL (Structured Query Language) (sql)

As you can see, we got the same result. However, the table must show whatever details the new employee has.

For this, we will need to join these tables using the LEFT JOIN clause.

Let’s see it by example.

Join Three Tables Using LEFT JOIN

Note that, the sequence in which you mention the tables in the LEFT JOIN matters.

In this case, we need to display the information of all employees even if the respective data isn’t available in the registration table. Therefore, we need to make sure of the correct sequence of the tables. Here, the employees table will become the left table because we want all data from it.

SELECT * FROM employees e
LEFT JOIN registration r
ON e.id=r.empId
LEFT JOIN departments d
ON d.id=r.deptId;Code language: SQL (Structured Query Language) (sql)

Here, we have used the same sequence as of the previous query which is correct in this case as well. However, this might not be true every time.

Now let’s execute the query and see the result.

As you can see here, the newly added employee doesn’t have any data in the registrations table but still, we are getting the information of that row in the output. This is because we have used the left join clause.

Conclusion

There is a simple method to understand the flow of the joining multiple tables process. First, try to join two tables and bring the result. This way, you will have the first result. Then think of the new result as a new table and try joining another table.

If you follow this process, you will be able to find the result correct without even executing the query. I hope this tutorial helped you learn something new. If it did, don’t forget to share it with your friends!