In a database such as MySQL, data is divided into a series of tables (the "why" is beyond what I'm writing today) which are then connected together in SELECT commands to generate the output required. I find when I'm running MySQL training, people often get confused between all the join flavours. Let me give you an example to see how it works.

If this isn't quite the question you're looking to have answered, we've got a MySQL IAQ (Infrequently Answered Questions that may help you. And if you want to learn how to make use of this in PHP, see here

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

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 compatibility and doesn't add an extra capabilities.

Extra link to three way join in solution centre (would have added that link in the comment except that it "spam trapped!
(written 2004-12-20 18:38:05)

Commentator	says ...
Graham:	Since I wrote this page (which seems to be generating a lot of traffic in its own right), I've also been asked to provide examples of joining more than two tables - both with a regular join, and also with left joins. Happy to oblige - there's a page here in our solutions centre. (comment added 2005-01-15 19:32:31)

S152 - SQL Primer as Used in MySQL
S157 - More MySQL Commands

Back to Automatic service upgrades

Previous and next or Horse's mouth home

Forward to MySQL - Optimising Selects

A Change is as good as a rest
Christmas break
Review of the Autumn
MySQL - Optimising Selects
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
Automatic service upgrades
Signage
Railway train service, Melksham station
Linux - where to put swap space
Aladdin, or careful what you wish.
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).