了解一下NULLs怎樣影響IN和exists
2024-07-21 02:06:20
供稿：網友
如果你的數據庫設計在任何一欄中都允許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時，你就可以避免一個非常普遍的問題了。
