|
| Home | Accessibility | Courses | Diary | The Mouth | Forum | Resources | Site Map | About Us | Contact |
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
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.
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:
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:
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:
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)
S157 - More MySQL Commands
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.
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).
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) |
Associated topics are indexed under
S152 - SQL Primer as Used in MySQLS157 - More MySQL Commands
| 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 restChristmas 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.
1634 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 at 50 posts per pageThis 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).
| Comment: "The best explanation of JOINs I've ever seen. Thank you it ..." Visitor Ranking 4.9 (5=excellent, 1=poor) | ||
| 1 unpublished comment pending on this page | ||
Comment by Atomic AlienZ (published 2008-04-21)
The best explanation of JOINs I've ever seen. Thank you it was most helpful=) [#3357]
Comment by Anon (published 2008-04-13)
Is the outer join really needed. I mean we can get better results by using " NOT IN". [#3356]
Comment by Anon (published 2008-04-13)
Is the outer join really needed. I mean we can get better results by using " NOT IN". [#3355]
Comment by sharath (published 2008-04-13)
it was very clear ... [#3353]
Comment by Anon (published 2008-02-29)
Thank you. All I was looking for. [#3352]
Comment by Saidul Islam (published 2008-04-13)
I've read many article but i cudn't understand the SQL JOIN. But this article takes 2 minute to teach me that. Nice article... Thanks to author. [#3350]
Comment by Jam (published 2008-01-21)
nice one! (~~,) [#3349]
Comment by Danish Ejaz (published 2007-12-31)
Good,Excellent Understanding of joins.
Thanks a lot [#3347]
Comment by Mohiuddin Khan Inamdar (published 2007-12-20) Suggested link.
Nice One ...... good effort to help out others..
just landed here because of exclusion queries.. from google
have a look at this page too.
http://r937.com/list.cfm?page=sql-articles
regards
Mohnkhan [#3345]
Comment by Anon (published 2007-12-19)
Illustrated in simple way. Very easy to understand. Thanks. [#3343]
Comment by Sudheesh (published 2007-12-19)
great.........easy to understand..........keep it up.......... [#3342]
Comment by Rashid Ali (published 2007-12-19)
ITs just a great artical to uderstand the JOINS in SQL ... great work ... keep it up [#3341]
Comment by Nirmal (published 2007-12-19)
Very good explanation [#3338]
Comment by Shawn (published 2007-10-06)
Thanks. Neat explanation. [#3337]
Comment by Anon (published 2007-09-26)
great!!!simple !!hurray!!!!!!!!!!! [#3336]
Comment by Sameer (published 2007-09-02)
Nobody can clear JOINS as this manual has done. It is SUPERB.. Thanks. It helped me a lot to understand. I will appreciate if you can make much clear to the INNER AND SELF JOINS. [#3334]
Comment by Anonymous (published 2007-09-02)
great explanation on a subject that's very confusing. [#3331]
Comment by kj (published 2007-09-02)
No one can explain with more simplicity than this. Any one who doesn't understands after reading this simple example can't ever understand. Really thanx a LOT [#3330]
Comment by Anon (published 2007-07-26)
At last! A simple effective explanation of the 'joins'. [#3329]
Comment by Mandar (published 2007-07-25)
Thanks for the Excellent help ! :) [#3328]
Comment by Rupinder Singh (published 2007-07-17)
A good brief tutorial on joins. Helped me a lot . [#3327]
Comment by MyMaster (published 2007-07-10)
Really it is excellent ............. [#3323]
Comment by edw (published 2007-06-26)
I'm very pleased with your clear explanation. I'v seen some other examples but this one just hits it all. Directly!
It made me understand. [#3321]
Comment by El Sayed (published 2007-06-13)
I'll use this idea explaining Joins 2morow isA
Thanks [#3320]
Comment by Veejay (published 2007-07-10)
Its excellent, to the point and very clear. thanx a lot for this buddy. [#3319]
Comment by Priya Saini (published 2007-04-24)
This is 'Simply' the best explaination. Concept of join always confused me... nw not any more [#3311]
Comment by vellaidurai (published 2007-04-03)
this is one of best and very simple explanations and examples.do keep this.. [#3310]
Comment by Roman (published 2007-05-04)
Very good article which clears the things which were not clearly described in the manual.
So it's a good example when developers can make something seem much more difficult than it actually is. I'm good in SQL, I've known about LEFT and RIGTH joins, but seeing INNER and OUTER JOINs I thought that there was still something that I didn't know! :-)
So, there are actually just TWO types of join (having in mind that RIGHT JOIN is the same as LEFT, but with different table order):
So we have ordinary JOIN, which is THE SAME as joining tables without JOIN keyword, but delimiting tables with commas and putting condition in WHERE clause (you can call it "implicit join"), so the example from the article will look this way:
select name, phone, selling
from demo_people, demo_property
WHERE demo_people.pid = demo_property.pid;
which returns only matching rows combinations from both tables,
and we have LEFT JOIN, which always returns ALL rows from the first table, and if a row from the first table doesn't have any matching rows in the second, we get single resulting row with null values for all columns of second table.
I prefer to use "implicit joins" instead of JOIN keyword when I need only matching rows, and to use LEFT JOINs when I need "NULL rows" to present in the result (if they exist of course), as it helps me to immediately see what type of query it is (as the difference is really important!) - completely different syntax does not give you a chance to confuse. [#3305]
Comment by K (published 2007-05-04)
Thank you very much for making it really clear ! [#3269]
Comment by Dror (published 2006-12-16)
Thanks (-: [#3260]
Comment by Anon (published 2006-12-16)
nice, thanks [#3254]
Comment by Jose Espinal (published 2006-11-10) Suggested link.
EXCELLENT!!!!!!!!!! thank you man... THANK YOU!!!! [#3250]
Comment by Anon (published 2006-11-10)
Simple and effective to understand join sentence. [#3244]
Comment by Cyriac Peter (published 2006-10-06)
A very good article. Thanks a lot for posting this. [#3240]
Comment by suresh (published 2006-09-27)
Simple and easy understandable examples.
Thanks a lot.
give examples for inner and outer joins also. [#3238]
Comment by Anon (published 2006-09-27)
This is really a good tutorial for those who want to understand joins [#3237]
Comment by Pankaj Jaiswal (published 2006-09-27)
It provides an execellent understanding of Joins [#3235]
Comment by vipin (published 2006-09-18)
very good explanation and examples... [#3234]
Comment by shawn (published 2006-09-12)
WOW THANKS FOR MAKING THIS SO SIMPLE. A lot of articles on these subjects are often overcomplicated. [#3233]
Comment by Anon (published 2006-09-08)
it was really nice,, he really can explaid it [#3231]
Comment by Prasanna R Raval (published 2006-08-21)
This is very good example for Join in mysql. [#3227]
Comment by Marcob (published 2006-08-21)
thank you.
now i really understand left adn right joins [#3225]
Comment by Anon (published 2006-08-11)
You're good... thx man. [#3224]
Comment by justGrayWind (published 2006-08-09)
Просто и доступно :)
Ура! Спасибо!
Simple and to the point :)
Hurray! Thanks! [#3223]
Comment by Murugesan (published 2006-07-13)
Its a very excellent article for me [#3209]
Comment by Mohamed (published 2006-07-13)
Thank you, it is short and good. [#3208]
Comment by bharathiraja (published 2006-06-14)
It is really usefull, no one can explain other than this [#3202]
Comment by Manish Jain (published 2006-06-14)
Very well defined clear and short description of Inner, Left and Right joins [#3200]
Comment by Rajan Rana (published 2006-06-10)
very brief and well written ,
well done [#3150]
Comment by Graham (published 2006-06-03) Suggested link.
The Accessibility link on the right of every page lets you select a larger or smaller font size, and it also lets you change the page colours. Regrettably, there is no single size / combinations that's ideal for everyone. [#3138]
Comment by Chris (published 2006-06-03)
Thanks for all the information, I was quited puzzled about the differences before. Now it's all clear. :)
One small tip: font-size is a little too small, it's hard to read sometimes. [#3134]
Comment by Graham (published 2006-06-01) Suggested link.
Sasha, I'm aware that left joins are widely supported by databases, and right join is much less common. Indeed, right join was added after left join to MySQL and people running an ancient versions may find left words and right doesn't. I don't see that this makes any difference as far as the text on this page is concerned, though - it's a broad overview and helps a lot of people start to understand the difference - it creates "lighbulb moments" and if people need a lot more they can go on and read more in the thousands-of-pages long manual. [#3133]
Comment by Sasha (published 2006-06-01)
Hi!
In the MySQL reference written following:
"RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN. "
so, your examples are incorrect. [#3117]
Comment by ZigZag (published 2006-05-07)
Spot on for us puzzled by joins [#3100]
Comment by Anon (published 2006-04-28)
Thank you thank you [#394]
Comment by Anon (published 2006-04-15)
Best short left right join description I've seen yet [#390]
Comment by Anon (published 2006-03-25)
Thank you.. now I understand joins [#352]
Comment by Grateful Anon (published 2006-03-25)
This is absolutely perfect - I was puzzling over joins today, not completely understanding what the difference was between INNER, LEFT and RIGHT. This page explained things so that a light bulb went on over my head the very first time I read it. Thank you! [#349]
Comment by Patrick (published 2006-03-25)
Great explanation -- many thanks!!! [#344]
Comment by Anon (published 2005-07-26)
Fantastic: finally understood joins! [#334]
Comment by Anon (published 2005-07-26)
At last! Thank you [#333]
Comment by Patrizia (published 2005-04-20)
Finally what I've been looking for. Simple and clear. Thanks! [#322]
Comment by Gert Cuppens (published 2005-03-12)
This was the page I've been looking for. It gave me the wanted information in a very brief manner, just what I needed. [#312]
Comment by Graham (published 2005-02-06) Suggested link.
Left, Right, Inner and outer joins in MySQL (and joins that don't use any of those words at all - and perhaps not even the word join) seem to be a cause of endless confusion. This page is one of the most visited on our website! "People who visit this page also visit our page on using LEFT JOIN to connect 3 or more tables" and you can get to that page through my suggested link on this comment [#38]
You can Add a comment or ranking or edit your own comments
There are presently 1 new comment(s) pending publication on this page
Average page ranking - 4.9
PH: 01144 1225 708225 • FAX: 01144 1225 707126 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho