RIGHT and FULL JOIN are on the trunk branch of SQLite and will (very likely) appear in the next release. Please grab a copy of the latest pre-release snapshot of SQLite (https://sqlite.org/download.html) and try out the new RIGHT/FULL JOIN support. Report any problems on the forum, or directly to me at drh at sqlite dot org.
Re:D1: Our SQL database
by isoprophlex on Wednesday May 11, 2022 @02:48PM
(#31344159)
This is fantastic news, I'm very glad to hear that this is appearing soon! Thanks!
Re:D1: Our SQL database
by cryptonector on Wednesday May 11, 2022 @02:47PM
(#31344147)
SWEEEEET!
Finally!
Thank you so much for this Mr. Hipp!
EDIT: Don't forget to edit the `omitted.html` page when you ship it!
`FULL OUTER JOIN` is the secret to diff'ing table sources. `MERGE` is just a diff operation + insert/update/delete statements to make the target table more like the source one (or even completely like the source one).
`FULL OUTER JOIN` is essential to implementing `MERGE`. Granted, one could implement `MERGE` without implementing `FULL OUTER JOIN` as a public feature, but that seems silly.
Sadly, the SQLite3 dev team specifically says they will not implement `FULL OUTER JOIN`[0].
Implementing `MERGE`-like updates without `FULL OUTER JOIN` is possible (using two `LEFT OUTER JOIN`s), but it's an O(N log N) operation instead of O(N).
The lack of `FULL OUTER JOIN` is a serious flaw in SQLite3. IMO.