MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

mysql> select * from demo_people;

+------------+--------------+------+

| name | phone | pid |

+------------+--------------+------+

| Mr Brown | 01225 708225 | 1 |

| Miss Smith | 01225 899360 | 2 |

| Mr Pullen | 01380 724040 | 3 |

+------------+--------------+------+

3 rows in set (0.00 sec)


mysql> select * from demo_property;

+------+------+----------------------+

| pid | spid | selling |

+------+------+----------------------+

| 1 | 1 | Old House Farm |

| 3 | 2 | The Willows |

| 3 | 3 | Tall Trees |

| 3 | 4 | The Melksham Florist |

| 4 | 5 | Dun Roamin |

+------+------+----------------------+

5 rows in set (0.00 sec)

f I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:

mysql> select name, phone, selling

from demo_people join demo_property

on demo_people.pid = demo_property.pid;

+-----------+--------------+----------------------+

| name | phone | selling |

+-----------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+-----------+--------------+----------------------+

If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join - thus ensuring (in my example) that every PERSON gets a mention:

mysql> select name, phone, selling

from demo_people left join demo_property

on demo_people.pid = demo_property.pid;

+------------+--------------+----------------------+

| name | phone | selling |

+------------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Miss Smith | 01225 899360 | NULL |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+------------+--------------+----------------------+

If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join - in my example, that means that each property gets a mention even if we don't have seller details:

mysql> select name, phone, selling

from demo_people right join demo_property

on demo_people.pid = demo_property.pid;

+-----------+--------------+----------------------+

| name | phone | selling |

+-----------+--------------+----------------------+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

| NULL | NULL | Dun Roamin |

+-----------+--------------+----------------------+