Up to now, we were doing 2-tables full-outer-join using the classic emulation, since SQLite lacks support for that join.
But now we are doing it with 3 tables, and it gets ugly fast IMHO. https://stackoverflow.com/questions/12759087/full-outer-join-in-sqlite-on-4-tables I "think" the reason that join is not historically supported by SQLite is because it only implements nested-loops, while full-outer-joins perform best with merge-join (assuming joining on PK or indexed columns). (I'm not expert here. Better minds, please correct me of course). That, and the fact there's an "easy" work-around for 2 tables. But for 3 or more tables, that "easy" work-around turns into rather awful SQL though... Why can't SQLite itself do the query rewriting itself, of the full-outer-join? The day SQLite gains "native" support for merge-join, the SQL does not have to change and get a speed-boost transparently. And intent is expressed clearly and declaratively, while it gets lost in the ugly work-around "manual rewrites" at the SQL level. And if merge-join nevers arrives, that's fine too, that's an impl detail and an optimization. With SQLite, there's always hope for missing features, even more complex features did arrive eventually, witness CTE and Window Functions (so the "Lite-clan" can please save your breath :)), so isn't it time to support Full-Outer-Join? We're always harping about the declarative nature of SQL, so why here when there's a SQL-standard way to express intent, we have to jump through hoops and emulate it with a "programmatic" recipe that scale very poorly with the number of tables? Sorry for the "impatience" here... I wish SQLite would grow that support, at last. Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
