How To Join Two Tables In MySQL

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

1 2 3 4

$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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

<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.

1 2 3 4

$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.

1 2 3 4

$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.

1 2 3

$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.