X
    Categories: Database Tutorials, Tips and GuidesLearn PHP Tutorials, Tips And Guides

How to Join Two Tables in MySQL

In the previous installment of this MySQL series, I demonstrated how you could fetch data using MySQL clauses. In this part we will learn about Joins in MySQL, why they are used and how to use them. Let’s get started.

What are Joins?

Joins are the most important thing in relational databases. They are used to join two or more different tables on a point in which both the tables match the same value and property. There are four easy ways to join two or more tables:

  1. Inner Join
  2. Left Join
  3. Right Join
  4. Union

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 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 ID’s which are found in both the tables.

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. Here is a short introduction and list of major topics in this MySQL series. 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.

Ahmed Khan: Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com