testing inequality with columns that can be null

Question

So, I asked a question this morning, which I did not phrase correctly, so I got a lot of responses as to why NULL compared to anything will give NULL/FALSE.

My actual question was, what is the time honored fashion in which db guys test inequalities for two columns that can both be NULL. My question is the exact opposite of this question.

The requirements are as follows, A and B are two columns:
a) if A and B are both NULL, they are equal, return FALSE
b) if A and B are both not NULL, then return A<>B
c) if either A or B are NULL, they are not equal, return TRUE

Answer 1

Depending on the data type and possible values for the columns:

COALESCE(A, -1) <> COALESCE(B, -1)

The trick is finding a value (here I used -1) that will NEVER appear in your data.

The other way would be:

(A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL)

This can be a problem depending on how your particular RDBMS handles NULLs. By the ANSI standard, this should give you what you want, but who follows standards anyway. :)

P.S. - I should also point out that using the COALESCE function may invalidate the use of indexes in comparing the columns. Check your query plan and performance of the query to see if that's a problem.

P.P.S. - I just noticed that OMG Ponies mentioned that Informix doesn't support COALESCE. It's an ANSI standard function I believe, but see what I said above about standards...

Answer 2

If you want to be sure about how NULLs are handled, you'll have to use whatever Informix supports for null checking. I haven't turned up much, other than the SE version doesn't support COALESCE, but it does support DECODE and possibly CASE.

WHERE COALESCE(t.a, 0) != COALESCE(t.b, 0)
WHERE DECODE(NULL, 0, t.a) != DECODE(NULL, 0, t.b)

Answer 3

I would personally write out the expression you came up with, especially if the table is expected to grow large. Wrapping the columns in function calls hurts performance by making it so the engine can't use any indexes you have on those columns. Of course, in a small table, this may not be any sort of issue, but I still like to do it the explicit way just in case a table ends up growing.

Answer 4

For SQL Server, use:

WHERE ISNULL(A, '') <> ISNULL(B, '')

Answer 5

can you try something like this in informix?

CASE
    WHEN a IS NULL AND B IS NULL THEN false 
    WHEN a IS NULL OR B IS NULL THEN true
    ELSE a <> B
END

from IBM Informix Guide to SQL: Syntax , CASE Expressions

Answer 6

The trouble is that a<>b (or a=b) yields NULL, not 1 or 0 when one or both operands are NULL. This doesn't matter for the = case because NULL OR 1 is 1 and NULL OR 0 is NULL which behaves like 0 for selecting in a WHERE clause.

You could say:

a<>b OR (a IS NULL)<>(b IS NULL)

However needing to do it either way may be a sign that you're misusing NULL and should consider changing the schema to use some other NOT NULL value to signify this comparable condition.

For example if you've got a person table with a title column, don't use NULL to signify that they have no title; that's not a ‘missing’ datum, it's just that no title exists. So store it as an empty string '' that you can happily compare with other empty strings. (Well unless you run Oracle of course, with its Empty String Problem...)

Answer 7

IBM Informix Dynamic Server has a somewhat peculiar view of booleans for a variety of historical (aka 'bad') reasons. Adapting the idea suggested by @astander, this CASE expression 'works', but I'd be the first to say 'not obvious' (see - I said it before you did!). The setup phase:

create table x(a int, b int);
insert into x values(null, null);
insert into x values(null, 1);
insert into x values(1, null);
insert into x values(1, 1);
insert into x values(1, 2);

The SELECT statement:

SELECT *
  FROM x
  WHERE   CASE
          WHEN a IS NULL AND b IS NULL THEN 'f'::BOOLEAN
          WHEN a IS NULL OR  b IS NULL THEN 't'::BOOLEAN
          WHEN a != b                  THEN 't'::BOOLEAN
          ELSE                              'f'::BOOLEAN
          END
;

The result from this query is:

                 1
      1           
      1          2

Issues:

• IDS does not recognize FALSE or TRUE or UNKNOWN as keywords.
• IDS does not recognize boolean expressions such as 'a != b' (or 'a <> b') as such.

Yes, it pains me greatly to have to state this.