How to Join Two Tables in MySQL [Easy Guide With Examples]

Get an AI Summary of This Article

Want a quick summary? Let AI help you digest the key points from this article.

Summarize in

or

When managing data in databases, organizing information into separate tables can enhance performance and maintain consistency. However, to fulfill certain queries, data from multiple tables often needs to be combined. Joining tables accomplishes this by matching records based on shared field values.

For example, joining a “customers” table with an “orders” table allows retrieving orders and the corresponding customer information in a single query.

Different types of joins provide various methods of combining table data. In this guide, we’ll learn how to join two tables in MySQL, why they are used, and how to use them.

Let’s get started.

Host PHP Websites with Ease [Starts at $11 Credit]

• Free Staging
• Free backup
• PHP 8.0
• Unlimited Websites

TRY NOW

What Are Joins in MySQL?

In short, MySQL Joins lets you access data from multiple tables. A MySQL Join is performed whenever two or more tables are joined in an SQL statement.

The basic format of an SQL join statement looks like this:

SELECT
*
FROM
<first_table>
<join_type>
<second_table>
<join_condition>;

Database Joins merge records from different sources based on matching values in specific columns. Each resulting row includes data from the first table and data from the second table, aligned based on shared column values.

In a join, each row includes all columns from the first table, followed by all columns from the second table. You can specify which columns to display in the SELECT part of the query.

MySQL Joins include:

• MySQL Inner Join (also known as the Simple Join)
• MySQL Left Outer Join (also called the Left Join, it returns matching records from the left table)
• Right Join (this returns the matching records from the right table)
• Full Join (this returns matching records from all tables)

The type of join and the conditions specified determine how each resulting row is constructed and what happens to rows from each table that don’t match the join condition.

For instance, an Inner Join can be used to combine a “customers” table with an “orders” table, retrieving orders alongside corresponding customer information.

How to Join Multiple Tables in MySQL?

If you are working on a large application, such as an e-commerce store, and creating multiple tables in it, such as customers, orders, and products, you may need to join tables.

Let’s look at an example where we’ll get all the products ordered by specific customers.

Let’s create our query:

SELECT * FROM table1
LEFT JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id

If you look at the query above, we first join Table 1 and Table 2, which delivers a temporary table with combined data from Table 1 and Table 2, which is then joined to Table 3. This equation can be extended to more than 3 tables to N tables. You need to make sure that the SQL query has an N-1 join statement in order to join N tables.

The above query will allow you to match the rows from Table 1 (in any case) to the rows of the other two tables. Using LEFT JOIN allows you to join Table 2 and Table 3 with Table 1 (not only Table 2 with Table 1 and Table 3 with Table 2).

You can also add conditions like WHERE, AND, and ORDER BY:

SELECT * FROM table1
LEFT JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id
WHERE month = 'numberHere'
AND (table2.email IS NOT NULL OR table3.email IS NOT NULL)
ORDER BY submitdate DESC

How Many Tables Can Be Joined in MySQL?

According to the official documentation of MySQL 8.0, the maximum number of tables in a JOIN statement is 61. However, note that JOIN statements could require a lot of server resources as the number of tables increases. If this is the case with your query, I highly recommend breaking it into multiple queries to reduce the load on the server.

Types of Joins in MySQL

There are different types of joins, and each one can produce different data sets. Knowing how they work can help you select the most suitable join to achieve the desired data outcome for your query.

• Inner Join
• Right Join
• Left Join
• Union
• Cross Join or Cartesian Product
• Self Join
• Full Outer Join
• Natural Join

To demonstrate the behavior of each join type, let’s consider a scenario where we have two tables: myguests and messages.

Schema for the “messages” Table

We’ll create a table named “messages” to store messages along with the user ID of the sender. Here’s the schema for our table:

CREATE TABLE `messages` (
`id` int(11) NOT NULL,
`message` varchar(255) NOT NULL
);

This table will serve as our reference for testing each join type.

Inner Join

In MySQL, the default type of join is an inner join. This can be specified using either INNER JOIN or just JOIN. Additionally, MySQL supports CROSS JOIN. Unlike certain database systems, where INNER JOIN and CROSS JOIN are separate concepts, MySQL combines them into a single construct.

The structure of Inner Join queries are:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name=table2.column_name;

Inner Join and simple Join are the same. You can also write your query like this:

SELECT column_name(s)

FROM table1

JOIN table2

ON table1.column_name=table2.column_name;

Now let us fetch the Name and the message from our database using Inner join. The query will look something like this:

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
From myguests 
INNER JOIN messages 
ON myguests.id = messages.id";

The CONCAT function is used to join two string columns in MySQL. Now open your index.php, which we have created previously, and copy the following code in it.

<table>

    <tr>

        <td> &nbsp;&nbsp;Name</td>

        <td> &nbsp;&nbsp;Email</td>

        <td> &nbsp;&nbsp;Message</td>

        <!-- <td> &nbsp;&nbsp;Message</td>

        <td> &nbsp;&nbsp;Date</td>!-->

    </tr>

<?php

include 'crud.php';

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
        From myguests 
        INNER JOIN messages 
        ON myguests.id = messages.id";

$result = selectdata($sql);

    if($result != "zero")

    {

        while($row = $result->fetch_assoc())

        {

            echo "<tr>";

            echo "<td>" . $row['name'] . "</td>";

            echo "<td>" . $row['email'] . "</td>";

            if($row['message'] === null){echo "<td>" . &nbsp;'null'. "</td>";} else { echo "<td>" . &nbsp;$row['message']. "</td>"; } ;

            echo "</tr>";

        }

    }

    else

        {

            echo $result;

    }

?>

</table>

?>

When you run this page on your PHP web hosting server, your result will look like this:

As you can clearly see, it has returned only those results that match user_id and where messages are not null.

RIGHT JOIN

RIGHT JOIN combines tables to return all values from the right table and matched values from the left table. It also includes NULL values on the left table when no match is found.

The structure for RIGHT JOIN is:

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name=table2.column_name;

Suppose we want to fetch the Name and message from our database, with messages as the right table and myguests as the left table. The query would be:

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message 
From myguests 
RIGHT JOIN messages 
ON messages.id = myguests.id";

This query returns all messages from the messages table. For each message, it also fetches the corresponding names and emails from the myguests table if available. If there are no matching IDs in the myguests table, NULL values are returned for names and emails.

Now open index.php and replace $sql query with the above. When you run it, your result will be:

If you take a look at the messages table in the screenshot above, you’ll notice some IDs that don’t match any user IDs. That’s why this query returns null in the name and email columns where it doesn’t find any matches in the left column.

LEFT JOIN

LEFT JOINs join the two tables in such a way that they return all the values from the left and matched values from the right tables, and also return NULL on the right table when there is no match found. The structure for a LEFT JOIN is:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name=table2.column_name;

Now let’s retrieve the Name and the message from our database, with the ‘messages’ table as our right table and ‘myguests’ as our left table.

$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, myguests.email, messages.message
From myguests
LEFT JOIN messages
ON messages.id = myguests.id";

Now open index.php and replace the $sql query with the above. When you run it, your result will be:

If you look at the messages table, you will find some IDs that don’t match any user IDs. That’s why this query returns NULL in the Message column, and it doesn’t find any match in the right column.

UNION

UNION in MySQL combines multiple columns from different tables into a single result set. The structure of a UNION query for selecting unique values is:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

And for selecting repeated values from columns:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Now, let us fetch the IDs from our tables.

$sql = "SELECT id FROM myguests
UNION
SELECT id FROM messages";

Now open index.php and replace the $sql query with the one above. When you run it, your result will be:

The query has fetched all the unique IDs found in both tables.

Cross JOIN or Cartesian Product

This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table that consists of records that combine each row from the first table with each row of the second table.

Cross JOIN Syntax is:

SELECT column-name-list

from table-name1

CROSS JOIN

table-name2;

Now, let’s show Cross JOIN using the “myguests” and “messages” tables:

Suppose we want to list all possible combinations of names from the “myguests” table with messages from the “messages” table. We can do this using a Cross JOIN:

SELECT myguests.firstname, myguests.lastname, messages.message
FROM myguests
CROSS JOIN
messages;

This query will give us a list with every combination of a name from the “myguests” table and a message from the “messages” table.

Self JOIN

A Self Join is a type of join that combines the rows of a table with itself. While it may not be immediately apparent how this could be useful, it actually has many common applications.

Self Join Syntax:

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Now, let’s demonstrate a Self Join using the “messages” table:

Suppose we want to find pairs of messages that have the same sender. We can achieve this using a Self Join:

SELECT m1.id, m1.sender, m2.id, m2.sender
FROM messages m1, messages m2
WHERE m1.sender = m2.sender
AND m1.id <> m2.id;

This query will return pairs of messages with the same sender, excluding cases where a message is compared with itself.

Self Joins are particularly useful in scenarios where you need to compare rows within the same table, such as in hierarchical structures or when dealing with recursive relationships.

FULL OUTER JOIN

The FULL OUTER JOIN returns all records when there is a match in either the left (table1) or right (table2) table records. It essentially combines the results of both LEFT JOIN and RIGHT JOIN.

Note: FULL OUTER JOIN can potentially return very large result sets!

Full Outer Join Syntax:

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Here’s a brief example to illustrate FULL OUTER JOIN:

Suppose we have two tables, “employees” and “departments”, and we want to retrieve all records from both tables, including matching records and unmatched records:

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

This query will return all team member records, including those without a corresponding department, and all department records, including those without any associated employees.

NATURAL JOIN

Natural Join is a type of join in SQL that combines rows from tables based on columns with the same name and data type. This join only includes common columns once in the result set.

To use Natural Join, you can combine two or more tables using the following syntax:

SELECT column_1, column_2, ..., column_n
FROM table_1
NATURAL JOIN table_2;

Let’s illustrate Natural Join using the “messages” table:

Imagine we have two tables, “messages” and “users”, both containing a column named “user_id”. We aim to fetch all records from both tables where the “user_id” column values match:

SELECT *
FROM messages
NATURAL JOIN users;

This query will execute a Natural Join on the “user_id” column, merging rows from both tables where the “user_id” values match. The resulting dataset will feature the “user_id” column only once.

Summary

Almost all of the real-world data queries are performed using some join in SQL. Joins are like puzzle pieces that help us gather information from various sources.

In this article, we’ve talked about SQL joins and how MySQL uses them to combine data from different tables.

By understanding joins, you’ll be able to handle data smoothly in your PHP applications, regardless of its structure.

SELECT
tb_1.*,
tb_2.*
FROM database_1.table_name_1 tb_1
JOIN database_2.table_name_2 tb_2 ON tb_1.id = tb_2.id

SELECT *

FROM TableA a, TableB b

SELECT columns  
FROM table1  
JOIN table2  
ON table1.common_column = table2.common_column;

SELECT * FROM table1  
UNION  
SELECT * FROM table2;

Shahzeb Ahmed

Shahzeb is a Digital Marketer with a Software Engineering background, works as a Community Manager — PHP Community at Cloudways. He is growth ambitious and aims to learn & share information about PHP & Laravel Development through practice and experimentation. He loves to travel and explore new ideas whenever he finds time. Get in touch with him at [email protected]

Get Connected on: Twitter Community Forum