MySQL Left Join

by Vincy. Last modified on July 19th, 2022.

Left JOIN is one of the methods used in joining the database tables as we have seen in the introduction of MySQL JOINs. In this tutorial, we are going to see an example to read data from more than one table using Left JOIN.

Also, we are going to see the possible use cases of where to use the ON clause and where to use USING clause for writing the join condition.

I take the two tables tbl_links and tbl_tags to explain how the Left JOIN works. The tbl_links table has a tag_id column to map a tag with link data. Similarly, the tbl_tags has a column tag_id marked as its primary key index.

We are going to use tbl_links.tag_id and tbl_tags.tag_id to apply Left JOIN between these tables. The structure and the data for these tables are shown below for your reference.

tbl_links

link_id links_name tag_id
1 google.com 3
2 phppot.com 1
3 facebook.com 2

tbl_tags

tag_id tag_name
1 PHP
2 Social Networking

Left JOIN Example

MySQL left join query will retrieve rows from the table which is specified on the left side of the keyword LEFT JOIN of the query. This query will read data from the right side table if the JOIN condition is satisfied or show NULL otherwise. The following Venn diagram shows how the Left JOIN works.

left-join

The following query is used to apply the left JOIN between the tbl_links and tbl_tags table with the use of tbl_links.tag_id and tbl_tags.tag_id.

<?php
$query = "SELECT tbl_links.links_name, tbl_tags.tag_name FROM tbl_links 
    LEFT JOIN tbl_tags ON tbl_links.tag_id = tbl_tags.tag_id";
?>

The table name specification in the JOIN condition will solve the problem of ambiguity when the tables have the same column names. Otherwise, we can use the USING clause to state the JOIN condition. The query can be written as,

<?php
$query = "SELECT links_name, tag_name FROM tbl_links 
    LEFT JOIN tbl_tags USING tag_id";
?>

These queries will read data from the table as shown below.

links_name tag_name
google.com NULL
phppot.com PHP
facebook.com Social Networking

PHP Code to Read Data using Left JOIN

<?php
$conn = mysqli_connect("localhost", "root", "", "payload");
$query = "SELECT tbl_links.links_name, tbl_tags.tag_name FROM tbl_links LEFT JOIN tbl_tags ON tbl_links.tag_id = tbl_tags.tag_id";
$result = mysqli_query($conn, $query) or die(mysqli_error($conn));
?>
<table width="500px" cellpadding="10" cellspacing="1" border="1"
	style="border-color: #CCCCCC">
	<tr>
		<td width="33%"><strong>Link Name</strong></td>
		<td width="33%"><strong>Tag Name</strong></td>
	</tr>
<?php
while ($row = mysqli_fetch_array($result)) {
    ?>
<tr>
		<td width="33%"><?php echo $row['links_name']; ?></td>
		<td width="33%"><?php echo $row['tag_name']; ?></td>
	</tr>
<?php
}
?>
Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page