left join and right join, inner join

Provided DataBase

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           |

+------+------+----------------------+---------------------------+------------------------+-------------------------
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 |

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

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           |
+-----------+--------------+----------------------+
-+--------------------------------------------+--------------------------+------------