List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 5 2005 3:06pm
Subject:Re: FULL OUTER JOIN
View as plain text  
Vincent.Badier@stripped 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 
Thread
FULL OUTER JOINVincent.Badier4 Apr
  • Re: FULL OUTER JOINSGreen5 Apr