Home Accessibility Courses Twitter The Mouth Facebook 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
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
S152 - SQL Primer as Used in MySQL
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [270] NULL in MySQL - (2005-04-06)

S157 - More MySQL commands
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)


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.
4318 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 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).

Comment: "It is a common mistake to think that there is no difference ..."
Visitor Ranking 4.8 (5=excellent, 1=poor)
2 unpublished comments pending on this page

Comment by Ilan (published 2011-08-18) Suggested link.
It is a common mistake to think that there is no difference putting the conditions in the ON clause and the WHERE clause.
well, there is a big difference. The following post explains it with examples:
http://www.mysqldiary.com/mysql-left-join/ [#3973]

Comment by Rahul (published 2011-08-18)
Thanks dear it is very helpful [#3967]

Comment by Rahul (published 2011-08-18)
Thanks dear it is very helpful [#3966]

Comment by Anon (published 2011-03-19)
That's really help! Thanks! [#3907]

Comment by Rajeev Sharma (published 2011-02-18)
Yes, Its really a good start to understand joins.
Good Work [#3883]

Comment by v (published 2011-02-18) Suggested link.
Thanks, I learned here after review of many different reference else ware...

simple but solid, I will use this in my site...

thanks [#3882]

Comment by happy_visitor (published 2011-02-18)
Thanks for this. I've been to a few pages trying to wrap my mind around joins and you finally cleared up the one thing that was confusing me. In less words too. Short and simple. Much appreciation. [#3881]

Comment by Juan (published 2011-02-18)
It's like Plug and Play... [#3872]

Comment by Akhilesh thakur (published 2011-01-16)
IT is very useful. [#3857]

Comment by Anon (published 2011-01-16)

good job today iam very clear about joins . [#3842]

Comment by Anon (published 2011-01-16)
First time I actually see joins explained in a CLEAR way. Great job. [#3832]

Comment by Disha (published 2010-11-04)
thank you so much!! .. example is so clear [#3825]

Comment by Disha (published 2010-11-04)
thank you so much!! .. example is so clear [#3824]

Comment by Demon (published 2010-10-25) Suggested link.
Excellent work guys. There also another good website about the select statement
http://apachejava.blogspot.com/2010/09/select-second-highest-3rd-4th-highest.html

visit it. [#3803]

Comment by Nafees (published 2010-10-08)
I have got what i have been looking for...very well explained...thanks [#3801]

Comment by Jaime (published 2010-10-08)
Excelent [#3795]

Comment by varunkumar (published 2010-10-08)
Its very good data which makes things clear about LEFT & RIGHT Join..But only one thing that reduced my rating from 5 to 4 is missing of enough detail regarding INNER & OUTER Joins. [#3784]

Comment by rajendra (published 2010-10-08)
good example [#3782]

Comment by Om Prakash Yadav (published 2010-10-08)
Excellent [#3775]

Comment by Abhiranjan Jagannath (published 2010-09-11)
Simple and clear. Thank you! [#3772]

Comment by Dhawal (published 2010-09-11)
thanks a lot sir, for posting this article....
you does a great job .... [#3771]

Comment by manikandan (published 2010-09-11)
Excellent example.......
Good Job...
Thanks a lot.....


[#3770]

Comment by Anton (published 2010-08-21) Suggested link.
Excellent - thanks very much, very very useful indeed. [#3741]

Comment by Anon (published 2010-07-15)
Thanks really helped [#3653]

Comment by Rahul Dhawan (published 2010-07-15)
This is the best example that i have ever read, now joins are really clear in my mind,,thank you very much [#3650]

Comment by sachin negi (published 2010-05-18)
hey!!
thanks , for the information.
now LEFT AND RIGHT join is clear in my mind. this is really very helpful .
[#3565]

Comment by sachin negi (published 2010-05-18)
hey!!
thanks , for the information.
now LEFT AND RIGHT join is clear in my mind. this is really very helpful .
[#3564]

Comment by Anon (published 2010-05-18)
great explanation, thanks [#3541]

Comment by Mruttunjaya (published 2010-04-14)
Great job.. helped me a lot [#3537]

Comment by jayaraj (published 2010-04-14)
i can understand easily about join

thank u [#3535]

Comment by jayaraj (published 2010-04-14)
i can understand easily about join

thank u [#3534]

Comment by peter (published 2010-04-14)
Excellent material and precisely presented.
Thanks
Peter [#3533]

Comment by spd (published 2010-05-18)
great explanation, all clear from the first read. [#3491]

Comment by valsj (published 2010-03-21)
thank you very much. Its easier to understand [#3489]

Comment by sagar (published 2010-03-21)
good simple but strong [#3485]

Comment by Linu Varghese (published 2010-03-09)
Nice Explanation, So Simple TO UnderStand,, Thanks [#3481]

Comment by Andy (published 2010-03-09) Suggested link.
Oh, thank you, thank you. I've been struggling with a left join for... like forever, and your clear example just cracked it! [#3480]

Comment by Raj (published 2010-02-08)
This is very nice example to understanding the concept of join query. [#3465]

Comment by Anon (published 2010-01-24) Suggested link.
Great tutorial! [#3459]

Comment by Jack (published 2010-01-18)
Awesome. Simple, crystal clear and to the point. [#3453]

Comment by Anon (published 2010-01-18)
Great explanation of joins. Thanks a lot! [#3452]

Comment by Yogesh (published 2010-01-18)
this is very simple and it cleared all doubts about joins like difference left and left outer join. It's simply gr8 example. [#3451]

Comment by Zheka82 (published 2009-12-28)
Thank you much! :) [#3450]

Comment by ARUL, COIMBATORE (published 2009-12-28)
REALLY A FANTASTIC ARTICLE [#3449]

Comment by Anon (published 2009-12-28)
Very good explainaiton. Simple and to the point.. [#3447]

Comment by Sunny (published 2009-12-28)
I really got helped.
These are pure basics of joining which are base of database. [#3445]

Comment by Steffen (published 2009-12-28)
perfect explanation. I can't believe it's that easy. Thank you! [#3444]

Comment by Geo (published 2009-12-28)
excellent explanation!! [#3443]

Comment by Anon (published 2009-12-28)
Thanks a lot for the info.Really recommandable job done. [#3441]

Comment by Chaitra Yale (published 2009-10-27)
Very nice examples and explanation of Mysql joins.
"An INNER JOIN does a full join, just like the first example"
regarding your above statement I'm not sure if inner join is same as full join. If you do a full join on the 2 tables the result set will have all the rows from the first table and all the rows from the second table. If there are rows in "demo_people" that do not have matches in "demo_property", or if there are rows in "demo_property" that do not have matches in "demo_people", those rows will be listed as well. However, I also think that the missing feature of FULL OUTER JOIN is a real drawback to MySQL. but from MySQL 4 on you can use a workaround using the UNION. for example if you take the left and right joins examples above and do a UNION on the 2 select statements you will get a FULL JOIN.

mysql> select name, phone, selling from demo_people left join demo_property on demo_people.pid = demo_property.pid UNION select name, phone, selling
from demo_people right join demo_property on demo_people.pid = demo_property.pid;

Thanks

Chaitra Yale

[#3439]

Comment by saptarshi (published 2009-10-27)
Very commendable work . Excellent. Should there be a little more discussion on outer join and inner join, it would had been better . [#3438]

Comment by Sean (published 2009-10-27) Suggested link.
Exactly what I was looking for! Saved me pulling the rest of my hair out. Many many thanks! [#3437]

Comment by Anon (published 2009-10-27)
really very good example thanks [#3433]

Comment by Anon (published 2009-10-27)
Good and simple. Appreciate it! [#3432]

Comment by Wedhus Brazen (published 2009-10-27)
thx for the explanations [#3431]

Comment by Paulraja (published 2009-10-27)
nice explanation ... its really useful for myself [#3430]

Comment by Rita (published 2009-10-27)
very good [#3428]

Comment by Anon (published 2009-10-27)
excellent article. [#3425]

Comment by Swamy (published 2009-06-30)
very useful example.
Thank you [#3422]

Comment by Nadia (published 2009-06-30)
you have made me clear the concepts of join which i never understood during database studies ... thanks a lot and you have saved me from many embarrasing moments to come [#3421]

Comment by Anon (published 2009-06-16)
This is great! [#3416]

Comment by mike (published 2009-06-16) Suggested link.
now i know how to use it :)
thanks a lot [#3414]

Comment by Anon (published 2009-06-16)
i appreciate the way it has been specified the diference between the left join and right join [#3413]

Comment by Sev (published 2009-06-16)
You covered most of the stuff correctly, but you messed up around the bottom. You oversimplify techniques that could cause someone to confuse things going forward. [#3412]

Comment by Hari K T (published 2009-06-16) Suggested link.
Good [#3411]

Comment by sahil (published 2009-06-02)
great it was an easy explanation [#3410]

Comment by Anon (published 2009-06-02)
Thank you

Laxman Kumar [#3407]

Comment by rea (published 2009-06-02)
this is a great tutorial. It's not that complicated but very useful. Amazing. [#3406]

Comment by Anon (published 2009-06-02)
Well.

Thanks for the help. It is a real color of life. [#3405]

Comment by zhimin (published 2009-06-02)
very useful. thank you! [#3404]

Comment by Alan Haggai Alavi (published 2009-06-02) Suggested link.
Simple and nice example. Thank you. [#3403]

Comment by manu (published 2009-06-02)
simple but effective .thanx a lot ,with a single glance i understood about joins.. [#3402]

Comment by Manju (published 2009-06-02)
Can u give any example where i can retrieve all the records of a table "users" where there are no corresponding records in other table "user_role" [#3401]

Comment by Ermal Kristo (published 2009-06-02)
Simply the best(Explanation) [#3400]

Comment by Ankur (published 2009-06-02)
good tips are given.it can be more better if there is an example that consider all column in both the table [#3398]

Comment by Almo! (published 2009-06-02)
Great explanation. Thanks! [#3396]

Comment by CSP (published 2009-06-02)
Thnx! very simple and easy to understand. [#3394]

Comment by Suchi (published 2009-06-02)
Great and powerful article that explains joins in a simple but easy manner. [#3393]

Comment by Pavan (published 2009-06-02)
A Excellent Explanation of Joins. Thanks to teh Author [#3391]

Comment by Eddie (published 2009-06-02)
Clear! brilliant! [#3390]

Comment by Matt (published 2009-06-02) Suggested link.
I believe your comment, "An INNER JOIN does a full join" is incorrect. FULL JOIN is short for FULL OUTER JOIN which is not the same as INNER JOIN. [#3389]

Comment by RAjesh (published 2009-06-02)
There are no words also in english to praise. It was simply superb and extraordinary. [#3388]

Comment by Anon (published 2008-12-31)
I'm glad it's not just me that has struggled understanding join vs left vs right join, even more glad you have written such a simple explanation for us all! :) [#3385]

Comment by Narendra (published 2008-12-31)
But my question is "where is the use of left and right join .........any scenario" [#3383]

Comment by Anon (published 2008-12-31)
Very simple explanation , i got cleared my confusions regarding joins [#3381]

Comment by Chris Hayes (published 2008-11-09)
This is the first article I have read that explains joins clearly! Short and concise, great article!

-Chris [#3380]

Comment by robin (published 2008-11-09)
nice page...i got joins in moments... [#3379]

Comment by Ranjeet (published 2008-11-09)
GR8 man...

Thanks a Ton [#3378]

Comment by Boka (published 2008-11-09) Suggested link.
thankx
[#3377]

Comment by Ehsan (published 2008-11-09)
Thank u for this article, it help me to understand joins completly [#3376]

Comment by Ehsan (published 2008-11-09)
Thank u for this article, it help me to understand joins completly [#3375]

Comment by Anon (published 2008-11-09)
extremely helpful! I'm bookmarking this page - thanks! [#3374]

Comment by Aftvin (published 2008-11-09)
Thank you! [#3372]

Comment by Swetha (published 2008-11-09)
very nicely and simply explained! thank u! [#3369]

Comment by ME (published 2008-11-09)
Really great!!! [#3368]

Comment by Vamshi (published 2008-07-09)
wonderful example! [#3367]

Comment by Santosh Thapliyal (published 2008-11-09)
i m thank for your help for me understand this topik [#3366]

Comment by erastus (published 2008-07-02)
it's until now i have gotten it! [#3365]

Comment by Anon (published 2008-06-25)
This should be in the MySQL comments section of the handbook. I knew joins should be easy, but most of the examples I saw were more complicated than they need to be. Please add this to the mysql handbook comments. [#3364]

Comment by phpnewbie (published 2008-11-09)
'tis very clear and helpful! thanks a lot! =) [#3363]

Comment by Anon (published 2008-06-05)
Perfect! Thanks a lot mate, this helped me to survive in school. [#3362]

Comment by aditya (published 2008-11-09)
it was good simple , and exactly what was needed [#3360]

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 2 new comment(s) pending publication on this page

Average page ranking - 4.8

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

PAGE: http://www.wellho.net/mouth/158_MySQ ... -JOIN.html • PAGE BUILT: Thu Sep 18 15:30:25 2014 • BUILD SYSTEM: WomanWithCat