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 != 'Sekhar'
   THEN

Sekhar');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('My nam
DBMS_OUTPUT.PUT_LINE ('My name is not
 e is Sekhar');
   END IF;
END;

BEGIN
   MY_NAME (NULL);


END;

If I run previous code it will print

 My name is Sekhar  

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

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 'Sekhar' || NULL will return 'Sekhar'

4- You can use logical conditional  "IS NULL" "IS NOT NULL" to check NULL value

  For example IF 'Sekhar' IS NOT NULL will return TRUE Boolean

  For example IF 'Sekhar' 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 != 'Sekhar'

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

 IF IN_NAME = 'Sekhar'

7-Take attention that empty string is NULL values also

 For example

 DECLARE  

   X  VARCHAR2 (10);  
BEGIN  
   X := '';  

EN  
    DBMS_OUT
IF X IS NULL  
   T
HPUT.PUT_LINE ('x is null');  
   ELSE  

);  
   END IF;  
 END;  

DBMS_OUTPUT.PUT_LINE ('x is not null

'

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

Sekhar