主页 / dba / 问题 / 121034
Accepted
Michael Richardson
Asked: 2015-11-14 11:18:12 +0800 CST

使用 LEFT JOIN 或 NOT EXISTS 之间的最佳实践

  • 772

使用 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 数据库使用查询。

sql-server join

4 个回答

  1. Best Answer

    最大的区别不在于加入与不存在，它是（如所写）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);

    执行计划：

    在此处输入图像描述

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

    • 77

  2. 从逻辑上讲，它们是相同的，但NOT EXISTS更接近您要求的 AntiSemiJoin，并且通常是首选。它还更好地突出了您无法访问 B 中的列，因为它仅用作过滤器（而不是使它们可用于 NULL 值）。

    许多年前（SQL Server 6.0 ish）LEFT JOIN速度更快，但很长一段时间以来都不是这种情况。这些天来，NOT EXISTS速度略快。

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

    • 34

  3. 在使用Linked Servers时，我注意到一个NOT EXISTS优于（尽管微乎其微）的例外。LEFT JOIN ... WHERE IS NULL

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

    演示此行为的示例执行计划： 在此处输入图像描述

    • 8

  4. 一般来说，引擎将创建一个执行计划，主要基于：

    1. A和B中的行数
    2. A 和/或 B 上是否有索引。
    3. 预期的结果行数（和中间行）
    4. 输入查询的形式（即你的问题）

    对于 (4)：

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

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

    然而，它只是一种“鼓励”，就像加权输入一样。强大的 (1),(2),(3) 经常使选择 (4) 没有实际意义。

    （由于 *. ，忽略您的示例返回不同列的影响）。

    • 7

相关问题