op 18-11-2015 21:23 schreef Richard Hipp op drh at sqlite.org:
> 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?
>
Does that look like this:
SELECT * FROM
(
SELECT 'added' AS what, a.* FROM a
UNION ALL
SELECT 'deleted', b.* FROM b
)
GROUP BY <all columns from a or b>
HAVING COUNT (*)<>2
;
