[EMAIL PROTECTED] wrote on 04/04/2005 01:14:23 PM:
> Hello all,
>
> mysql 4.0.20
>
> I'd like to know how one can do a "full outer join".
> I've read some workaround with a UNION, but i need the join only on a
few
> columns, while UNION will make double tuple if one column is not the
same.
>
> I also would like to avoid temporary table if possible, since the query
is
> on many millions of rows, and i saw performances suffer when working on
> multiple temporary tables.
>
> If anyone has an idea, he would be great.
>
> Thanks you
> Vincent
>
>
>
Sorry Vincent,
The only way I can think to make it work is through a UNION. I don't fully
trust RIGHT JOINS (on any system) so I use two LEFT JOINs but flip the
order on the second table. Basically this is what the engine has to do to
compute a FULL OUTER JOIN anyway but you have to write the code externally
instead of it being encapsulated within the query engine.7
(
SELECT <column list>
FROM table a
LEFT JOIN table b
ON a.keycol = b.keycol
) UNION (
SELECT <column list>
FROM table b
LEFT JOIN table a
ON b.keycol = a.keycol
WHERE a.keycol is null
)
The UNION will only return the columns you ask it to. This method also
avoids manually creating a temporary table. So long as you do not say
UNION ALL, you won't get any duplicate rows.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
