Re: Aggregate functions on empty sets in SQL

In article <JAVF7.14841$xS6.20943_at_www.newsranger.com>, Mikito Harakiri says...
>
>The query
>
>SQL> select min(1) from tbl where 1=0;
>
>MIN(1)
>----------
>
>
>returns one row with NULL. 2 questions:
>
>1. Is it SQL standard?
>2. Is it for any reason better than retuning minimal number in the numeric
>domain? (Same for dates, strings and other domains with total ordering).

In order to be consistent you have to return _max_ number in the domain (or positive infinity:-) when you query for tha minimum on empty set.

Surprisingly, 3-valued logic gives consistent answers:

SQL> select min(a) from (
2 select min(1) a from dual where 1=0 3 union
4 select 1 from dual
5 );

MIN(A)

>Every
>aggregate function internally starts a loop with some value (for example, sum
>starts with 0), and that value is the intuitive result of quering the empty set.

Except avg.

>Besiades, in order to find out the min/max numbers in the domain I would have to
>make one less trip to the documnetation;-)

ANSI standard doesn't allow you to do that, because different vendor might return different answer to you. Received on Tue Nov 06 2001 - 21:25:54 CET