Two tables union

Question

I need to join two tables, example below:

table 1

K a b c
P x x x
P x x x
P x x x

table 2

K a b c 
P x x x
P x x x
P x x x

Final table

K a b c d e f
P x x x . . .
P x x x . . .
P x x x . . .
P . . . x x x
P . . . x x x
P . . . x x x

All the unions I've tried don't get my result properly.

Thanks!

You are searching for [`OUTER UNION`](https://stackoverflow.com/a/52524364/5070879) — Lukasz Szozda, Jun 12 '19 at 21:02
Possible duplicate of [How to do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql) — clinomaniac, Jun 12 '19 at 21:36
Also you should post some of the queries you have tried and outline whst was wrong with them. — GMc, Jun 12 '19 at 22:16
I see you are new in stack overflow, You should check all answers and mark as correct answer whatever solve your problem and you think answered you completely. — Engr Syed Rowshan Ali, Jun 12 '19 at 22:31

score 1 · Answer 1 · answered Jun 12 '19 at 21:01

1

The simplest way, in my opinion, is to use full join:

select t1.*, t2.*
from table1 t1 full join
     table2 t2
     on 1 = 0;   -- never true

answered Jun 12 '19 at 21:01

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 12 '19 at 22:07

You may try other answers also, This one particularly answer your question and is faster than join. Because there is no where condition

SELECT t1.p, t1.a, t1.b, t1.c, '0' d, '0' e, '0' f
FROM table1 t1 
UNION ALL
SELECT t2.p, '0' a, '0' b, '0' c, t2.a d, t2.b e, t2.c f 
FROM table2 t2

Two tables union

2 Answers2