Re: strange behaviour

Mathieu Arnold writes:

> sympa=> select count(*) from subscriber_table;
> count
> -------
> 14029
> sympa=> select count(*) from subscriber_table where bounce_subscriber = NULL;
> count
> -------
> 14024
> sympa=> select count(*) from subscriber_table where bounce_subscriber <> NULL;
> count
> -------
> 0

'anything <> NULL' can never be true, because 'anything {operator} NULL'
is always NULL, which means false in a WHERE clause. The reason that
'anything = NULL' works is that it is explicitly handled to work around MS
Access breakage. What you want to use is 'xxx IS NULL' and 'xxx IS NOT
NULL'.

> sympa=> select count(*) from subscriber_table where bounce_subscriber = '';
> count
> -------
> 0
> sympa=> select count(*) from subscriber_table where bounce_subscriber <> '';
> count
> -------
> 5
>
> I was thinking that "= NULL" and "<> NULL" were oposite and that "<> NULL"
> would give me the result i had with "<> ''" or am I mistaken ?

NULL and '' are not the same, in spite of anything Oracle is trying to
tell you.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/