To illustrate, have created two tables 'food' and 'family',
mysql> select * from food;
+--------------+----------+
| Meal | Position |
+--------------+----------+
| Steak | DAD |
| Salad | Mom |
| Tacos | DAD |
| Spinach Soup | NULL |
+--------------+----------+
4 rows in set (0.00 sec)
mysql> select * from family
-> ;
+----------+------+
| Position | AGE |
+----------+------+
| Dad | 41 |
| mom | 45 |
| Daughter | 17 |
| Dog | 0 |
+----------+------+
4 rows in set (0.01 sec)
mysql>
Combining both the tables.
mysql> SELECT * FROM food, family;
+--------------+----------+----------+------+
| Meal | Position | Position | AGE |
+--------------+----------+----------+------+
| Steak | DAD | Dad | 41 |
| Salad | Mom | Dad | 41 |
| Tacos | DAD | Dad | 41 |
| Spinach Soup | NULL | Dad | 41 |
| Steak | DAD | mom | 45 |
| Salad | Mom | mom | 45 |
| Tacos | DAD | mom | 45 |
| Spinach Soup | NULL | mom | 45 |
| Steak | DAD | Daughter | 17 |
| Salad | Mom | Daughter | 17 |
| Tacos | DAD | Daughter | 17 |
| Spinach Soup | NULL | Daughter | 17 |
| Steak | DAD | Dog | 0 |
| Salad | Mom | Dog | 0 |
| Tacos | DAD | Dog | 0 |
| Spinach Soup | NULL | Dog | 0 |
+--------------+----------+----------+------+
16 rows in set (0.00 sec)
mysql>
Execute the following simple "join" query that selected all meals that were liked by a family members.
mysql> SELECT * FROM food, family WHERE food.Position = family.Position;
+-------+----------+----------+------+
| Meal | Position | Position | AGE |
+-------+----------+----------+------+
| Steak | DAD | Dad | 41 |
| Tacos | DAD | Dad | 41 |
| Salad | Mom | mom | 45 |
+-------+----------+----------+------+
3 rows in set (0.00 sec)
mysql>
Only Meal and position table is required. So, it is possible to filter the query as below,
mysql> SELECT food.Meal, family.Position
-> FROM family, food
-> WHERE food.Position = family.Position;
+-------+----------+
| Meal | Position |
+-------+----------+
| Steak | Dad |
| Salad | mom |
| Tacos | Dad |
+-------+----------+
3 rows in set (0.01 sec)
mysql>
Now, when doing the left join all the family members including null value will be accounted. Find the below query.
mysql> SELECT family.Position, food.Meal FROM family LEFT JOIN food ON family.Position = food.Position;
+----------+-------+
| Position | Meal |
+----------+-------+
| Dad | Steak |
| Dad | Tacos |
| mom | Salad |
| Daughter | NULL |
| Dog | NULL |
+----------+-------+
5 rows in set (0.01 sec)
mysql>
mysql> select * from food;
+--------------+----------+
| Meal | Position |
+--------------+----------+
| Steak | DAD |
| Salad | Mom |
| Tacos | DAD |
| Spinach Soup | NULL |
+--------------+----------+
4 rows in set (0.00 sec)
mysql> select * from family
-> ;
+----------+------+
| Position | AGE |
+----------+------+
| Dad | 41 |
| mom | 45 |
| Daughter | 17 |
| Dog | 0 |
+----------+------+
4 rows in set (0.01 sec)
mysql>
Combining both the tables.
mysql> SELECT * FROM food, family;
+--------------+----------+----------+------+
| Meal | Position | Position | AGE |
+--------------+----------+----------+------+
| Steak | DAD | Dad | 41 |
| Salad | Mom | Dad | 41 |
| Tacos | DAD | Dad | 41 |
| Spinach Soup | NULL | Dad | 41 |
| Steak | DAD | mom | 45 |
| Salad | Mom | mom | 45 |
| Tacos | DAD | mom | 45 |
| Spinach Soup | NULL | mom | 45 |
| Steak | DAD | Daughter | 17 |
| Salad | Mom | Daughter | 17 |
| Tacos | DAD | Daughter | 17 |
| Spinach Soup | NULL | Daughter | 17 |
| Steak | DAD | Dog | 0 |
| Salad | Mom | Dog | 0 |
| Tacos | DAD | Dog | 0 |
| Spinach Soup | NULL | Dog | 0 |
+--------------+----------+----------+------+
16 rows in set (0.00 sec)
mysql>
Execute the following simple "join" query that selected all meals that were liked by a family members.
mysql> SELECT * FROM food, family WHERE food.Position = family.Position;
+-------+----------+----------+------+
| Meal | Position | Position | AGE |
+-------+----------+----------+------+
| Steak | DAD | Dad | 41 |
| Tacos | DAD | Dad | 41 |
| Salad | Mom | mom | 45 |
+-------+----------+----------+------+
3 rows in set (0.00 sec)
mysql>
Only Meal and position table is required. So, it is possible to filter the query as below,
mysql> SELECT food.Meal, family.Position
-> FROM family, food
-> WHERE food.Position = family.Position;
+-------+----------+
| Meal | Position |
+-------+----------+
| Steak | Dad |
| Salad | mom |
| Tacos | Dad |
+-------+----------+
3 rows in set (0.01 sec)
mysql>
Now, when doing the left join all the family members including null value will be accounted. Find the below query.
mysql> SELECT family.Position, food.Meal FROM family LEFT JOIN food ON family.Position = food.Position;
+----------+-------+
| Position | Meal |
+----------+-------+
| Dad | Steak |
| Dad | Tacos |
| mom | Salad |
| Daughter | NULL |
| Dog | NULL |
+----------+-------+
5 rows in set (0.01 sec)
mysql>