What is left join – MySQL LEFT JOIN with Examples

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

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

tbl_tags

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.

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.

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
}
?>