MySQL "NOT IN" 查询

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

我试着用:

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

这反而会抛出一个语法错误。谷歌搜索把我带到了论坛，人们说 MySQL 不支持 NOT IN，需要使用一些极其复杂的东西。这是真的吗?还是我犯了个可怕的错误?

542600 次浏览
小开
最佳答案

要使用IN，你必须有一个set，使用下面的语法:

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

子查询选项已经回答，但请注意，在许多情况下，LEFT JOIN可以更快地完成此操作:

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

如果你想检查多个表，以确保它不存在于任何表中(就像在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
小开

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

MySQL，以及除SQL Server之外的所有其他系统，能够优化__abc0 / __abc1，以便在找到匹配值时返回FALSE，并且它是唯一关心记录此行为的系统。 [& help;]由于MySQL不能使用HASH和MERGE连接算法，它唯一能够使用的ANTI JOIN是NESTED LOOPS ANTI JOIN

(白马王子;)

本质上，(< >强NOT IN < / >强)与LEFT JOIN / IS NULL使用的计划完全相同，尽管这些计划是由不同的代码分支执行的，并且它们在EXPLAIN的结果中看起来不同。算法实际上是一样的，查询在同一时间内完成。

(白马王子;)

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

(白马王子;)

MySQL可以优化所有三个方法来做某种NESTED LOOPS ANTI JOIN。 (白马王子;) 然而，这三个方法生成了三个不同的计划，由三个不同的代码段执行。执行EXISTS谓词的代码效率降低了30% [& help;]

这就是为什么在MySQL中搜索缺失值的最佳方法是使用__ABC0 / __ABC1或__ABC2而不是NOT EXISTS。

(重点)

小开

不幸的是，这似乎是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>
小开

注意:NOT IN不是<> ANY的别名，而是<> ALL的别名!

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html < a href = " 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

不能被取代

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

你必须使用

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

提交答案