oracle sql_not exists與null的測試

除錯儲存過程時,發現產品表中許多列的值可能為null,導致not exists產生結果與所期不符
測試如下
--構建兩個同樣表結構的表
SQL> desc t_null_column;                                                                                                                                 
Name Type    Nullable Default Comments                                                                                                                   
---- ------- -------- ------- --------                                                                                                                   
A    INTEGER Y                                                                                                                                           
B    INTEGER Y                                                                                                                                           
C    INTEGER Y                                                                                                                                           
                                                                                                                                                         
SQL> desc t_null_column_2;                                                                                                                               
Name Type    Nullable Default Comments                                                                                                                   
---- ------- -------- ------- --------                                                                                                                   
A    INTEGER Y                                                                                                                                           
B    INTEGER Y                                                                                                                                           
C    INTEGER Y                                                                                                                                           
 
--b列為null                                                                                                                                                        
SQL> select * from t_null_column;                                                                                                                        
                                                                                                                                                         
                                      A                                       B                                       C                                  
--------------------------------------- --------------------------------------- ---------------------------------------                                  
                                      1                                                                               2                                  
                                                                                                                                                         
SQL> select * from t_null_column_2;                                                                                                                      
                                                                                                                                                         
                                      A                                       B                                       C                                  
--------------------------------------- --------------------------------------- ---------------------------------------                                  
                                      1                                                                               2                                  

  --如下sql本意是想查詢完全不在t_null_column_2表中的t_null_column表的記錄,我想的是不返回記錄才對,結果返回了記錄                                                                                                                                                     
SQL>  select tc.* from t_null_column tc where not exists (select 1 from t_null_column_2 tc2 where tc.a=tc2.a and tc.b=tc2.b and tc.c=tc2.c);             
                                                                                                                                                         
                                      A                                       B                                       C                                  
--------------------------------------- --------------------------------------- ---------------------------------------                                  
                                      1                                                                               2                                  
                                                                                                                         
--對上述的sql進行修正,對null列b進行nvl變更,這下結果符合期望                                                                                                                                                         
SQL>  select tc.* from t_null_column tc where not exists (select 1 from t_null_column_2 tc2 where tc.a=tc2.a and nvl(tc.b,1)=nvl(tc2.b,1) and tc.c=tc2.c);                                                                                                                                                    
                                                                                                                                                          
                                      A                                       B                                       C                                   
--------------------------------------- --------------------------------------- ---------------------------------------

小結:1,null相當可怕,一定要深入理解,不然會犯大錯誤
     2,null與null是不相等,即null=null的結果是false,而非true