Mysql Full Outer Join Temporary Tables

advertisements

There are a lot of questions about full-joining in mysql(5.1.36). Of course the solution is the join-union combination.

My problem is that I have two temporary tables like this:

CREATE TEMPORARY TABLE wConfs
(
    idWorker INT,
    confs SMALLINT
) ENGINE=INNODB;

CREATE TEMPORARY TABLE wRejects
(
    idWorker INT,
    rejects SMALLINT
) ENGINE=INNODB;

JOIN-UNION mix cannot be used to full join temporary tables, because it will result in ERROR 1137 (HY000): Can't reopen table.

My question is - what is the simpliest solution with best performance to achieve full join on temporary tables?

EDIT: JOIN-UNION mix:

SELECT wc.idWorker, wc.confs, wr.rejects FROM wConfs wc LEFT JOIN wRejects wr
ON (wr.idWorker = wc.idWorker)
UNION
SELECT wc.idWorker, wc.confs, wr.rejects FROM wConfs wc RIGHT JOIN wRejects wr
ON (wr.idWorker = wc.idWorker);


Try to create third temporary table with distinct idWorker values from two tables, then use it in your JOIN-UNION mix, e.g. -

CREATE TEMPORARY TABLE all_id
  SELECT idWorker FROM wConfs UNION SELECT idWorker FROM wRejects;

SELECT t.idWorker, c.confs, r.rejects FROM all_id t
LEFT JOIN wConfs c
  ON t.idWorker = c.idWorker
LEFT JOIN wRejects r
  ON t.idWorker = r.idWorker;