NULL is perhaps one of  the most dangerous concept but easily ignored by many. Because its definition it self is not clear or rather not a standard one. Different softwares and applications define NULL differently and so their functionality. I have not got much exposure to numeruos technologies, but so far with my experience Oracle handles NULL very eligantly. Oracle defines NULL as something not defined, so you don't know its value. And more interestingly, both 'NULL = NULL' and 'NULL != NULL' are not true, because when you don't know the value of NULL it can not be compared. By now one more point is clear that NULL and '' (a zero length string) are not same in Oracle - since '' is a knwon value.

Handling NULL is not at all difficult, at least from my experience. All that we need to know is how your environment handles NULL - understanding its definition and little bit testing. Common mistake is many people ignore the very basic truth that NULL in their environment could work differently from thier past experiences with NULL, and face surprises that are usually difficult to debug. It may work differently not just from application to application but even from version to version.

Today, I was caught in surprise when NULL behaved differently from Informatica 7.1 to 8.6. See the below image.

There are many session instances that run cocurrently. All of them are same but they just run  for different connections (1 session = 1 source = 1 connection) for  which connection names are parameterized. Since number of sources is not static, I have to run this  workflow until I finish running a session for every source. If  I have 10 concurrent sessions and 47 sources then it would  require 5 workflow runs ( 10 + 10 + 10 + 10 + 7). So in that last run I need to run only 7 sessions but not 10, and this is  taken care by the link condictions I've specified in between "Start" task and Session instances. In Informatica 8.6 I defined a link as one below...

IS NOTNULL($$DBConnectionSource1)

$$DBConnectionSource1  is a workflow variables that comes from parameter file. Below is the parameter file used for last run (that runs only 7 sessions)

$cat w_source_test.prm
[Test_Folder.WF:w_source_test]

$$DBConnection1=IDPV1
$$DBConnection2=IDPV2
$$DBConnection3=IDPV3
$$DBConnection4=IDSV1
$$DBConnection5=IDTV1
$$DBConnection6=IMPV1
$$DBConnection7=IMPV2
$

If you notice only 1 through 7 variables are defined. My intention is to keep only the variables in parameter file so that only thier correspnding sessions (based on the link condition) would run.
 
In 8.6 Informatica it runs as expected (I expected if variable is not defined then it is treated as NULL).
 

Link [Start --> s_m_source_test_7]: condition is TRUE for the expression [NOT ISNULL($$DBConnection7)] 
Link [Start --> s_m_source_test_8]: condition is FALSE for the expression [NOT ISNULL($$DBConnection8)] 

  
But in Informatica 7.1 it worked differently. With same parameter file both the links (for sessions 7 and 8) are evaluated false - means not defining the variable was not treated as NULL.
 
I had to change the link condition like below to make work...
 

$$DBConnectionSource1 != '' 

  
It worked - 7.1 considered undefined variable value as zero length string (but not as NULL).

Link [Start --> s_m_infa_connection_test_8]: condition is TRUE for the expression [NOT ISNULL($$DBConnection8)].

Link [Start --> s_m_infa_connection_test_9]: condition is FALSE for the expression [$$DBConnection9 != ''].

DBConnection8 was not defined in parameterfile but that was still evaluated to true treating its value as not a NULL, but DBConnection9 link evaluated correctly (to FALSE since value is not set) against zero length string.
 
So, it's just a point of note, but the bottomline is that we simply just can not ignore NULL as it is nothing set with or undefined, because you never know how that unknown thing is handled by your app. 
 

Update:

Although this post has much talked about Informatica handling NULL, there are some interesting posts of Tom Kyte (of asktom.oracle.com) on handling of NULL in Oracle.

http://tkyte.blogspot.com/2006/01/something-about-nothing.html
http://tkyte.blogspot.com/2006/01/mull-about-null.html

- Karteek