SQL Kiwi (11/22/2011)
L' Eomot Inversé (11/22/2011)
Nice question, but only works for non-empty tables.
The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.
Strictly, non-empty sets rather than tables. More confusing examples, for fun:
Yes, you are right, I should have said sets (or preferably bags) not tables.
And your four queries are a nice illustration of where things in SQL are wrong, and where they are right.
Of course I think the first in your collection is good mathematics and second is bad mathematics. The third is pretty straightforward and seems sensible (no error of mathematics there, sensible use of NULL.
The fourth is more interesting - I had to think about it for a bit - but I feel it is correct in mathematical terms. That's because I guess that that "OVER ()" means "partition into as many partitions as there are (not necessarily distinct) rows with each partition containing all the rows" (actually the exact meaning doesn't matter here - just that it's asking for some partition to be done - but it might matter in other queries, so I guess I should go and check that whether it really does mean that) so we are asking for a rowset containing the maxima of an empty set of rowsets (because there are no rows in the rowset being partitioned) so the result is an empty rowset.