How to Join Two Tables in MySQL

In the previous installment of this MySQL series, I demonstrated how you can fetch data using MySQL clauses. In this part we will learn about the Joins function 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

How Can I Link Two Tables in MySQL

MySQL Joins let you access data from multiple tables. A MySQL Join is performed whenever two or more tables are joined in an SQL statement. MySQL Joins include: the MySQL Inner Join (also known as the Simple Join), the MySQL Left Outer Join (also called the Left Join, it returns matching records from the left table), the Right Join (this returns the matching records from the right table), and the Full Join (this returns matching records from the all tables). Using MySQL JOINs, you’ll be able to join more than two tables.

In the realm of MySQL, the Inner Join stands as the default join method. When prompted by specific keywords, this method selects all rows from both tables, provided there’s a match between the columns in both tables. Experience the synergy of Inner Join and optimized performance with PHP web hosting.

Unlike SQL, MySQL does not consider the Outer Join as a separate Join type. To get the same results as the Outer Join, you need to join the Left Outer Join and Right Outer Join.

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.

Let us start by adding a new table in our database which will contain the message along with the user ID which have sent this message, we will name it messages. The schema of our table is:

CREATE TABLE `messages` (

 `id` int(11) NOT NULL,

 `message` varchar(255) NOT NULL

)

We will be using the same selectdata function which we have created in our crud.php file. Now let us get started by joining these two tables. You can also fill your table so you can practice it.

Inner Join

Inner Join joins table in such a way that it only shows those results which matches the condition that is given and hide others. 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 both are 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 be 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 strings column in MySQL. Now open your index.php which we have created previously 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 which matches user_id and where messages is not null.

RIGHT JOIN

RIGHT JOIN joins the two tables in such a way that it returns all the value from the right and matched value from left tables and also return null on left table when there is no match found. The structure for RIGHT JOIN is:

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name=table2.column_name;

Now let us fetch the Name and the message from our database making messages in our right table and myguests in our left table.

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

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 messages table, you will see some IDs’ which won’t match any user ID’s that’s why this query returns null in name and email column where it won’t find any match in left column.

LEFT JOIN

LEFT Joins joins the two table in such a way that it returns all the value from the left and matched value from right tables and also return null on right table when there is no match found. The structure for LEFT JOIN is:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name=table2.column_name;

Now let us fetch the Name and the message from our database making messages in our right table and myguests in 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 query in place of $sql with the above. When you run it, your result will be:

If you take a look at messages table, you will find some ID’s which won’t match any user ID’s that’s why this query returns null in Message column where it won’t find any match in right column.

UNION

UNION in MySQL is used to union multiple columns from different table into a single column. The structure of 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 is:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Now let us fetch the ID’s from our tables.

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

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

The query has fetched us all the unique IDs which are found in both the 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;

Self JOIN

A self JOIN is a regular join, but the table is joined with itself.

SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

FULL OUTER JOIN

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

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

SELECT column_name(s)

FROM table1

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

Join Multiple Tables

In the previous blogs, you have learned how to join two tables together using different SQL join queries. But if you are working on a large application i.e. building an e-commerce store and creating multiple tables in it such as customers, orders and products, the complexity in joining tables can definitely arise. To sort this out, you need to get all the products ordered by specific customers. The schema stands the same, so I’m not declaring it here. Let’s see the query:

SELECT * FROM table1

LEFT JOIN table2

ON table2.id = table1.id

LEFT JOIN table3

ON table3.id = table1.id

We first join table 1 and table 2 which deliver a temporary table with combined data from table1 and table2, which is at that point joined to table3. This equation can be extended to more than 3 tables to N tables, You just need to make sure that the SQL query ought to have N-1 join statement in arrange to join N tables.

The above query will allow you to match the rows from table1 (in any case) to the rows of other two tables. Using LEFT JOIN allows you to join table2 and table3 with table1 (not only table2 with table1 and table3 with table2).
You can also add conditions like WHERE, AND, and ORDERBY

SELECT * FROM table1

LEFT JOIN table2

   ON table2.id = table1.id

LEFT JOIN table3

   ON table3.id = table2.id

WHERE month = 'numberHere'

   AND (table2.email IS NOT NULL OR table3.email IS NOT NULL)

ORDER BY submitdate DESC

Conclusion:

In this tutorial, we learnt about joins which are used in relational databases a lot. Joins are not only used for two tables only and you can join more than two table using the same technique. In the eight and final installment of this MySQL series, I will discuss how to use Regular Expressions (REGEX) for fetching and sorting data in MySQL. For that, subscribe to our blog newsletter and you can sign up as well for our Managed PHP Stack and start testing these tutorials on our PHP optimized servers.

SELECT *

FROM TableA a, TableB b

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