All logical operations including NOT involving NULL return NULL.

NULL in IF/THEN/ELSE or CASE statements is interpreted as FALSE.

Most operations including built-in functions, arithmetic operation with any NULL operand return NULL.

Rule Exception

  • Concatenations of strings ignore NULL.
  • DECODE can compare values with NULL.
  • REPLACE function can take NULL as a third parameter.

To check for NULL values, use the syntax:

value IS [NOT] NULL 


SQL> declare-- from w  w  w  .j  a  v  a  2  s.  c  om
  2      v1_nr NUMBER;
  3      v2_nr NUMBER :=1;
  4  begin
  5      if v1_nr is null then
  6          DBMS_OUTPUT.put_line('V1 is NULL');
  7      elsif v1_nr 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;
 13      if v2_nr is null then
 14          DBMS_OUTPUT.put_line('V2 is NULL');
 15      elsif v2_nr 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.


Related Topic