CODEWITHSUNDEEP
mysqljointemp-tables
Joe
Joe

Reputation: 2591

Mysql Full Outer Join Temporary Tables

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);

Upvotes: 4

Views: 4972

Answers (2)

Joe
Joe

Reputation: 2591

I've found another way to solve it. I also created 3rd temp table:

DROP TABLE IF EXISTS wResults;
CREATE TEMPORARY TABLE wResults
(
    idWorker INT PRIMARY KEY,
    rejects SMALLINT,
    confs SMALLINT
) ENGINE=INNODB;

Now I Insert rows to it like that:

INSERT INTO wResults(idWorker, confs)
    SELECT idWorker, confs FROM wConfs;

INSERT INTO wResults(idWorker, rejects)
    SELECT wr.idWorker, wr.rejects FROM wRejects wr
    ON DUPLICATE KEY UPDATE rejects = wr.rejects;

It works like a charm. I guess that if there were a lot of data to insert - it would be pretty slow, but my tables are rather small. Thank you all anyway!

Upvotes: 1

Devart
Devart

Reputation: 121922

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;

Upvotes: 5

Related Questions