TOP

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)

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

mysql>

Orginal Content: http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

Comments are closed.