SQL LEFT INNER JOIN vs. LEFT OUTER JOIN - 为什么 OUTER 需要更长的时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2389204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT INNER JOIN vs. LEFT OUTER JOIN - Why does the OUTER take longer?
提问by Randy Minder
We have the query below. Using a LEFT OUTER join takes 9 seconds to execute. Changing the LEFT OUTER to an LEFT INNER reduces the execution time to 2 seconds, and the samenumber of rows are returned. Since the same number of rows from the dbo.Accepts table are being processed, regardless of the join type, why would the outer take 3x longer?
我们有下面的查询。使用 LEFT OUTER 连接需要 9 秒来执行。将 LEFT OUTER 更改为 LEFT INNER 会将执行时间减少到 2 秒，并且返回相同数量的行。由于正在处理 dbo.Accepts 表中相同数量的行，无论连接类型如何，为什么外部需要 3 倍的时间？
SELECT CONVERT(varchar, a.ReadTime, 101) as ReadDate,
a.SubID,
a.PlantID,
a.Unit as UnitID,
a.SubAssembly,
m.Lot
FROM dbo.Accepts a WITH (NOLOCK)
LEFT OUTER Join dbo.Marker m WITH (NOLOCK) ON m.SubID = a.SubID
WHERE a.LastModifiedTime BETWEEN @LastModifiedTimeStart AND @LastModifiedTimeEnd
AND a.SubAssembly = '400'
回答by Remus Rusanu
The fact that the same number of rows is returned is an after fact, the query optimizer cannot know in advance that every row in Accepts has a matching row in Marker, can it?
返回相同行数的事实是事后的，查询优化器不可能提前知道Accepts中的每一行在Marker中都有一个匹配的行，可以吗？
If you join two tables A and B, say A has 1 million rows and B has 1 row. If you say A LEFT INNER JOIN B it means only rows that match bothA and B can result, so the query plan is free to scan B first, then use an index to do a range scan in A, and perhaps return 10 rows. But if you say A LEFT OUTER JOIN B then at leastall rows in A have to be returned, so the plan must scan everything in A no matter what it finds in B. By using an OUTER join you are eliminating one possible optimization.
如果连接两个表 A 和 B，假设 A 有 100 万行，B 有 1 行。如果您说 A LEFT INNER JOIN B 则意味着只有同时匹配A 和 B 的行才能产生结果，因此查询计划可以自由地先扫描 B，然后使用索引在 A 中进行范围扫描，并且可能返回 10 行。但是，如果您说 A LEFT OUTER JOIN B，那么至少必须返回 A 中的所有行，因此无论在 B 中找到什么，计划都必须扫描 A 中的所有内容。通过使用 OUTER join，您正在消除一种可能的优化。
If you do knowthat every row in Accepts will have a match in Marker, then why not declare a foreign key to enforce this? The optimizer will see the constraint, and if is trusted, will take it into account in the plan.
如果您确实知道Accepts 中的每一行都将在 Marker 中匹配，那么为什么不声明一个外键来强制执行此操作呢？优化器将看到约束，如果受信任，将在计划中考虑它。
回答by KM.
1) in a query window in SQL Server Management Studio, run the command:
1) 在 SQL Server Management Studio 的查询窗口中，运行以下命令：
SET SHOWPLAN_ALL ON
SET SHOWPLAN_ALL ON
2) run your slow query
2）运行你的慢查询
3) your query will not run, but the execution plan will be returned. store this output
3）您的查询不会运行，但会返回执行计划。存储这个输出
4) run your fast version of the query
4）运行查询的快速版本
5) your query will not run, but the execution plan will be returned. store this output
5）您的查询不会运行，但会返回执行计划。存储这个输出
6) compare the slow query version output to the fast query version output.
6) 比较慢查询版本输出和快速查询版本输出。
7) if you still don't know why one is slower, post both outputs in your question (edit it) and someone here can help from there.
7）如果您仍然不知道为什么一个更慢，请在您的问题中发布两个输出（编辑它），这里有人可以从那里提供帮助。
回答by NebuSoft
This is because the LEFT OUTER Join is doing more work than an INNER Join BEFORE sending the results back.
这是因为在将结果发回之前，LEFT OUTER Join 比 INNER Join 做了更多的工作。
The Inner Join looks for all records where the ON statement is true (So when it creates a new table, it only puts in records that match the m.SubID = a.SubID). Then it compares those results to your WHERE statement (Your last modified time).
Inner Join 查找 ON 语句为真的所有记录（因此当它创建一个新表时，它只放入匹配 m.SubID = a.SubID 的记录）。然后它将这些结果与您的 WHERE 语句（您的上次修改时间）进行比较。
The Left Outer Join...Takes all of the records in your first table. If the ON statement is not true (m.SubID does not equal a.SubID), it simply NULLS the values in the second table's column for that recordset.
左外连接...获取第一个表中的所有记录。如果 ON 语句不正确（m.SubID 不等于 a.SubID），则它只是将第二个表的该记录集的列中的值设为 NULL。
The reason you get the same number of results at the end is probably coincidence due to the WHERE clause that happens AFTER all of the copying of records.
最后得到相同数量结果的原因可能是巧合，因为 WHERE 子句发生在所有记录复制之后。
回答by MJB
Wait -- did you actually mean that "the same number of rows ... are being processed" or that "the same number of rows are being returned"? In general, the outer join would process many more rows, including those for which there is no match, even if it returns the same number of records.
等等——您的意思是“正在处理相同数量的行...... ”还是“正在返回相同数量的行”？通常，外连接将处理更多的行，包括那些没有匹配的行，即使它返回相同数量的记录。
