MySQL - Left Join
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 LEFT JOIN Example
Here is the two tables, family table and food table
| Position | Age |
|---|
| Dad | 41 |
| Mom | 45 |
| Daughter | 17 |
| Dog | |
| Meal | Position |
|---|
| Steak | Dad |
| Salad | Mom |
| Spinach Soup | |
| Tacos | Dad |
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
// 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 "
";
}
?>
MySQL LEFT JOIN 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!
