Does memsql support Full Outer Join?

Question

I wanted to have a full outer join in memsql. Something like

SELECT *
FROM A FULL OUTER JOIN B
ON A.id  = B.id

Is it possible ?

Answer 1

It appears that MemSQL does not have a FULL OUTER JOIN syntax. However, you should be able to simulate a full outer join in MemSQL using a combination of LEFT and RIGHT OUTER JOIN operations:

SELECT * FROM A
LEFT OUTER JOIN B ON A.id = B.id
UNION ALL
SELECT * FROM A
RIGHT OUTER JOIN B on A.id = B.id
WHERE ISNULL(A.id)

                                                

The first SELECT covers the orange area, namely matching records between A and B along with records in A which do not match to anything in B. The second query obtains only records in B which do not match to anything in A. Using UNION ALL instead of UNION ensures that duplicates are not removed.