Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
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