MySQL Left Join

When we have seen an introduction on MySQL JOINs, we have also see about the list of available types in it. Among them, we have started with Left Join to know how it is used to retrieve rows from more than one table by providing possible join condition.

Unlike simple join, here, the ON clause is used to link two table instead of WHERE clause. For using left join, the WHERE clause is used to have additional conditions added with ON condition. Like, how we separate multiple condition in WHERE clause which are separated by MySQL AND operator, the left join query can be created as follows to join students and users tables.

$query = "SELECT students.student_name, students.student_email, users.user_type FROM students LEFT JOIN users ON (students.student_id = users.user_id)"; 

MySQL left join query will retrieve rows from the table which is specified in left hand side of key word LEFT JOIN. Retrieval from the left table is done based on the condition specified in WHERE clause if any. Otherwise all rows will be fetched without any conditions.

mysql_left_join

While executing LEFT JOIN query, for each result from left table, it will be checked if match found in right hand side table based on the ON condition. If match found, then the requested column of second table will be displayed with their value. Otherwise, the column name is specified with NULL value.

For example, in the above query, the join operation is made between two tables students and users. Both of these tables looks like as follows.

students Table

mysql_student_table

users Table

mysql_user_table

The query will be created to left join users table with students table to retrieve matched rows from users table, after getting all the data from left table, that is students. So, the query is as stated as above to get student_name and student_email, from students table and also to get user_type attribute from users table, once the condition specified in ON clause is satisfied. So we can write MySQL left join code as follows.

<?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("payload",$conn);
$query = "SELECT students.student_name, students.student_email, users.user_type FROM students LEFT JOIN users ON (users.user_id = students.student_id)"; 
$result = mysql_query($query) or die(mysql_error());
?>
<table width="500px" cellpadding="10" cellspacing="1" border="1" style="border-color:#CCCCCC">
<tr>
<td width="33%"><strong>Student Name</strong></td>
<td width="33%"><strong>Student Email</strong></td>
<td width="33%"><strong>User Type</strong></td>
</tr>
<?php
while($row = mysql_fetch_array($result)){
?>
<tr>
<td width="33%"><?php echo $row['student_name']; ?></td>
<td width="33%"><?php echo $row['student_email']; ?></td>
<td width="33%"><?php echo $row['user_type']; ?></td>
</tr>
<?php
}
?>

In the above code, the ON conditions looks for the result from user table to be displayed to the browser by comparing student_id and user_id to check both are equals. We can see that, the last entry of the students table containing value of student_id as 5, which doesn’t have any match with the users table. Even though, this row will be included with the query result to be shown to the browser and has no value for user_type attribute of this row. So the query will result data as shown below.

mysql_left_join_result

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

↑ Back to Top