Unexpected results with NULLs in SQL comparisons

Products

• IDMS
• IDMS - Database

Issue/Introduction

When using a NULLABLE field in an SQL comparison, if the value NULL is encountered, some of the boolean results of such comparisons can be surprising. For example in a simple SQL WHERE clause, NULL might appear to NOT be unequal to 1.

Consider the following data in a simple table with a NULLABLE column:

SELECT * FROM T1;
*+
*+      C1      C2
*+      --      --
*+       1       1
*+       2       9
*+       3  <null>
*+
*+ 3 rows processed

Now consider this query, the result of which is obvious:

SELECT * FROM T1 WHERE C1=C2;
*+
*+      C1      C2
*+      --      --
*+       1       1
*+
*+ 1 row processed

But consider this, which might be a little curious:

SELECT * FROM T1 WHERE C1<>C2;
*+
*+      C1      C2
*+      --      --
*+       2       9
*+
*+ 1 row processed

Why wasn't the third row included in the result set of this query?

Environment

Release: All supported releases.

Resolution

The answer lies in how NULLs are treated in an SQL comparison. When one of the columns in a comparison is NULL, the result is UNKNOWN, not TRUE or FALSE. The boolean result UNKNOWN is considered TRUE or FALSE depending upon the context of the comparison. If the comparison appears in the WHERE or HAVING clause of a SELECT, UPDATE or DELETE statement, the UNKNOWN is considered FALSE. This explains the above result.

However, if the same comparison appears in a CHECK constraint, the UNKNOWN value is interpreted as TRUE:

CREATE TABLE T2
  ( C1                               UNSIGNED NUMERIC(4) NOT NULL,
    C2                               UNSIGNED NUMERIC(4),
    CHECK        ( C1 <> C2 )  )
    IN SQLWORK.SQL-AREA-1
    ;

INSERT INTO T2 VALUES (3,NULL);
*+ Status = 0        SQLSTATE = 00000
*+ 1 row processed

SELECT * FROM T2;
*+
*+      C1      C2
*+      --      --
*+       3  <null>
*+
*+ 1 row processed

Additional Information

Expansion of Search-condition