Left Join Multiple Tables in MySQL

The LEFT JOIN is often used in any project to combine the data of two tables. You might have joined only two tables using the LEFT JOIN until now. But today, we will learn to join multiple tables using the LEFT JOIN. It is very simple and easy to understand if you get the basic flow. So, without further ado, let’s get started!

Also check: MySQL RIGHT JOIN

What is a LEFT JOIN?

You might have already worked with the left join in MySQL before. But let’s revise it quickly.

Unlike the INNER JOIN where only common records of two tables are shown in the output, the LEFT JOIN combines the all data from the first table and only the matched data from the second table.

But what if a particular row is present in the first table and there is no row matching in the second table?

In this case, the values from the first table row will be displayed along with the NULL value for the second table row.

If the situation is vice-versa, which means the row is present in the second table but not in the first table, then that row will be excluded.

Skip the basics, and jump right to using the MySQL Left Join on Multiple Tables

MySQL LEFT JOIN Syntax

The general syntax of the left join is as follows-

SELECT colNames
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;Code language: SQL (Structured Query Language) (sql)

As told earlier, all the data from the table1 will be shown in the output and the common data from the table2 will be shown. If there is no common data in the table2, those columns will be filled with the NULL value in the output table.

The left join is frequently used for analytical tasks.

MySQL LEFT JOIN On Single Tables

Let’s take an example to understand a simple use case of the left join. We have an application where users can sign up and make transactions. We want to find out all those users who didn’t make a single transaction ever.

In this case, we can simply use the left join. In the query, the “users” table where the signup data is available will act as a left table. Whereas the transaction table will act as the right table. Using some aggregate functions and comparison operators, we can easily find out the list of users who didn’t make a single transaction in the application.

Let’s demonstrate the example.

Here are two tables as shown in the image below.

Now let’s find out the names of all those users who made at least a single transaction using the left join.

SELECT u.id, u.name, count(t.userId) as tnxCount 
FROM users u 
LEFT JOIN tnx t
ON u.id=t.userId
GROUP BY u.id
HAVING tnxCount > 0;Code language: SQL (Structured Query Language) (sql)

As you can see, we got the perfect output.

That was too easy, right? Let’s now try to left join on multiple tables.

MySQL LEFT JOIN On Multiple Tables

MySQL allows us to use the left join on multiple tables.

First, we need to create one more table which we will use to left join the previous result. The new table will store the recharges history of the users.

Following is the table description and the data of the recharges table.

Now let’s write a query to left join multiple tables.

SELECT *
FROM users u 
LEFT JOIN tnx t
ON u.id=t.userId
LEFT JOIN recharges r
ON u.id = r.userId;Code language: SQL (Structured Query Language) (sql)

Here, we have simply used the recharges table to join with the previous result.

Note that, we have not used any aggregate function or GROUP BY clause to count anything. This is because the records keep multiplying according to the number of matching rows in the new table.

This way, we can use the left join on multiple tables.

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

The result we got is not so nice because we didn’t use any functions and all the data we got is simply a combination of all tables extracted with the left join.

Conclusion

This is a simple example of the left join on multiple tables.

However, if you want more advanced features such as counting the number of transactions as well as the recharges which are kept in different tables, you have to use the subqueries and store the result of the previous left join in a temporary table.

You can try the above example with the subqueries to count to recharges and the transactions of each user.