在使用LEFT JOIN或NOT EXISTS格式之间有最佳实践吗？

与另一种格式相比有什么好处？

如果没有，则应首选？ 

SELECT *
FROM tableA A
LEFT JOIN tableB B
     ON A.idx = B.idx
WHERE B.idx IS NULL




SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)


我在Access中使用SQL Server数据库查询。

评论

顺便说一句，由于NULL的三价行为，看似相同的方法WHERE A.idx NOT IN（...）是不相同的（即NULL不等于NULL（也不等于），因此，如果tableB您会得到意想不到的结果！）

#1 楼

最大的区别不是联接与不存在，而是（如所写的）SELECT *。在第一个示例中，您从A和B都获得了所有列，而在第二个示例中，则只能从A中获得列。

在SQL Server中，在一个非常简单的示例中，第二种变体稍微快一点：

创建两个示例表：

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO


在每个表中插入10,000行：

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000


从第二个表中删除第5行：

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;


执行两个测试SELECT语句变体：

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);


执行计划：



第二个变体不需要执行过滤操作，因为它可以使用左反半联接运算符。

#2 楼

从逻辑上讲，它们是相同的，但是NOT EXISTS更接近您所要求的AntiSemiJoin，通常是首选。它还突出显示了您无法访问B中的列，因为它仅用作过滤器
（与使它们具有NULL值相反）。

很多年前（ SQL Server 6.0（ish），LEFT JOIN速度更快，但很长一段时间以来并非如此。如今，NOT EXISTS略快一些。


Access中的最大影响是JOIN方法必须先完成连接，然后才能对其进行过滤，从而在内存中构造连接集。使用NOT EXISTS会检查行，但不会为列分配空间。另外，一旦找到行，它就会停止寻找。 Access中的性能差异更大，但是一般的经验法则是NOT EXISTS往往会更快一些。我不愿意说这是“最佳实践”，因为涉及更多因素。

评论

“ Access的最大影响是JOIN方法必须在过滤之前完成连接。”这也适用于SQL Server（2012），并且当表B有很多行时，LEFT JOIN的乘法性质可以从字面上看，吹牛的方式不成比例：在一个测试案例中，我发现我的表A（约3000行）和表B（约25万行）产生了超过7.5亿（！）行的组合。使用NOT EXISTS，可以预先应用过滤，并且总结果集不会超过两个表中的行数。

–汤姆·林特（Tom Lint）
6月12日11:53

#3 楼

我注意到NOT EXISTS优于LEFT JOIN ... WHERE IS NULL的一个例外是（在边际上）在使用链接服务器时。

从检查执行计划来看，似乎NOT EXISTS运算符以嵌套循环的方式执行。因此，它是按行执行的（我认为这很有意义）。

示例执行计划演示了此行为：


评论

链接服务器对于这种事情是残酷的。解决该问题的一种可能方法是使用简单的INSERT INTO #t（a，b，c）在LinkedServer.database.dbo.table中选择a，b，c，通过链接服务器链接复制远程数据，其中x = y然后针对该数据库的临时副本运行NOT EXISTS（...）子句。

– Max Vernon♦
18年8月30日在14:36

#4 楼

通常，引擎将基本上基于以下内容来创建执行计划：

A和B中的行数
是否在A和/或B上有索引。
结果行（和中间行）的预期数量
输入查询的形式（即您的问题）

对于（4）：

“不存在”计划鼓励在表B上进行基于搜索的计划。当表A小而表B大（并且B上存在索引）时，这是一个不错的选择。

当表A很大或表B很小或B上没有索引并返回大结果集时，“ antijoin”计划是一个不错的选择。

它只是一个“鼓励”，例如a加权输入。强（1），（2），（3）通常会选择（4）辩论。

（忽略示例由于*而返回不同列的效果，@ MaxVernon解决了答案。）。