使用联合或加入 - 哪个更快 [英] Use A Union Or A Join - What Is Faster

问题描述

我只是想知道如果你有一张桌子并且你联合了它会比使用连接更有效吗??

我确实知道连接会创建更多列，但这更具理论性 - 联合是否需要像连接那样对另一个表进行嵌套循环扫描?

解决方案

Union 会更快，因为它只是传递第一个 SELECT 语句，然后解析第二个 SELECT 语句并将结果添加到输出表的末尾.

Join 将遍历两个表的每一行，在另一个表中查找匹配项，因此由于为每一行搜索匹配的行，因此需要更多的处理.

编辑

Union，我的意思是 Union All，因为它似乎足以满足您想要实现的目标.虽然普通的 Union 通常比 Join 快.

编辑 2(回复@seebiscuit 的评论)

我不同意他的观点.从技术上讲，无论您的连接有多好，JOIN"仍然比纯连接更昂贵.我在我的博客

JOIN 执行计划

实际结果

实际上，聚集索引查找的差异可以忽略不计:

I just wonder if you had a table and you unioned it would it be more efficent then using a join??

I do know that the join creates more columns but this is more theoretical - Will the union need to do a nested loop scan of the other table like a join would have to?

解决方案

Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.

The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching for matching rows for each and every row.

EDIT

By Union, I mean Union All as it seemed adequate for what you were trying to achieve. Although a normal Union is generally faster then Join.

EDIT 2 (Reply to @seebiscuit 's comment)

I don't agree with him. Technically speaking no matter how good your join is, a "JOIN" is still more expensive than a pure concatenation. I made a blog post to prove it at my blog codePERF[dot]net. Practically speaking they serve 2 completely different purposes and it is more important to ensure your indexing is right and using the right tool for the job.

Technically, I think it can be summed using the following 2 execution plans taken from my blog post:

UNION ALL Execution Plan

JOIN Execution Plan

Practical Results

Practically speaking the difference on a clustered index lookup is negligible:

这篇关于使用联合或加入 - 哪个更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！