Full outer join

I see that full join is not supported with n1ql. Is a valid workaround to do left join UNION right join ?

MB-50220

single level JOIN you can use right JOIN which rewritten as LEFT JOIN. Instead of UNION, use UNION ALL and project MISSING one from right JOIN.

If you multiple JOINS use LEFT JOIN by manually reversing like below.

SELECT l, r
FROM default AS l
LEFT JOIN default AS r ON ...
WHERE ....
UNION ALL
SELECT d.*
FROM (SELECT l, r
             FROM default AS r
             LEFT JOIN default AS l ON ...
             WHERE ....) AS d
WHERE d.l IS MISSING

Also you can try following you required 7.0+ due to NOT EXCEPT need to be correlated suquery.

SELECT  ..
FROM l
LEFT JOIN r
......
UNION ALL
(SELECT  ...  FROM r
  WHERE ... AND NOT EXCEPT (SELECT RAW 1 FROM l WHERE r.xxxx = l.xxxy LIMIT 1)

I was thinking of single join. This is awesome. Thanks !