Shulou(Shulou.com)06/01 Report--

Oracle Study-the use of Oracle IN and NOT IN

The difference between NOT IN and IN:

-

Not In is equivalent to all. If Not In is followed by a subquery, as long as the subquery contains a return value of null, it will result in

The entire Not in statement returns a null value, and the result is that the query does not return any results.

While in is equivalent to = any, it can effectively handle the situation that null values are returned in subqueries and return correct results.

-

NOT IN example:

This example wants to return the names of employees who do not have subordinates, and if a null value is returned in the subquery, no results will be returned for the entire query.

11:20:02 SYS@ test3 > conn scott/tiger Connected. 11:21:18 SCOTT@ 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 78391981-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 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 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 14 rows selected. 11:20:11 SCOTT@ test3 > select empno from emp 11:20:21 2 where empno NOT IN (select mgr from emp); no rows selected

Description:

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.

-

An example of 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 (subordinate), use the following SQL statement:

11:20:42 SCOTT@ 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:

If you use Not In, be careful to remove the null values that will be returned in the subquery

11:27:01 SCOTT@ 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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.