Informix SQL - Simple select query is returning unexpected results

Question

Follow this, it's really obvious and simple. For some reason, the results differ between queries and approach angles on data subsets. Keep in mind that the field, correct_addr is a char(1) field with allowable nulls.

select distinct correct_addr, count(*) from id_rec group by correct_addr;

correct_addr       (count(*))
                         2477
N                          80
Y                       84013

3 row(s) retrieved.

Ok, so correct_addr contains 3 distinct values: "N","Y", and either "" or " " or NULL

So now, I try this:

select count(*) from id_rec where correct_addr <> 'N';

      (count(*))
           84013

What happened to the 2477 records that have that blank value?

Another try from a different angle:

select count(*) from id_rec where correct_addr in (null,'',' ','Y');

      (count(*))
           84013

Same thing happens....

So what is going on here? Why doesn't the sql engine (?) recognize the blank value in the last 2 sql statements? It discovers it fine in the first query with the grouping, but nowhere else.

Does anyone have any ideas as to why this type of thing happens?

Answer 1

NULLs require special handling in SQL.

Try

select count(*) 
from id_rec 
where correct_addr <> 'N' 
    or correct_addr is null; 

See here for an explanation of handling NULLs.

Answer 2

NULL comparisons are always false. And empty string or single space is a value which is not NULL.

However, GROUP BY will recognise it and count it.

Try these

select count(*) from id_rec
where correct_addr <> 'N' or correct_addr IS NULL

select count(*) from id_rec
where COALESCE(correct_addr, 'X') <> 'N' 


select count(*) from id_rec
where COALESCE(correct_addr, ' ') in (' ','Y');

Also, COUNT(column) will ignore NULLS so some more to try

select count(correct_addr), COUNT(*) from id_rec GROUP BY correct_addr

select count(correct_addr), COUNT(*) from id_rec
where correct_addr <> 'N' or correct_addr IS NULL

Note: char(1) will always pad to a space