MySQL Left Join

When we have seen an introduction on MySQL JOINs, we have also seen 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 conditions in WHERE clause which is 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 keyword 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.


While executing LEFT JOIN query, for each result from the 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 the second table will be displayed with their value. Otherwise, the column name is specified with a 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


users 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 a 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 the 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.

$conn = mysql_connect("localhost","root","");
$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">
<td width="33%"><strong>Student Name</strong></td>
<td width="33%"><strong>Student Email</strong></td>
<td width="33%"><strong>User Type</strong></td>
while($row = mysql_fetch_array($result)){
<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>

In the above code, the ON conditions look 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 student’s table containing the value of student_id as 5, which doesn’t have any match with the user’s table. Even though, this row will be included with the query result to be shown in the browser and has no value for a user_type attribute of this row. So the query will result in data as shown below.


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

↑ Back to Top