[SOLVED] Are full or outer joins directly possible in Couchbase?

Are full or outer joins directly possible in Couchbase?

Are full or outer joins directly possible in Couchbase, without doing any extra operations?

Solution

A LEFT JOIN is the same thing as a LEFT OUTER JOIN (and likewise with RIGHT JOIN/RIGHT OUTER JOIN). So, yes Couchbase's SQL++ supports that.

FULL OUTER JOIN is not currently supported in Couchbase's SQL++ implementation (this is currently only indicated in the SQL++ for Analytics portion of the docs, so I plan to submit a ticket to get that corrected). If it's something you really need, you can simulate a full outer join by UNION'ing a left join and a right join together. E.g.

SELECT A.id, A.column1, B.column2
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id

UNION

SELECT A.id, A.column1, B.column2
FROM TableA A
RIGHT JOIN TableB B ON A.id = B.id;

Update: Here's the ticket I opened with the docs team: DOC-12300