topic nulls and the select not counting nulls in Operating System - Linux

nulls and the select not counting nulls

Stephen Ray Badgett — Mon, 12 Jun 2006 15:35:28 GMT

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

Re: nulls and the select not counting nulls

Reiß, Thomas — Tue, 13 Jun 2006 01:21:11 GMT

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

Re: nulls and the select not counting nulls

Karsten Breivik_1 — Tue, 13 Jun 2006 02:46:41 GMT

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.

Re: nulls and the select not counting nulls

Stuart Browne — Tue, 13 Jun 2006 04:44:40 GMT

What database are you using?

Re: nulls and the select not counting nulls

Donald Crook — Tue, 13 Jun 2006 11:43:00 GMT

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