First Part : I hate Null : Null and Number

Why I hate Null !

C:Documents AND SettingsNicolas>sqlplus gerardnico/Password
 
SQL*Plus: Release 10.2.0.4.0 - Production ON Wed Apr 8 15:41:24 2009
 
Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.
 
 
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
 
gerardnico@orcl>create TABLE IHateNull (id number NOT NULL, val1 number NULL, val2 number NULL);
 
TABLE created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,2,NULL);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,NULL,1);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,0,0);
 
1 row created.
 
gerardnico@orcl>insert INTO IHateNull VALUES (1,1,1);
 
1 row created.
 
gerardnico@orcl>select * FROM IHateNull WHERE ( val1 + val2 ) <> 0;
 
        ID       VAL1       VAL2
         1          1          1
 
gerardnico@orcl>select * FROM IHateNull WHERE ( nvl(val1,0) + nvl(val2,0) ) <> 0;
 
        ID       VAL1       VAL2
         1          2
         1                     1
         1          1          1