On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
<[email protected]> 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/[email protected]
