瞭解一下NULLs怎樣影響IN和EXISTS(轉)

瞭解一下NULLs怎樣影響IN和EXISTS(轉)[@more@]

　　如果你的資料庫設計在任何一欄中都允許NULL值的話，你需要了解一下，在你的查詢語句中，不同的子句是怎樣對待這一問題的。

　　

　　從表面上看，可能顯示出這樣的情形，即SQL子句IN與EXISTS可以互換。然而，在處理NULL值時，它們的表現截然不同，而且得到的結果也很可能不同。問題源於這樣一個事實，即在一個Oracle資料庫中，一個NULL值意味著未知，因此，對一個NULL值的任何比較或操作也都是無效的，而任何返回NULL的測試也都被忽視了。例如，以下這些查詢語句都不會返回任何行：

　　

　　select 'true' from dual where 1 = null;

　　

　　select 'true' from dual where 1 != null;

　　

　　值1既不能說是等於NULL，也不能說是不等於NULL。只有是NULL的時候才會返回一個真正的NULL值並返回一行。

　　

　　select 'true' from dual where 1 is null;

　　

　　select 'true' from dual where null is null;

　　

　　當你使用IN時，相當於你告訴SQL接受一個值，並將它與某個清單中使用＝的每一個值或一組值進行比較。只要存在了任何NULL值，就不會返回任何行，縱使兩個值都是NULL也不行。

　　

　　select 'true' from dual where null in (null);

　　

　　select 'true' from dual where (null,null) in ((null,null));

　　

　　select 'true' from dual where (1,null) in ((1,null));

　　

　　一個IN從功能上等同於＝ANY子句：

　　

　　select 'true' from dual where null = ANY (null);

　　

　　select 'true' from dual where (null,null) = ANY ((null,null));

　　

　　select 'true' from dual where (1,null) = ANY ((1,null));

　　

　　當你使用一種與EXISTS等同的格式時，SQL會計算行數，卻忽視子查詢中的值，就算你返回NULL也一樣。

　　

　　select 'true' from dual where exists (select null from dual);

　　

　　select 'true' from dual where exists (select 0 from dual where null is null);

　　

　　從邏輯上看，IN與EXISTS是一樣的。IN子句在外部查詢中比較子查詢返回的值，並過濾掉行；EXISTS子句在子查詢內部比較那些值並過濾掉行。在出現NULL值的情況下，作為結果而出現的那些行是相同的。

　　

　　selectename from emp where empno in (select mgr from emp);

　　

　　selectename from emp e where exists (select 0 from emp where mgr = e.empno);

　　

　　不過，當邏輯被轉變成使用NOT　IN和NOT　EXISTS時，問題就出現了，這兩個語句會返回不同的行（第一個查詢會返回0行；第二個返回意想的資料－它們是不同的查詢）：

　　

　　selectename from emp where empno not in (select mgr from emp);

　　

　　selectename from emp e where not exists (select 0 from emp where mgr =e.empno);

　　

　　NOT　IN子句實際上與用＝比較每一個值相同，如果任何一個測試為FALSE　或NULL的話，它就會失敗。例如:

　　

　　select 'true' from dual where 1 not in (null,2);

　　

　　select 'true' from dual where 1 != null and 1 != 2;

　　

　　select 'true' from dual where (1,2) not in ((2,3),(2,null));

　　

　　select 'true' from dual where (1,null) not in ((1,2),(2,3));

　　

　　這些查詢不會返回任何行。而第二個更值得懷疑，1！＝NULL是NULL，因此對整個WHERE條件來說都是錯誤的。它們會這樣執行：

　　

　　select 'true' from dual where 1 not in (2,3);

　　

　　select 'true' from dual where 1 != 2 and 1 != 3;

　　

　　只要你在結果中阻止系統返回NULL，在這之前你還是可以使用NOT　IN查詢（同樣，這些都能執行，不過我假定empno不是NULL，在我們這個案例中，這是一個很好的假設）:

　　

　　selectename from emp where empno not in (select mgr from emp where mgr is not null);

　　

　　selectename from emp where empno not in (select nvl(mgr,0) from emp);

　　

　　由於瞭解了IN，EXISTS，NOT　IN，以及NOT　EXISTS之間的差別，當一個子查詢的資料中出現NULL時，你就可以避免一個非常普遍的問題了。