使用联合或联接-更快 [英] Use A Union Or A Join - What Is Faster

问题描述

我只是想知道您是否有一个表并且您对其进行了联合，这样会比使用联接更有效??

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?

推荐答案

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

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.

联合"，我的意思是全部联合"，因为它似乎足以满足您想要实现的目标.尽管一般的联盟通常比加入联盟要快.

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.

我不同意他的看法.从技术上讲，无论您的联接有多好，"JOIN"仍然比纯串联更为昂贵.我在博客 codePERF [net] 上发表了一篇博文来证明这一点.一个>.实际上，它们有两个完全不同的目的，更重要的是要确保索引正确并使用正确的工具进行工作.

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:

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

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

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