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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
