NULLs如何影響IN與EXISTS

    如果你的數(shù)據(jù)庫設(shè)計(jì)在任何一欄中都允許NULL值的話，你需要了解一下，在你的查詢語句中，不同的子句是怎樣對(duì)待這一問題的。

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

    select 'true' from dual where 1 = null;

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

    值1既不能說是等于NULL，也不能說是不等于NULL。只有是NULL的時(shí)候才會(huì)返回一個(gè)真正的NULL值并返回一行。

    select 'true' from dual where 1 is null;

    select 'true' from dual where null is null;

    當(dāng)你使用IN時(shí)，相當(dāng)于你告訴SQL接受一個(gè)值，并將它與某個(gè)清單中使用＝的每一個(gè)值或一組值進(jìn)行比較。只要存在了任何NULL值，就不會(huì)返回任何行，縱使兩個(gè)值都是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));

    一個(gè)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));

    當(dāng)你使用一種與EXISTS等同的格式時(shí)，SQL會(huì)計(jì)算行數(shù)，卻忽視子查詢中的值，就算你返回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子句在子查詢內(nèi)部比較那些值并過濾掉行。在出現(xiàn)NULL值的情況下，作為結(jié)果而出現(xiàn)的那些行是相同的。

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

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

    不過，當(dāng)邏輯被轉(zhuǎn)變成使用NOT　IN和NOT　EXISTS時(shí)，問題就出現(xiàn)了，這兩個(gè)語句會(huì)返回不同的行（第一個(gè)查詢會(huì)返回0行；第二個(gè)返回意想的數(shù)據(jù)－它們是不同的查詢）：

    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子句實(shí)際上與用＝比較每一個(gè)值相同，如果任何一個(gè)測(cè)試為FALSE　或NULL的話，它就會(huì)失敗。例如:

    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));

    這些查詢不會(huì)返回任何行。而第二個(gè)更值得懷疑，1！＝NULL是NULL，因此對(duì)整個(gè)WHERE條件來說都是錯(cuò)誤的。它們會(huì)這樣運(yùn)行：

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

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

    只要你在結(jié)果中阻止系統(tǒng)返回NULL，在這之前你還是可以使用NOT　IN查詢（同樣，這些都能運(yùn)行，不過我假定empno不是NULL，在我們這個(gè)案例中，這是一個(gè)很好的假設(shè)）:

    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之間的差別，當(dāng)一個(gè)子查詢的數(shù)據(jù)中出現(xiàn)NULL時(shí)，你就可以避免一個(gè)非常普遍的問題了。