mysql中not in怎么使用

Question

如题所述

Answer 1

not In 相当于 <> all,如果 Not In 后面跟的是子查询的话，子查询中只要包含一个 null 的返回值，则会造成整个 Not in 字句返回空值，结果就是查询不会返回任何结果。而 in 相当于 =any 的意思,可以有效处理子查询中返回空值的情况,返回正确的结果。

mysql中not in和not exists两种查询到底哪种快?

因为in会使用你的子查询字段去到主表匹配你需要的行，而exists是根据匹配项去判断是或者否，然后根据是否决定结果，子查询的表大，用exists判断，效率就会高，而当子查询很小的时候，直接匹配你需要的值则更快。比如主表4万行，子查询里面有5条数据，那么exists会把4万行在子查询里面进行匹配，匹配上了就显示，匹配不上就不显示，所以需要判断4万次，而in则会在主表4万行里面去检索这5条记录，由于索引等等的存在，in的效率通常会更高，但是如果反过来，主表5条记录，子查询里面有4万行，exists只进行5次判断，而in会用4万个数据去匹配这5条记录，当然exists更快。

Answer 2

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);