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

First, some sample data:
Mr Brown, Person number 1, has a phone number 01225 708225
Miss Smith, Person number 2, has a phone number 01225 899360
Mr Pullen, Person number 3, has a phone number 01380 724040
and also:
Person number 1 is selling property number 1 - Old House Farm
Person number 3 is selling property number 2 - The Willows
Person number 3 is (also) selling property number 3 - Tall Trees
Person number 3 is (also) selling property number 4 - The Melksham Florist
Person number 4 is selling property number 5 - Dun Roamin.



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)

mysql>



If 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 |
+-----------+--------------+----------------------+
4 rows in set (0.01 sec)

mysql>



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 |
+------------+--------------+----------------------+
5 rows in set (0.00 sec)

mysql>



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 - im 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 |
+-----------+--------------+----------------------+
5 rows in set (0.00 sec)

mysql>



An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatability and doesn't add an extra capabilities.