List:General Discussion« Previous MessageNext Message »
From:Dotan Cohen Date:October 20 2011 12:03am
Subject:Re: What is wrong with this outer join?
View as plain text  
On Wed, Oct 19, 2011 at 21:10, Shawn Green (MySQL)
<shawn.l.green@stripped> 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
Thread
What is wrong with this outer join?Dotan Cohen19 Oct
  • Re: What is wrong with this outer join?MySQL)19 Oct
    • Re: What is wrong with this outer join?Dotan Cohen19 Oct
      • Re: What is wrong with this outer join?MySQL)19 Oct
        • Re: What is wrong with this outer join?Dotan Cohen20 Oct
          • Re: What is wrong with this outer join?MySQL)20 Oct
            • Re: What is wrong with this outer join?Peter Brawley20 Oct
            • Re: What is wrong with this outer join?Dotan Cohen20 Oct
Re: What is wrong with this outer join?Dotan Cohen19 Oct
  • Re: What is wrong with this outer join?hsv20 Oct