Oracle Study---Use of oracle in and not

Oracle Study---Use of oracle in and not

------------------------------------------------------------------------------------------------------
 <> all, If not is followed by a subquery, the subquery contains only one A null return value causes the

In the equivalent of =any , you can effectively handle the case of returning null values in a subquery and return the correct result.
------------------------------------------------------------------------------------------------------
Not in Example:

--This example wants to return the name of an employee without a subordinate, and if a null value is returned in the subquery, the entire query will have no results returned

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

subordinates. Logically, this SQL statement should has returned 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, all conditions, that compare a null value, result in a null. so whenever null values 
is likely to is part of the resultsset of a subquery, does not use the not inoperator. The not in
operator are equivalent to <> all.
---------------------------------------------------------------------------------------------------------
In example:

notice that, the null value as part of the results set of a subquery are not a problem if you Use the in
operator. The in operator are equivalent to =any. For example, to-display the employees who have 
Subordinates (subordinate), 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
Has any subordinates:
--use not in to take care to get rid of the null value that will be returned in the subquery

2. 11:27:01 [email protected] test3 >select empno from emp 11:27:12 2 where empno not in (select Mgr from EMP where MG         R is not NULL); EMPNO----------7844 7521 7654 7499 7934 7369 7876 7900 8 rows Selected.

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1688399

Oracle Study---Use of oracle in and not