NOT IN和不存在

下面哪个查询更快?

不存在:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

或不在:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

查询执行计划表示它们都做相同的事情。如果是这样的话，推荐哪种形式?

这是基于北风的数据库。

(编辑)

刚找到这篇有用的文章: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

我想我还是用“不存在”吧。

当前回答

数据库表模型

让我们假设我们的数据库中有以下两个表，它们形成了一对多表关系。

student表是父表，student_grade是子表，因为它有一个student_id外键列引用了student表中的id主键列。

student表包含以下两条记录:

id	first_name	last_name	admission_score
1	Alice	Smith	8.95
2	Bob	Johnson	8.75

并且，student_grade表存储了学生获得的分数:

id	class_name	grade	student_id
1	Math	10	1
2	Math	9.5	1
3	Math	9.75	1
4	Science	9.5	1
5	Science	9	1
6	Science	9.25	1
7	Math	8.5	2
8	Math	9.5	2
9	Math	9	2
10	Science	10	2
11	Science	9.4	2

SQL的存在

假设我们想要所有在数学课上得到10分的学生。

如果我们只对学生标识符感兴趣，那么我们可以运行这样的查询:

SELECT
    student_grade.student_id
FROM
    student_grade
WHERE
    student_grade.grade = 10 AND
    student_grade.class_name = 'Math'
ORDER BY
    student_grade.student_id

但是，应用程序感兴趣的是显示学生的全名，而不仅仅是标识符，所以我们还需要学生表中的信息。

为了过滤数学成绩为10的学生记录，我们可以使用EXISTS SQL操作符，如下所示:

SELECT
    id, first_name, last_name
FROM
    student
WHERE EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'
)
ORDER BY id

当运行上面的查询时，我们可以看到只有Alice行被选中:

id	first_name	last_name
1	Alice	Smith

外部查询选择我们感兴趣的学生行列返回给客户端。然而，WHERE子句使用EXISTS操作符和关联的内部子查询。

如果子查询返回至少一条记录，EXISTS操作符返回true，如果没有选择行，则返回false。数据库引擎不必完全运行子查询。如果匹配到一条记录，EXISTS操作符将返回true，并选择相关联的其他查询行。

内部子查询是相关的，因为student_grade表的student_id列与外部student表的id列相匹配。

SQL不存在

假设我们要选择所有成绩不低于9分的学生。为此，我们可以使用NOT EXISTS，它否定EXISTS操作符的逻辑。

因此，如果底层子查询没有返回记录，NOT EXISTS操作符将返回true。但是，如果内部子查询匹配了一条记录，NOT EXISTS操作符将返回false，并且可以停止子查询的执行。

要匹配所有没有关联student_grade值低于9的学生记录，我们可以运行下面的SQL查询:

SELECT
    id, first_name, last_name
FROM
    student
WHERE NOT EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

当运行上面的查询时，我们可以看到只有Alice记录被匹配:

id	first_name	last_name
1	Alice	Smith

因此，使用SQL EXISTS和NOT EXISTS操作符的优点是，只要找到匹配的记录，就可以停止内部子查询的执行。

2020-01-28 10:27:43

其他回答

它们非常相似，但并不完全相同。

在效率方面，我发现左join is null语句更有效(当要选择大量行时)

2018-03-19 08:27:30

我在用

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

发现它给出了错误的结果(我说的错误是指没有结果)。因为在TABLE2.Col1中有一个NULL。

将查询更改为

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

给了我正确的结果。

从那时起，我开始在任何地方使用NOT EXISTS。

2013-06-13 15:02:31