On 11/18/15, Nico Williams <nico at cryptonector.com> wrote: > Consider two tables with the same columns and primary keys, and then > consider this query: > > SELECT 'added., a.* FROM > (SELECT a.* FROM a EXCEPT SELECT b.* FROM b) a > UNION ALL > SELECT 'deleted', d.* FROM > (SELECT b.* FROM b EXCEPT SELECT a.* FROM a) d; > > If only the optimizer could recognize > this pattern, then it could surely do as well as diff(1). This sort of > query strikes me as a common enough kind of query that implementing such > an optimization might be worthwhile, though the SQLite3 team will have > better knowledge of that. (No, Postgres 9.4.4 doesn't seem to optimize this > substantially better either.) >
If it's so common, why are you the first to request it :-) And, yeah, according to the WWPD principal, if Postgres doesn't do it, we probably won't be in a big hurry to do it either... Note that you can get efficient performance by tweaking the query slightly. The "sqldiff.exe" command-line utility has an (undocumented and unsupported) "--debug 2" option that will show you the SQL that it uses to do a diff between two tables. Maybe look at that and tweak it for your use? -- D. Richard Hipp drh at sqlite.org
