List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 5 2005 3:06pm
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 
> columns, while UNION will make double tuple if one column is not the 
> I also would like to avoid temporary table if possible, since the query 
> 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
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.


Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 
FULL OUTER JOINVincent.Badier4 Apr
  • Re: FULL OUTER JOINSGreen5 Apr