Joe Celko's SQL for Smarties, 3rd Edition

26.3. A Note on ALL and SELECT DISTINCT

Here is a series of observations about the relationship between the ALL option in set operations and the SELECT DISTINCT options in a query from Beught Gunne.

Given two tables with duplicate values:

CREATE TABLE A (i INTEGER NOT NULL);
INSERT INTO A VALUES (1), (1), (2), (2), (4), (4);
CREATE TABLE B (i INTEGER NOT NULL);
INSERT INTO B VALUES (2), (2), (3), (3);

The UNION and INTERSECT operations have regular behavior in that:

(A UNION B) = SELECT DISTINCT (A UNION ALL B) = ((1), (2), (3))

and

(A INTERSECT B) = SELECT DISTINCT (A INTERSECT ALL B) = (2)

However,

(A EXCEPT B) <> SELECT DISTINCT (A EXCEPT ALL B)

Or, more literally, (1) <> ((1), (2)) for the tables given in the example. Likewise, we have: ...