MySQL“NOT IN”查询

5 回答

• 286

  不幸的是，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>
  

  回复于 2024-05-15T10:38:03+08:00
• 5

  在MySQL中

  NOT IN与NOT EXISTS与LEFT JOIN / IS为空

  MySQL以及除SQL Server之外的所有其他系统都能够优化LEFT JOIN / IS NULL，以便在找到匹配值后立即返回FALSE，并且它是唯一一个关注此行为的系统 . [...]由于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％[...]这就是为什么在MySQL中搜索缺失值的最佳方法是使用LEFT JOIN / IS NULL或NOT IN而不是NOT EXISTS .

  （重点补充）

  回复于 2024-05-15T10:38:03+08:00
• 35

  小心 NOT IN 不是 <> ANY 的别名，而是 <> ALL ！

  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)
  

  回复于 2024-05-15T10:38:03+08:00
• 7

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

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

  回复于 2024-05-15T10:38:03+08:00
• 156

  子查询选项已经得到解答，但请注意，在许多情况下， 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
  

  回复于 2024-05-15T10:38:03+08:00