> create table t(x int); > create table u(x int); > > insert into t values (1), (2), (2), (3), (3); > insert into u values (1), (2); > > select * from t except all select * from u;
> x > --- > 3 > 3 > 2 > (3 rows)
I find this pretty odd behavior. Is this not an outright bug? I haven't read the SQL standard on this matter[*], but if they define EXCEPT ALL to work like this, then it seems pretty useless. (Maybe they just didn't intend EXCEPT ALL to be useful?) If this is indeed the case, maybe we should amend the docs not only to explain this behavior but also to warn against the construct.
I think that's the way it is defined (but I agree, I don't remember ever seeing a use for it)
[*] I didn't try terribly hard, but couldn't actually find where the behavior is defined. What I have on hand is a draft of SQL:2011 where this appears to be defined in 7.13 <query expression> but I was unable to find the rules for set operations. It refers to 9.12 Grouping operations but that defines conformance rules only. --
I may have a different version but I see:
iii) T contains the following rows: 1) Let R be a row that is a duplicate of some row in ET1 or of some row in ET2 or both. Let m be the number of duplicates of R in ET1 and let n be the number of duplicates of R in ET2, where m ≥ 0 and n ≥ 0. 2) If DISTINCT is specified or implicit, then .... ...
3) If ALL is specified, then Case: A) If UNION is specified, then the number of duplicates of R that T contains is (m + n). B) If EXCEPT is specified, then the number of duplicates of R that T contains is the maximum of (m – n) and 0 (zero). C) If INTERSECT is specified, then the number of duplicates of R that T contains is the minimum of m and n.
