Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
For 2021 - online Python 3 training - see ((here)).

Our plans were to retire in summer 2020 and see the world, but Coronavirus has lead us into a lot of lockdown programming in Python 3 and PHP 7.
We can now offer tailored online training - small groups, real tutors - works really well for groups of 4 to 14 delegates. Anywhere in the world; course language English.

Please ask about private 'maintenance' training for Python 2, Tcl, Perl, PHP, Lua, etc.
MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN

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
Update ... January 2010 .... THE FUTURE OF MySQL - please read the page you're on at the moment to help with your joins, then follow this link.


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, updated 2011-01-01)

Commentatorsays ...
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)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
S157 - More MySQL commands
  [159] MySQL - Optimising Selects - (2004-12-21)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [449] Matching in MySQL - (2005-09-24)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [515] MySQL - an FAQ - (2005-12-03)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [673] Helicopter views and tartans - (2006-04-06)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1331] MySQL joins revisited - (2007-09-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)

S152 - SQL Primer as Used in MySQL
  [270] NULL in MySQL - (2005-04-06)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)


Back to
Automatic service upgrades
Previous and next
or
Horse's mouth home
Forward to
MySQL - Optimising Selects
Some other Articles
A Change is as good as a rest
Christmas break
Review of the Autumn
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.
4759 posts, page by page
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, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 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).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2021: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/158_MySQ ... -JOIN.htmlI • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb