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@gmx.net       http://yi.org/peter-e/