--- In firebird-support@yahoogroups.com, "adsieben" <adostal@g...> wrote:

>
> Hi,
>
> select ID, SENDER_ID
> from T_BILL
> where SENDER_ID not in ( select id from T_CONTACT )
>
> "SENDER_ID" is set 'NULL' in every dataset.
> Oracle returns zero datasets, firebird returns the whole table.
>
> As I am not a sql guru, I don't know if this behavior is defined in
> sql, but I find it interesant, that the 2 databases act different.
>

Lets take a look at your query and the definition of NULL.

NULL means undefined / unknown / not applicable / not used / unspecified.

Your subselect returns a set of values, assume (1,2,3) for simplicity.
Your T_BILL table contains NULL for each sender.

select ID, SENDER_ID
from T_BILL
where SENDER_ID not in (1,2,3)

which is for each record
where NULL not in (1,2,3)

which is
where <unknown value> not in (1,2,3)

which is of course anyones guess.

The common mistake here is that people treat NULL as just another
value that can be in the field. But NULL is not a value, it is a state.

where NULL = NULL would return true if it were a value, but because it
is a state, by definition when you compare these fields it is
where <unknown value> = <unknown value>, which may be true or false,
but is treated as false.

Here is a helpful reference:
http://www.firebirdsql.org/manual/nullguide.html

In this particular case, I think Oracle is technically returning the
wrong information for the query. Your subject title (when comparing
with "NULL") is presuming null is some value that can be compared.

The question then becomes which resultset were you expecting? If you
were expecting. Firebird 1.5 and higher contains the Coalesce function
which is really useful when dealing with fields that may have a null
state in some records.

Adam