sql - Emulate a full outer join of a nested query in mysql -

mysql doesn't have full outer joins, , know there ways emulate them using right , left joins. however, of suggested methods have encountered require referencing tables joining twice. if using large subquery, there no way reference query twice because mysql not support cte. there other way can structure query doesn't involve copying , pasting large subquery?

so suppose want emulate

select * outer join b using(x) 

with tables

a                      b x     y                x     z ---------              -------- 1     2                1     q 3     3                2     p 6     11               3     r 

so start off, left join creates table

a left join b x   y   z ------------- 1   2   q 3   3   r 6   11  null 

now append null entry table so:

(a left join b) union (select null,null,null) x    y    z ------------- 1    2    q 3    3    r 6    11   null null null null 

we append null column b using same technique

b union (select null, null) x     z --------------------------- 1     q 2     p 3     r null  null 

if right join these 2 tables together, following

((a left join b using(x)) union (select null,null,null)) right join (b union select null,null) using (x) x    y    z1    z2 ------------------- 1    2    q     q null null null  p 3    3    r     r 6    11   null  null null null null  null 

which if ignore column z1 , "null" row, same outer join of , b. remove row, need statement. note based on null not showing in columns merging on.

i'm still working out detail of sql syntax, because mysql complains naming subqueries, sequence of operations theoretically correct, , accesses table once.