nulls and the select not counting nulls

[Stephen Ray Badgett]

We have a table called trans and in it we have column SIG that allows nulls, so why if SEG is null it is not counted here with this statement?

select count(*) from trans where SIG <> 'RTS';


COUNT(*)
----------
263

select count(*) from trans where SIG is null;

COUNT(*)
----------
425126


select count(*) from trans where (SIG <> 'RTS' or SIG is NULL);

COUNT(*)
----------
425389

Steve

[Reiß, Thomas]

Can be, so NULL isn't defined and the comparsion with <> wouldn't work with this (because is not true)?

or, what ist the result of:

select count(*) from trans where ((SIG <> 'RTS') or (SIG is NULL));

What DB is running?

Greetings
Thomas

[Karsten Breivik_1]

Just a thought - are you sure that the values are really null? If there is an home-grown application on top fo the base, could it be that the application developers actually have insert a text literal value kalled '' instead of the database null value. Have seen this a couple of times. When doing a select it can be difficult to distinguish between the null value and a text literal called null if you do not think about it.

[Stuart Browne]

What database are you using?

[Donald Crook]

NULL is a strange beast. It's not equal to anything, not even itself. You should always use IS NULL or IS NOT NULL rather than = when testing for NULL values.

--Don