List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 19 2011 7:10pm
Subject:Re: What is wrong with this outer join?
View as plain text  
On 10/19/2011 13:19, Dotan Cohen wrote:
> ...
>
> Thank you Shawn! I see that I am getting support right from the top!
>
> So far as I understand, an outer join should return all matched and
> unmatched rows (essentially all rows) from both tables. So it is not
> clear to me what is the difference between a right outer join and a
> left outer join, and how they differ from a regular outer join. But
> don't answer that, I'll google it and post back for the fine archives.
>

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
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