联合还是联合所有人,这是一个问题
我有两个查询正在 UNION
在一起,这样我就知道这两个查询之间不会有重复的元素。因此,UNION
和 UNION ALL
将产生相同的结果。
我应该使用哪一个?
I have two queries that I'm UNION
ing together such that I already know there will be no duplicate elements between the two queries. Therefore, UNION
and UNION ALL
will produce the same results.
Which one should I use?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
您应该使用与您正在寻找的内容相匹配的内容。如果要确保没有重复项,请使用
UNION
,否则使用UNION ALL
。仅仅因为您的数据现在会产生相同的结果并不意味着它永远都会。也就是说,
UNION ALL
在任何正常的数据库实现上都会更快,请参阅下面的文章了解示例。但通常情况下,它们是相同的,只是UNION
执行额外的步骤来删除相同的行(正如人们所期望的那样),并且它可能会主导执行时间。You should use the one that matches the intent of what you are looking for. If you want to ensure that there are no duplicates use
UNION
, otherwise useUNION ALL
. Just because your data will produce the same results right now doesn't mean that it always will.That said,
UNION ALL
will be faster on any sane database implementation, see the articles below for examples. But typically, they are the same except thatUNION
performs an extra step to remove identical rows (as one might expect), and it may tend to dominate execution time.我看到您已将这个问题标记为“性能”,因此我认为这是您的首要考虑因素。
UNION ALL
的性能绝对优于UNION
,因为 SQL 不必检查这两个集合是否有重复。除非您需要 SQL 来执行重复检查,否则请始终使用 UNION ALL。
I see that you've tagged this question PERFORMANCE, so I assume that's your primary consideration.
UNION ALL
will absolutely outperformUNION
since SQL doesn't have to check the two sets for dups.Unless you need SQL to perform the duplicate checking for you, always use
UNION ALL
.无论如何我都会使用UNION ALL。即使您知道不会有重复项,但根据您的数据库服务器引擎,它可能不知道这一点。
因此,只是为了向数据库服务器提供额外的信息,以便其查询规划器有更好的选择(可能),请使用
UNION ALL
。话虽如此,如果您的数据库服务器的查询规划器足够聪明,能够从
UNION
子句和表索引中推断出信息,那么结果(性能和语义方面)应该是一样的。无论哪种情况,它都很大程度上取决于您正在使用的数据库服务器。
I would use
UNION ALL
anyway. Even though you know that there are not going to be duplicates, depending on your database server engine, it might not know that.So, just to provide extra information to DB server, in order for its query planner a better choice (probably), use
UNION ALL
.Having said that, if your DB server's query planner is smart enough to infer that information from the
UNION
clause and table indexes, then results (performance and semantic wise) should be the same.Either case, it strongly depends on the DB server you are using.
根据 http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/ 至少在性能方面是这样最好使用 UNION ALL,因为它不会主动区分重复项,因此速度更快
According to http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/ at least for performance it is better to use UNION ALL, since it does not actively distinct duplicates and as such is faster
由于两者不会重复,所以使用 UNION ALL。您不需要检查重复项,UNION ALL 将更有效地执行任务。
Since there will be no duplicates from the two use UNION ALL. You don't need to check for duplicates and UNION ALL will preform the task more efficiently.