Oracle的where條件in/not in中包含NULL時的處理

我們在寫SQL時經常會用到in條件，如果in包含的值都是非NULL值，那麼沒有特殊的，但是如果in中的值包含null值(比如in後面跟一個子查詢，子查詢傳回的結果有NULL值)，Oracle又會怎麼處理呢？

建立一個測試表t_in

<code>zx@TEST&gt;</code><code>create</code> <code>table</code> <code>t_in(id number);</code>

<code>Table</code> <code>created.</code>

<code>zx@TEST&gt;</code><code>insert</code> <code>into</code> <code>t_in </code><code>values</code><code>(1);</code>

<code>1 row created.</code>

<code>zx@TEST&gt;</code><code>insert</code> <code>into</code> <code>t_in </code><code>values</code><code>(2);</code>

<code>zx@TEST&gt;</code><code>insert</code> <code>into</code> <code>t_in </code><code>values</code><code>(3);</code>

<code>zx@TEST&gt;</code><code>insert</code> <code>into</code> <code>t_in </code><code>values</code><code>(</code><code>null</code><code>);</code>

<code>zx@TEST&gt;</code><code>insert</code> <code>into</code> <code>t_in </code><code>values</code><code>(4);</code>

<code>zx@TEST&gt;</code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in;</code>

<code>    </code><code>ID</code>

<code>----------</code>

<code>     </code><code>1</code>

<code>     </code><code>2</code>

<code>     </code><code>3</code>

<code>     </code><code>4</code>

現在t_in表中有5條記錄

１、in條件中不包含NULL的情況

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in </code><code>where</code> <code>id </code><code>in</code> <code>(1,3);</code>

<code>2 </code><code>rows</code> <code>selected.</code>

上面的條件等價于id =1 or id = 3得到的結果正好是2；檢視執行計劃中可以看到 2 - filter("ID"=1 OR "ID"=3)說明我們前面的猜測是正确的

<a href="https://s5.51cto.com/wyfs02/M02/8E/8A/wKiom1jEF2aCr9WPAAAvp0DwXvA709.png" target="_blank"></a>

2、in條件包含NULL的情況

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in </code><code>where</code> <code>id </code><code>in</code> <code>(1,3,</code><code>null</code><code>);</code>

上面的條件等價于id = 1 or id = 3 or id = null，我們來看下圖當有id = null條件時Oracle如何處理

<a href="https://s2.51cto.com/wyfs02/M02/8E/89/wKioL1jEGDGSqwq4AADFs0n5yAs239.png" target="_blank"></a>

從上圖可以看出當不管id值為NULL值或非NULL值，id = NULL的結果都是UNKNOWN，也相當于FALSE。是以上面的查結果隻查出了1和3兩條記錄。

檢視執行計劃看到優化器對IN的改寫

<a href="https://s4.51cto.com/wyfs02/M02/8E/89/wKioL1jEGXbS0KvWAAAn88BjuzM400.png" target="_blank"></a>

3、not in條件中不包含NULL值的情況

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in </code><code>where</code> <code>id </code><code>not</code> <code>in</code> <code>(1,3);</code>

上面查詢的where條件等價于id != 1 and id !=3，另外t_in表中有一行為null，它雖然滿足!=1和!=3但根據上面的規則，NULL與其他值做=或!=比較結果都是UNKNOWN，是以也隻查出了2和4。

從執行計劃中看到優化器對IN的改寫

<a href="https://s3.51cto.com/wyfs02/M01/8E/89/wKioL1jEGpDD8LvvAAAmZlcvHxo822.png" target="_blank"></a>

4、not in條件中包含NULL值的情況

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in </code><code>where</code> <code>id </code><code>not</code> <code>in</code> <code>(1,3,</code><code>null</code><code>);</code>

<code>no</code> <code>rows</code> <code>selected</code>

上面查詢的where條件等價于id!=1 and id!=3 and id!=null，根據上面的規則，NULL與其他值做=或!=比較結果都是UNKNOWN，是以整個條件就相當于FALSE的，最終沒有查出資料。

從執行計劃中檢視優化器對IN的改寫

<a href="https://s2.51cto.com/wyfs02/M02/8E/89/wKioL1jEG26hPkSeAAAnXX8JGa8308.png" target="_blank"></a>

總結一下，使用in做條件時時始終查不到目标列包含NULL值的行，如果not in條件中包含null值，則不會傳回任何結果，包含in中含有子查詢。是以在實際的工作中一定要注意not in裡包含的子查詢是否包含null值。

<code>zx@TEST&gt;</code><code>select</code> <code>* </code><code>from</code> <code>t_in </code><code>where</code> <code>id </code><code>not</code> <code>in</code> <code>(</code><code>select</code> <code>id </code><code>from</code> <code>t_in </code><code>where</code> <code>id = 1 </code><code>or</code> <code>id </code><code>is</code> <code>null</code><code>);</code>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions013.htm#SQLRF52169" target="_blank">http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions013.htm#SQLRF52169</a>

<a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions004.htm#SQLRF52116" target="_blank">http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions004.htm#SQLRF52116</a>

      本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1905515，如需轉載請自行聯系原作者