How to mimic a full outer join using mysql views?

Question

I have all kinds of views in mysql with employee data. I have tried to use methods like this -https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but they simply don't work for views.

I end up getting more of a LEFT JOIN as a result. Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.

• ypercubeᵀᴹ: Full joining 2 views is no different than full joining 2 tables. Shows us what you tried.
• LOSTinDB: @ypercube - I did exactly what was in the link. I got all of the entries that weren't matches. I have tried unions and group bys but I have to run them over several views to get them to work. I can get it working but not in one view statement. I want to know how I can take viewA and add viewB (and ViewC and ViewD...) where there are no duplicate ids.
• ypercubeᵀᴹ: Did you try the answer there with 3 views/tables? I think that answer is wrong when it comes to 3+ tables. Edit the question and add the SHOW CREATE VIEW viewname; output.

Answer 1

Simulation of FULL OUTER JOIN:

( SELECT ... FROM a LEFT JOIN b ON ... ) -- Intersection, plus rest of b
UNION ALL -- There will be no overlap; ALL is faster
( SELECT ... FROM b LEFT JOIN a ON ... WHERE a.id IS NULL ) -- Just rest of a

(The link given is less efficient because it gets the Intersection twice, then dedups by doing UNION DISTINCT.)

If you need a FULL OUTER JOIN between more than 2 tables, then it gets messier. (But you did not ask for that.)