All logical operations (including NOT) involving NULL always return NULL : NULL « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. NULL

In a logical group of IF/THEN/ELSE or CASE statements, NULL is interpreted as FALSE.

Most operations (built-in functions, arithmetic) with any NULL operand return NULL with the following exceptions:

Concatenations of strings ignore NULL.

DECODE (which we discuss later) can compare values with NULL.

The REPLACE function can take NULL as a third parameter.

You should check for NULL values by using the syntax:

variable|expression|function IS [NOT] NULL

This structure evaluates the value against NULL.

You can check equality by using IS NULL or inequality by using IS NOT NULL.

SQL>
SQL> declare
  2      v1 NUMBER;
  3      v2 NUMBER :=1;
  4  begin
  5      if v1 is null then
  6          DBMS_OUTPUT.put_line('v1 is NULL');
  7      elsif v1 is not null then
  8          DBMS_OUTPUT.put_line('v1 is not NULL');
  9      else
 10          DBMS_OUTPUT.put_line('Undefined result');
 11      end if;
 12
 13      if v2 is null then
 14          DBMS_OUTPUT.put_line('v2 is NULL');
 15      elsif v2 is not null then
 16          DBMS_OUTPUT.put_line('v2 is not NULL');
 17      else
 18          DBMS_OUTPUT.put_line('Undefined result');
 19      end if;
 20  end;
 21  /
v1 is NULL
v2 is not NULL

PL/SQL procedure successfully completed.

SQL>