MySQL Left Join

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

tbl_tags

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.

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.

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

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.

↑ Back to Top

• PHP
• jQuery
• JavaScript
• WordPress
• MySQL
  • MySQL Basics
    • MySQL Data Types
    • MySQL Database Files
    • MySQL SHOW
    • MySQL CONCAT
    • MySQL OrderBy
    • MySQL Date Time
    • MySQL JOINs: An Introduction
    • MySQL Left Join
    • MySQL Right JOIN
    • MySQL vs MySQLi in PHP
  • phpMyAdmin
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js