Play with NULL Value

NULL is big problem in whole programming language, Today I will write about NULL and  what's the problems we face in code and tips to play with NULL values.

Calling any method or variable of Class has NULL value raise exception in other programming language like C++, Java, C# ,.... etc but in PLSQL it doesn't raise any exception.
If I use NULL in any calculation or logical condition, the output will be NULL.

I wrote before about Three-Valued Logic and the problem  in three-valued logic in PLSQL is NULL value.

Lets see example work with NULL before begin illustration.

CREATE OR REPLACE PROCEDURE MY_NAME (IN_NAME VARCHAR2)
IS
BEGIN
   IF IN_NAME != 'Mahmoud'
   THEN
      DBMS_OUTPUT.PUT_LINE ('My name is not Mahmoud');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My name is Mahmoud');
   END IF;
END;

BEGIN
   MY_NAME (NULL);
END;

If I run previous code it will print

 My name is Mahmoud  

The printed result is illogical for us because I pass IN_NAME parameter as NULL value, and It printed "MY Name is Mahmoud"

So I should state tips about play with NULL
1-Any logical conditional with NULL will return NULL
   For example variable=NULL will return NULL
   NULL = NULL will return NULL

2- Any mathematical operation with NULL will return NULL
   For example 3+NULL will return NULL

3- Any concatenation with NULL will return the basic string without NULL
  For example 'Mahmoud' || NULL will return 'Mahmoud'

4- You can use logical conditional  "IS NULL" "IS NOT NULL" to check NULL value
  For example IF 'Mahmoud' IS NOT NULL will return TRUE Boolean
  For example IF 'Mahmoud' IS NULL will return FALSE  Boolean

5-Use DECODE to handle NULL values column in SELECT statement
  You want to get employees from EMP table whose Name is equal to parameter :EMP_NAME otherwise if :EMP_NAME hasn't value return all employees.
You can do this requirement using  OR operand

 SELECT *
  FROM EMP
 WHERE ENAME = :EMP_NAME OR :EMP_NAME IS NULL ;

I should replace previous query by the below query to use DECODE

 SELECT *  
  FROM EMP  
  WHERE DECODE (:EMP_NAME, ENAME, 1, NULL, 1, 0) = 1  ;

6-To avoid logical fallacies always is is better to begin checking with true value then the difference
For example in previous MY_NAME I began logical condition  with

 IF IN_NAME != 'Mahmoud'

So I should write it again and begin with true value like

 IF IN_NAME = 'Mahmoud'

7-Take attention that empty string is NULL values also
 For example

 DECLARE  
   X  VARCHAR2 (10);  
 BEGIN  
   X := '';  
   IF X IS NULL  
   THEN  
    DBMS_OUTPUT.PUT_LINE ('x is null');  
   ELSE  
    DBMS_OUTPUT.PUT_LINE ('x is not null');  
   END IF;  
 END;  

It will print "x is null" however x is empty string

8-NULL values are excluded from B-tree indexes, so searching for NULL values will cause a full table scan.

9-Bitmap indexes can be used to search for NULL values

Thanks