“不存在”与“不在”与“左连接”的区别是什么？

在我看来，您可以在 SQL 查询中使用 NOT EXISTS、 NOT IN 或 LEFT JOIN WHERE IS NULL 来做同样的事情。例如:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)


SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)


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

我不确定我是否掌握了所有的语法，但这些是我见过的通用技术。我为什么要选择一个而不是另一个？表演有什么不同吗？哪一个是最快/效率最高的？(如果它取决于实现，那么我什么时候使用每一个?)

110500 次浏览
小开
最佳答案

简而言之:

NOT IN有一点不同: 如果列表中只有一个 NULL，那么它永远不会匹配。

  • 在 MySQL中，NOT EXISTS的效率要低一些

  • 在 SQL Server中，LEFT JOIN / IS NULL的效率较低

  • 在 PostgreSQL中，NOT IN的效率较低

  • 在 Oracle中，所有三种方法都是相同的。

小开

如果数据库善于优化查询，那么前两个查询将被转换为接近第三个查询的查询。

对于像您提到的那些简单情况，应该没有什么差别，因为它们都将作为连接执行。在更复杂的查询中，数据库可能无法连接 not in和 not exists查询。在这种情况下，查询会变得非常慢。另一方面，如果没有可以使用的索引，联接也可能性能很差，因此仅仅因为您使用了联接并不意味着您是安全的。您必须检查查询的执行计划，以确定是否存在任何性能问题。

小开

当需要使用多字段主键在表中插入数据时，考虑到不检查“表中不存在具有‘ such’值的记录”会快得多(我在 Access 中试过，但我认为在任何数据库中都是如此) ，而只是将数据插入表中，多余的记录(按键)不会插入两次。

小开

性能透视图总是避免使用反向关键字，如 NOT IN，NOT EXISTS，..。 因为要检查反向选项，DBMS 需要遍历所有可用的选项并删除反向选项。

小开

假设您避免使用 null，那么它们都是使用标准 SQL 编写 反加入的方法。

一个明显的遗漏是使用 EXCEPT的等效性:

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

注意，在 Oracle 中需要使用 MINUS操作符(可以说是一个更好的名称) :

SELECT a FROM table1
MINUS
SELECT a FROM table2

说到专有语法，可能还有一些非标准的等价物值得研究，这取决于您正在使用的产品，例如 SQL Server 中的 OUTER APPLY(类似于) :

SELECT t1.a
FROM table1 t1
OUTER APPLY
(
SELECT t2.a
FROM table2 t2
WHERE t2.a = t1.a
) AS dt1
WHERE dt1.a IS NULL;

提交答案