Oracle sql: if I am doing a comparison operator on a date field do i still need a not null-CodePudding

If I am doing a comparison operator (>, <, =) on a date field do I also need a IS NOT NULL on the date field

CodePudding user response：

If I understood you correctly, then no - you don't have to check whether it is not null.

For example (today is date '2022-07-07', 7th of July 2022):

SQL> with test (id, date_column) as
  2    (select 1, date '2022-05-25' from dual union all  -- before today
  3     select 2, date '2022-12-13' from dual union all  -- after today
  4     select 3, null              from dual            -- unknown, as there's no value in DATE_COLUMN
  5    )
  6  select *
  7  from test
  8  where date_column >= sysdate;

        ID DATE_COLUM
---------- ----------
         2 2022-12-13

SQL>

If you include the not null check, you'll get the same result:

<snip>
  6  select *
  7  from test
  8  where date_column is not null
  9    and date_column >= sysdate;

        ID DATE_COLUM
---------- ----------
         2 2022-12-13

SQL>