John Isaiah Carmona John Isaiah Carmona - 6 months ago 18
SQL Question

Is Null Greater Than Any Date Data Type?

I have this query in DB2

SELECT * FROM SOMESCHEMA.SOMETABLE WHERE SYSDATE > @A


If the
SYSDATE
is
NULL
, would it be greater than any value of
@A
, assuming that
@A
and
SOMESCHEMA.SOMETABLE.SYSDATE
is a Date data type?

Please help. Thanks in advance.

Answer

Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both columns contain an equal non-null value. If both columns are null, the result will be false because null is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal non-null value and also when both columns are the null value.

DB2 Null Handling

That means that all comparison operations will be false because you are comparing an unknown value to something. So no matter which comparison you use (only the IS NULL/IS NOT NULL operation do work!), it will be false.

If you want the query to work you should use something like

SELECT * 
  FROM SOMESCHEMA.SOMETABLE 
 WHERE COALESCE(SYSDATE, TIMESTAMP_FORMAT('0001-01-01 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))  > @A