oracle中in和的区别吗,In和Not In的使用注意事项和区别

not in  与  In 的区别

------------------------------------------------------------------------------------------------------

not In 相当于 <> all,如果 Not In 后面跟的是子查询的话，子查询中只要包含一个 null 的返回值，则会造成

整个 Not in 字句返回空值，结果就是查询不会返回任何结果。

而 in 相当于 =any 的意思,可以有效处理子查询中返回空值的情况,返回正确的结果。

------------------------------------------------------------------------------------------------------

not in 示例：--该例子想要返回没有下属的职员的姓名，如果子查询中有空值返回的话，则整个查询将没有结果返回

SELECT emp.last_name

FROM employees emp

WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr)

说明：

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:

SELECT emp.last_name

FROM employees emp

WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);

---------------------------------------------------------------------------------------------------------

Alternatively, a WHERE clause can be included in the subquery to display all employees who do not

have any subordinates:

--使用 Not In 的话，要注意除掉子查询中将要返回的空值

SELECT last_name

FROM employees

WHERE employee_id NOT IN

(SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);