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/