If you want to do a full outer join in RethinkDB you can, although it will be noticeably slower than other queries. If this seems strange, this is a somewhat common use case in reporting or scraping, where you may have a subset of all available fields at any point.
The way to execute this correctly is to separate left and right joins, then combine and de-duplicate the results.
In this case, we’ll do a full outer join of a list of users and audit history, which shows audit history without users, and users without history:
r.db('test')
.table('users')
.outerJoin(
r.table('user_actions'),
(user, action) =>
action('user_id').eq(user('id')))
.zip()
.union(
r.db('test')
.table('user_actions')
.outerJoin(
r.table('users'),
(action, user) =>
action('user_id').eq(user('id'))
).zip()
)
Note that you need to apply “.zip” to both sides (or do it at the end). The two sides of the union are here also not at the same level, as they would be in SQL (the second is nested), so I suspect that there is an implicit ordering here, where the two sides of the union could be re-arranged in a SQL database.
Another interesting point here is that in SQL, unions are sensitive to column ordering, but RethinkDB matches things on names in JSON, so you no longer have to deal with that irritation.