Full OUTER JOIN OR UNION in mysql

Question

I've two temporary table say temp1 and temp2(which are created in runtime). I need to perform "FULL outer JOIN" to get the data from both the table. But I got error

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'FULL OUTER JOIN cashExtTemp t2 
ON t1.code = t2.code'

SELECT * FROM cashIntTemp t1 FULL OUTER JOIN cashExtTemp t2 ON t1.code = t2.code

And came to know that FULL OUTER JOIN is NOT possible in MySQL from this link Full Outer Join in MySQL and tried to implement UNION as given in the link. Since I'm using temporary table it doesn't work and i got the error as

Error Number: 1137 
Can't reopen table: 't1'

This is my UNION Query

SELECT * FROM cashIntTemp t1 LEFT JOIN cashExtTemp t2 ON t1.code = t2.code
UNION
SELECT * FROM cashIntTemp t1 RIGHT JOIN cashExtTemp t2 ON t1.code = t2.code

The data in the table would be like this TABLE: cashIntTemp

code     qty     date
P001     100    2013-11-29 
P003     200    2013-11-30
P005     600    2013-11-30

The data in the table would be like this TABLE: cashIntTemp

code     qty     date
P001     110    2013-11-29 
P002     250    2013-12-01
P005     650    2013-12-01

I need a query to get all the data from both the tables.

I need the result in this format

code     qty     date        code    qty     date
P001     100    2013-11-29   P001    110   2013-11-29
P002     250    2013-12-01
P003     200    2013-11-29   
P005     600    2013-11-29   P005    650   2013-11-29

So please help me on this. Thanks in advance.

select * from (select * from tbl where someclause) t1 left join ANOTHERTABLE s ON (s.id=t1.id) — sumit, Dec 02 '13 at 03:34

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

edit: totally missed the part about temporary tables, don't know if this will work...

sqlfiddle: http://sqlfiddle.com/#!2/3bf61/10

I'm not getting 100% of what you're throwing down, let me try to rephrase: You have 2 tables: cashIntTemp and cashExtTemp. In a single query, you want to fetch all rows from both tables. The intended result is:

CODE    QTY DATE
P001    100 November, 29 2013 00:00:00+0000
P002    250 December, 01 2013 00:00:00+0000
P003    200 November, 30 2013 00:00:00+0000
P005    650 December, 01 2013 00:00:00+0000
P005    600 November, 30 2013 00:00:00+0000

A UNION will provide what you want, again per @Ryan, as long as they are different tables. Part of your question references two different tables, while the other part references the same table.

Shouldn't the following be what you're after?

SELECT * FROM `cashIntTemp` as `t1`
UNION
SELECT * FROM `cashExtTemp` as `t2`
ORDER BY `code`

Or, if you don't want to filter duplicate rows, use UNION ALL: http://sqlfiddle.com/#!2/3bf61/11

Query:

SELECT * FROM `cashIntTemp` as `t1`
UNION ALL
SELECT * FROM `cashExtTemp` as `t2`
ORDER BY `code`

Result:

CODE    QTY DATE
P001    100 November, 29 2013 00:00:00+0000
P001    100 November, 29 2013 00:00:00+0000
P002    250 December, 01 2013 00:00:00+0000
P003    200 November, 30 2013 00:00:00+0000
P005    650 December, 01 2013 00:00:00+0000
P005    600 November, 30 2013 00:00:00+0000

edited May 23 '17 at 12:28

Community

1
1

answered Dec 02 '13 at 03:37

zamnuts

9,492
3
39
46

Sorry, i forgot to mention the required result in the question. Now I've added it. – saran Dec 02 '13 at 04:00
@saran, do you have multiple temporary tables, or just one? what are they called? you reference `cashIntTemp` twice with data: "The data in the table would be like this TABLE: cashIntTemp" – zamnuts Dec 02 '13 at 04:06
I have two temporary tables, "cashIntTemp" and "cashExtTemp". The qty will differ in both the tables. – saran Dec 02 '13 at 04:08
@saran, no matter how hard I try, I can't seem to get a `FULL OUTER JOIN`-like single query with *temporary* tables. I can only see it happening in two, in which case, you might as well query the db twice on diff tables. – zamnuts Dec 02 '13 at 04:42
@saran This is as close as i've gotten, but it is ugly and really isn't the same as a full outer join, it just concatenates tables while isolating their columns: `SELECT t1.code as t1code,qty as t1qty,date as t1date,null as t2code,null as t2qty,null as t2date FROM cashIntTemp as t1 UNION SELECT null as t1code,null as t1qty,null as t1date,t2.code as t2code,qty as t2qty,date as t2date FROM cashExtTemp as t2;` – zamnuts Dec 02 '13 at 04:56

score 0 · Answer 2 · answered Dec 02 '13 at 03:09

In MySql, you can't access a single temp table multiple times in the same query. You're emulating full outer join with a left join unioned to a right join, correct?

You'll have to insert your result set for the left join and your result set for the right join in to a separate temp table in separate queries (ie, not using a union).

Make sense?

Full OUTER JOIN OR UNION in mysql

2 Answers2