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;
In other words, the SQL analog of a straightforward diff(1) of two
[sorted] files. I think this is the canonical way to do this in SQL,
though there are variants.
Now, diff(1) (i.e., the Unix command) can do this very efficiently
because it just moves a "cursor" in both files looking for different
records (lines).
SQLite3, on the other hand, does four table scans and further scans two
sub-queries' result row-sets. 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. I'd certainly appreciate such an
optimization. (No, Postgres 9.4.4 doesn't seem to optimize this
substantially better either.)
A difference UDF would be one way to handle this. Table-valued
functions are half the battle, but one still needs table-valued
arguments to UDFs to make it clean looking:
-- Outputs two-column rows where the first column denotes addition vs
-- deletion and the second is a row-valued column with the result
-- columns of the corresponding row in the old or new table source:
SELECT diff((SELECT ...), (SELECT ...));
Using "eval" and JSON one could define a diff() UDF that takes two
strings, compile the two strings as SQL statements and execute them,
stepping over the results of each just like diff(1) would:
-- Outputs JSON arrays of addition/deletion marker and column array or
-- object.
SELECT diff('SELECT ...', 'SELECT ...');
and/or
SELECT diff('old_view_or_table', 'new_view_or_table');
Perhaps SQL could grow a DIFFERENCE operator for this, though there are
syntactic/semantic issues to deal with. Maybe:
SELECT d.diff, o.c0, o.c1, .., o.cN
FROM old_source o
DIFFERENCE (/*deletion*/ '<', '>' /*addition*/)) d
SELECT d.diff, n.c0, n.c1, .., n.cN
FROM new_source n;
One might get a comm(1) equivalent too:
SELECT d.diff, o.c0, o.c1, .., o.cN
FROM old_source o
DIFFERENCE (/*deletion*/ '<', '>' /*addition*/), '=' /*same*/) d
SELECT d.diff, n.c0, n.c1, .., n.cN
FROM new_source n;
Nico
--
