General
cancel
Showing results for 
Search instead for 
Did you mean: 

nulls and the select not counting nulls

nulls and the select not counting nulls

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
4 REPLIES
Reiß, Thomas
Regular Advisor

Re: nulls and the select not counting nulls

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
Thomas Reiß
Karsten Breivik_1
Frequent Advisor

Re: nulls and the select not counting nulls


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.
poi
Stuart Browne
Honored Contributor

Re: nulls and the select not counting nulls

What database are you using?
One long-haired git at your service...
Donald Crook
Occasional Contributor

Re: nulls and the select not counting nulls

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