On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL) <shawn.l.gr...@oracle.com> wrote: > What you are describing is a FULL OUTER JOIN. This is not supported, yet, in > MySQL. Â We only support INNER, NATURAL, LEFT, and RIGHT. > > To simulate a FULL OUTER JOIN, you need to construct a UNION of a LEFT and a > RIGHT like this: > ( > SELECT ... > FROM basetable > LEFT JOIN jointable > Â ON basetable.PKID = jointable.base_id > .... > ) UNION ALL( > SELECT ... > FROM basetable > RIGHT JOIN JOINtable > Â ON basetable.PKID = jointable.base_id > ... > WHERE basetable.PKID is NULL > ... > ) > > The first half of the UNION finds all rows in basetable plus any rows where > the jointable matches. The second half identifies only rows in jointable > that have no match with a row in basetable. > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. > Office: Blountville, TN >
Thank you Shawn. I very much appreciate your help, and I also appreciate your employer's initiative to have such a position monitoring the mailing list. Is that an Oracle-created position, or did it exist at Sun as well? If I'm already talking with the MySQL Principal Technical Support Engineer then I have to suggest that the MySQL manual include more example code. I'm a read-the-manual kind of guy and the C# / PHP manuals are usually enough to get me unstuck. The MySQL and Java (only mentioned as it is another Sun/Oracle product) manuals usually do not provide code examples and I must google for them from unreliable blogs and forum postings. I personally find concise code examples much more intuitive and informative than full-format [{(someOption | anotherOption), somethingHere} rarelyUsedFeature] which I might or might not mentally parse. I can gladly make more specific suggestions if Oracle sees the idea as actionable. I mention this as constructive criticism, take no offense! I'm only at the beginning of my career and I don't claim to have the expertise or experience to tell Oracle how to run their show, I only voice my concern as a consumer of the product and one with an interest in keeping the product and technology viable. I have nothing but appreciation to Oracle for continuing to develop Java, MySQL and for having the good sense to pass OOo onto the Apache foundation. Thank you. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org