Returning to null...

In a recent article I talked about the way to return a NULL from a query. That remembered me how "picky" Informix is with NULL. Did this sound like me saying Informix does something wrong?! That would be strange in an Informix dedicated blog...
Well... I said "picky" because that's the feeling I get from people who face the way Informix deals with NULL. Usually those persons have a background in other RDBMS which may times behave differently. Let me show some situations common in Informix:

1. The query below returns NULL. Most of the times people expect it to return "SomeString"
SELECT
"SomeString"||NULL
FROM
systables
WHERE
tabid = 1;
   
2. The query below return no rows. People would expect it to return some rows
   SELECT
"SomeString"
FROM
systables
WHERE
"A" != NULL::CHAR
   
3. The query below also returns NULL. People usually expect it to return 10
   SELECT
10 + NULL::INTEGER
FROM
systables
WHERE
tabid = 1

   

Query 1) and 3) are similar. Above I wrote that Informix is "picky". I could say it in another way: Informix is very strict on the way it treats NULL. By definition (SQL standards), NULL is the absence of value. That means that you can't do nothing with it... Any operation (concatenation in query 1 and addition in query 3 for example) will return NULL. Any comparison with NULL returns FALSE. In order to understand it we must avoid looking at the question with a practical approach and instead use a formal, conceptual approach. In other words, it would be intuitive that if we concatenate a string with "nothing" we would still get the string. But on the other hand, conceptually, we cannot concatenate a string with the absence of string... And we cannot compare anything with NULL, because it does not exist. So, by definition any such comparison should return FALSE. "A" is not equal to something that does not exists, but conceptually you can't compare these two operands, so the comparison fails (FALSE).

Also note that in Informix NULL cannot be represented by "" for example. "" is an empty string. But at least it has a defined value. NULL hasn't.

Many developers argue that the way Informix deals with NULL does not make sense and causes incoveniences. I argue that this makes a lot of sense (mathematically) and I concede that it can be inconvenient sometimes from a practical perspective. But in reality, what those developers or I think is pretty irrelevant. What is relevant is that it's the way the SQL standard defines NULL and Informix follows it.

In order to overcome the inconveniences we can use the NVL() function.