Re: Rewrite Full Outer Join into Subquery

[Rick James]

> can the query be written as subquery or as RIGHT..UNION DISTINCT..LEFT as you suggested and how?

A syntax lesson:

( SELECT ... )
UNION [ ALL | DISTINCT ]
( SELECT ... )
UNION [ ALL | DISTINCT ]
( SELECT ... )
UNION [ ALL | DISTINCT ]
( SELECT ... )
UNION [ ALL | DISTINCT ]
( SELECT ... )
[ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ] [ LIMIT ... ];

Each of the inner selects can be RIGHT/LEFT JOIN, etc, and each can have GROUP/HAVING/ORDER/LIMIT.

The final (and optional) GROUP/HAVING/ORDER/LIMIT clauses apply after doing the UNION.

In your case:
( SELECT ... RIGHT JOIN ... )
UNION DISTINCT
( SELECT ... LEFT JOIN ... );

To carry things a step further... Wherever there can be a SELECT statement, you can have UNION, but you should (must?) be sure to add parentheses.
Also, a "table" in FROM or JOIN can be a parenthesized subquery. It also must have an "alias".
So, this mess should be possible:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS foo
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS bar ON foo.x = bar.x
WHERE ... etc;

This is another way a subquery can be done:
SELECT a, b, c, d,
( SELECT ... ) AS e, f, g, h
FROM ...
That subquery must return exactly one value. It is usually a "correlated subquery";

This also works, again with a single-valued subquery. It is usually horribly inefficient:
SELECT ... WHERE x = ( SELECT ... )
It usually can/should be turned into a JOIN.