Bookmark and Share

MySQL LEFT JOIN

In the previous lesson, Mysql Joins we learned how to do a basic join of two tables. This lesson will teach you how to do a specialized join: left join.

Advertise on Tizag.com

MySQL LEFT JOIN Explanation

How is a LEFT JOIN different from a normal join? First of all, the syntax is quite different and somewhat more complex. Besides looking different, the LEFT JOIN gives extra consideration to the table that is on the left.

Being "on the left" simply refers to the table that appears before the LEFT JOIN in our SQL statement. Nothing tricky about that.

This extra consideration to the left table can be thought of as special kind of preservation. Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to.

MySQL Join and LEFT JOIN Differences

Here are the tables we used in the previous Mysql Joins lesson.

MySQL family and food Tables:

PositionAge
Dad41
Mom45
Daughter17
Dog
MealPosition
SteakDad
SaladMom
Spinach Soup
TacosDad

We executed a simple query that selected all meals that were liked by a family member with this simple join query:

Simplified MySQL Query:

SELECT food.Meal, family.Position 
FROM family, food  
WHERE food.Position = family.Position

Result:

Dad - Steak
Mom - Salad
Dad - Tacos

When we decide to use a LEFT JOIN in the query instead, all the family members be listed, even if they do not have a favorite dish in our food table.

This is because a left join will preserve the records of the "left" table.

MySQL LEFT JOIN Example

The code below is the exact same as the code in the previous lesson, except the LEFT JOIN has now been added to the query. Let's see if the results are what we expected.

PHP and MySQL Code:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
 "FROM family LEFT JOIN food ".
	"ON family.Position = food.Position"; 
	 
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
	echo $row['Position']. " - ". $row['Meal'];
	echo "<br />";
}
?>

Display:

Dad - Steak
Dad - Tacos
Mom - Salad
Daughter -
Dog -

Success! The LEFT JOIN preserved every family member, including those who don't yet have a favorite meal in the food table! Please feel free to play around with LEFT JOIN until you feel like you have a solid grasp of it. This stuff isn't easy!

Bookmark and Share




Download Tizag.com's MySQL Book

If you would rather download the PDF of this tutorial, check out our MySQL eBook from the Tizag.com store. You may also be interested in getting the PHP eBook

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials