Oracle Study之---Oracle IN和NOT IN的使用

標籤：oracle

Oracle Study之---Oracle IN和NOT IN的使用

NOT IN 與  IN 的區別:
------------------------------------------------------------------------------------------------------
not In 相當於 <> all,如果 Not In 後面跟的是子查詢的話，子查詢中只要包含一個 null 的傳回值，則會造成
整個 Not in 字句返回空值，結果就是查詢不會返回任何結果。

而 in 相當於 =any 的意思,可以有效處理子查詢中返回空值的情況,返回正確的結果。
------------------------------------------------------------------------------------------------------
NOT IN樣本：

--該例子想要返回沒有下屬的職員的姓名，如果子查詢中有空值返回的話，則整個查詢將沒有結果返回

11:20:02 [email protected] test3 >conn scott/tiger  Connected.  11:21:18 [email protected] test3 >select * from emp;       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------        7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20        7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30        7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30        7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20        7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30        7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30        7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10        7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20        7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10        7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30        7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20        7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30        7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20        7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10  14 rows selected.  11:20:11 [email protected] test3 >select empno from emp  11:20:21   2            where empno NOT IN (select mgr from emp);  no rows selected

說明：
Null Values in a Subquery
The SQL statement in the slide attempts to display all the employees who do not have any 
subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL 
statement does not return any rows. One of the values returned by the inner query is a null value and, 
therefore, the entire query returns no rows

The reason is that all conditions that compare a null value result in a null. So whenever null values 
are likely to be part of the resultsset of a subquery, do not use the NOT INoperator. The NOT IN
operator is equivalent to <> ALL.
---------------------------------------------------------------------------------------------------------
IN的樣本：

Notice that the null value as part of the results set of a subquery is not a problem if you use the IN
operator. The IN operator is equivalent to =ANY. For example, to display the employees who have 
subordinates(下屬), use the following SQL statement:

11:20:42 [email protected] test3 >select empno from emp  11:21:04   2      where empno in  (select mgr from emp);         EMPNO  ----------        7566        7698        7782        7788        7839        7902  6 rows selected.

---------------------------------------------------------------------------------------------------------
Alternatively, a WHERE clause can be included in the subquery to display all employees who do not 
have any subordinates:
--使用 Not In 的話，要注意除掉子查詢中將要返回的空值

1. 11:27:01 [email protected] test3 >select empno from emp  11:27:12   2  where empno NOT IN (select mgr from emp WHERE MGR IS NOT NULL);         EMPNO  ----------        7844        7521        7654        7499        7934        7369        7876        7900  8 rows selected.

本文出自 “天涯客的blog” 部落格，請務必保留此出處http://tiany.blog.51cto.com/513694/1688399

Oracle Study之---Oracle IN和NOT IN的使用