MySQL Left Join

Last modified on December 5th, 2017 by Vincy.

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 tables using Left JOIN. Also, we are going to see the possible use cases of where to use ON clause and where to use USING clause for writing the join condition.

connect

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 a 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 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 left JOIN between the tbl_links and tbl_tags table with the use of tbl_links.tag_id and tbl_tags.tag_id. 

$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 same column names. Otherwise, we can use the USING clause to state the JOIN condition. The query can be written as,

$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 = "$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
}
?>

This MySQL code tutorial was published on May 25, 2013.

↑ Back to Top

Share this Article