MySQL“NOT IN”查询

MySQL "NOT IN" query

提问by Kshitij Saxena -KJ-

I wanted to run a simple query to throw up all the rows of Table1where a principal column value is not present in a column in another table (Table2).

我想运行一个简单的查询来抛出Table1另一个表 ( Table2)的列中不存在主列值的所有行。

I tried using:

我尝试使用：

SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal

This is instead throwing a syntax error. Google search led me to forums where people were saying that MySQL does not support NOT INand something extremely complex needs to be used. Is this true? Or am I making a horrendous mistake?

这反而会引发语法错误。谷歌搜索把我带到论坛，那里有人说 MySQL 不支持NOT IN并且需要使用一些非常复杂的东西。这是真的？还是我犯了一个可怕的错误？

回答by Julien Lebosquain

To use IN, you must have a set, use this syntax instead:

要使用 IN，您必须有一个集合，请改用以下语法：

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2)

回答by Luká? Lalinsky

The subquery option has already been answered, but note that in many cases a LEFT JOINcan be a faster way to do this:

已经回答了子查询选项，但请注意，在许多情况下， aLEFT JOIN可能是执行此操作的更快方法：

SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal
WHERE table2.principal IS NULL

If you want to check multiple tables to make sure it's not present in any of the tables (like in SRKR's comment), you can use this:

如果您想检查多个表以确保它不存在于任何表中（如 SRKR 的评论中），您可以使用：

SELECT table1.*
FROM table1
LEFT JOIN table2 ON table2.name=table1.name
LEFT JOIN table3 ON table3.name=table1.name
WHERE table2.name IS NULL AND table3.name IS NULL

回答by engin

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL in MySQL

在 MySQL 中 NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL

MySQL, as well as all other systems except SQL Server, is able to optimize LEFT JOIN/ IS NULLto return FALSEas soon the matching value is found, and it is the only system that cared to document this behavior. […] Since MySQL is not capable of using HASHand MERGEjoin algorithms, the only ANTI JOINit is capable of is the NESTED LOOPS ANTI JOIN

MySQL 以及除 SQL Server 之外的所有其他系统都能够优化LEFT JOIN/IS NULL在FALSE找到匹配值后立即返回，并且它是唯一关心记录此行为的系统。[…] 由于 MySQL 不具备使用HASH和MERGE连接算法的能力，因此ANTI JOIN它唯一能做的就是NESTED LOOPS ANTI JOIN

[…]

[…]

Essentially, [NOT IN]is exactly the same plan that LEFT JOIN/ IS NULLuses, despite the fact these plans are executed by the different branches of code and they look different in the results of EXPLAIN. The algorithms are in fact the same in fact and the queries complete in same time.

本质上，[ NOT IN]与LEFT JOIN/IS NULL使用的计划完全相同，尽管这些计划由不同的代码分支执行并且它们在EXPLAIN. 算法实际上是相同的，并且查询在同一时间完成。

[…]

[…]

It's hard to tell exact reason for [performance drop when using NOT EXISTS], since this drop is linear and does not seem to depend on data distribution, number of values in both tables etc., as long as both fields are indexed. Since there are three pieces of code in MySQL that essentialy do one job, it is possible that the code responsible for EXISTSmakes some kind of an extra check which takes extra time.

很难说出[使用NOT EXISTS]时性能下降的确切原因，因为这种下降是线性的，并且似乎不依赖于数据分布、两个表中的值数量等，只要两个字段都被索引。由于 MySQL 中有三段代码基本上可以完成一项工作，因此负责的代码可能会EXISTS进行某种额外的检查，这需要额外的时间。

[…]

[…]

MySQL can optimize all three methods to do a sort of NESTED LOOPS ANTI JOIN. […] However, these three methods generate three different plans which are executed by three different pieces of code. The code that executes EXISTSpredicate is about 30% less efficient […]

That's why the best way to search for missing values in MySQL is using a LEFT JOIN/ IS NULLor NOT INrather than NOT EXISTS.

MySQL 可以优化所有三种方法来做一种NESTED LOOPS ANTI JOIN. […] 然而，这三种方法生成三种不同的计划，由三段不同的代码执行。执行EXISTS谓词的代码效率降低了大约 30% […]

这就是为什么在 MySQL 中搜索缺失值的最佳方法是使用LEFT JOIN/IS NULL或NOT IN而不是NOT EXISTS.

(emphases added)

（重点补充）

回答by Legna

Unfortunately it seems to be a issue with MySql usage of "NOT IN" clause, the screen-shoot below shows the sub-query option returning wrong results:

不幸的是，这似乎是 MySql 使用“NOT IN”子句的问题，下面的屏幕截图显示了返回错误结果的子查询选项：

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.21                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.07 sec)

mysql> select count(*) from TABLE_A where TABLE_A.Pkey not in (select distinct TABLE_B.Fkey from TABLE_B );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from TABLE_A left join TABLE_B on TABLE_A.Pkey = TABLE_B.Fkey where TABLE_B.Pkey is null;
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from TABLE_A where NOT EXISTS (select * FROM TABLE_B WHERE TABLE_B.Fkey = TABLE_A.Pkey );
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> 

回答by user4554358

Be carefull NOT INis not an alias for <> ANY, but for <> ALL!

Be carelNOT IN不是 for 的别名<> ANY，而是 for <> ALL！

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

SELECT c FROM t1 LEFT JOIN t2 USING (c) WHERE t2.c IS NULL

cant' be replaced by

不能'被取代

SELECT c FROM t1 WHERE c NOT IN (SELECT c FROM t2)

You must use

你必须使用

SELECT c FROM t1 WHERE c <> ANY (SELECT c FROM t2)