> How many times do you *really* want to get the "not known" answer here > instead > of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?
All the time. If I would want the answer 'fisk', I would store '' instead of NULL... your problem is that Oracle treats NULL as '' (empty string), so even if you insert an empty string it will end up as NULL, that's why they HAVE TO give the result you say it's more logical. > That's like saying: SELECT sum(field) should return NULL(value not known) if > some of the tuples are NULL, which is definitly not what you want. And it would really return null, if aggregates wouldn't ignore the NULL values altogether... the null values are skipped before they get into the summing. The same happens with count, if you specify a column it will only count the ones which are not null: cnagy=# create table test_null(a integer); CREATE TABLE cnagy=# insert into test_null values (1); INSERT 0 1 cnagy=# insert into test_null values (null); INSERT 0 1 cnagy=# insert into test_null values (2); INSERT 0 1 cnagy=# select sum(a) from test_null; sum ----- 3 (1 row) cnagy=# select count(a) from test_null; count ------- 2 (1 row) But: cnagy=# select (1 + 2 + null) is null; ?column? ---------- t (1 row) Cheers, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly