How to replicate FULL JOIN multiple times in MySQL?

Question

I've seen the article on how to do a full join for 2 tables in MySQL. I've also read this for multiple tables, but here's my unique problem.

I have multiple temp tables (result of SQL queries, but shown below as tables for simplicity)

Table1
ID  Value1
1   A
2   B

Table2
ID  Value2
2   C
3   D

Table3
ID  Value3
1   E
3   F

Table4
....

and so on (At-least 10 tables). What I need is this

ID  Value1  Value2  Value3  Value4 ....
1   A               E
2   B       C
3           D       F
4 .......

Trying to do this without a FULL join seems extremely convoluted. What is the simplest way I can achieve this in MySQL?

are you saying you want to get from all these tables without using join? — KP., May 10 '16 at 05:00
this should do the trick: SELECT main.ID, t1.Value1, t2.Value2, t3.Value3, FROM ( SELECT DISTINCT IFNULL(t1.ID, IFNULL(t2.ID, t3.ID)) ID FROM TABLE1 t1, TABLE2 t2, TABLE3 t3) main LEFT JOIN TABLE1 t1 ON main.ID = t1.ID LEFT JOIN TABLE2 t2 ON main.ID = t2.ID LEFT JOIN TABLE3 t3 ON main.ID = t3.ID — Dr. Stitch, May 10 '16 at 05:01
@KP - I am open to using joins, but I can't seem to find an elegant way to implement this without a full outer join feature that MySQL doesn't offer. So I am open to any suggestion. — max_max_mir, May 10 '16 at 05:12
@Dr. Stitch That assumes just three tables - I have multiple tables (at-least 10) and doing this over and over would be problematic. — max_max_mir, May 10 '16 at 05:13
@max_max_mir that is a poor database design, why not just make it one table? and put them as null values in each column? — Dr. Stitch, May 10 '16 at 05:18
@ Dr. Stitch - The Tables are outputs of SQL statements - I tried to simplify the question by showing them as tables instead. — max_max_mir, May 10 '16 at 05:29
@max_max_mir, why derive output from results, you should use a stored procedure here — KP., May 10 '16 at 05:32

score 3 · Accepted Answer · answered May 10 '16 at 05:55

If you want to combine so many tables (query results) without full join, then the simplest way is to generate a full list of ids using union distinct and left join all your tableson that list:

select tid.id, table1.value, ..., tableN.value from
    (select id from table1
     union distinct
     select id from table2
     ...) tid
left join table1 on tid.id=table1.id
...
left join tableN on tid.id=tableN.id

How to replicate FULL JOIN multiple times in MySQL?

1 Answers1