You are right - my original suggestion does not work. I forgot that the EXCEPT operator does a DISTINCT on the result set of both operands. Your approach works much better.
This could be fixed by updating SQLite to accept the standard SQL syntax where a "set quantifier" can be used with not only a standard query but a compound query.
Meaning:
SELECT <set quantifier> ...
And:
SELECT ... <compound operator>[ <set quantifier>] SELECT ...
Where:
<set quantifier> ::= DISTINCT|ALL <compound operator> ::= UNION|EXCEPT|INTERCEPT
This way, users can specify what behaviour they want, and get flexability. it should be very simple to implement. Since the <set quantifier> is optional, the default behaviour is akin to ALL for regular selects and DISTINCT for compound selects, as SQLite and other databases already do.
See SQL:2003, 7.13 "<query expression>" (p351) for the definition.
A slightly modified version of your suggestion would then produce the desired result, a duplicates list:
SELECT * FROM table EXCEPT ALL SELECT DISTINCT * FROM table;
-- Darren Duncan
