SQL Server UNION - 默认的 ORDER BY 行为是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/421049/
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
SQL Server UNION - What is the default ORDER BY Behaviour
提问by Ray Booysen
If I have a few UNION Statements as a contrived example:
如果我有几个 UNION 语句作为人为的例子：
SELECT * FROM xxx WHERE z = 1
UNION
SELECT * FROM xxx WHERE z = 2
UNION
SELECT * FROM xxx WHERE z = 3
What is the default order bybehaviour?
默认order by行为是什么？
The test data I'm seeing essentially does not return the data in the order that is specified above. I.e. the data is ordered, but I wanted to know what are the rules of precedence on this.
我看到的测试数据基本上没有按照上面指定的顺序返回数据。即数据是有序的，但我想知道什么是优先规则。
Another thing is that in this case xxx is a View. The view joins 3 different tables together to return the results I want.
另一件事是，在这种情况下，xxx 是一个视图。该视图将 3 个不同的表连接在一起以返回我想要的结果。
回答by DJ.
There is no default order.
没有默认顺序。
Without an Order Byclause the order returned is undefined. That means SQL Server can bring them back in any order it likes.
如果没有Order By子句，返回的订单是未定义的。这意味着 SQL Server 可以按照它喜欢的任何顺序将它们带回来。
EDIT: Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there is an index that it is using, the result may come back in that order but again there is no guarantee.
编辑：根据我所看到的，没有 Order By，结果返回的顺序取决于查询计划。因此，如果它正在使用一个索引，结果可能会按该顺序返回，但同样无法保证。
回答by Will Rickards
In regards to adding an ORDER BY clause:
关于添加 ORDER BY 子句：
This is probably elementary to most here but I thought I add this. Sometimes you don't want the results mixed, so you want the first query's results then the second and so on. To do that I just add a dummy first column and order by that. Because of possible issues with forgetting to alias a column in unions, I usually use ordinals in the order by clause, not column names.
这对这里的大多数人来说可能是基本的，但我想我添加了这个。有时您不希望结果混合，所以您希望第一个查询的结果然后第二个等等。为此，我只需添加一个虚拟的第一列并按此排序。由于忘记在联合中为列设置别名可能会出现问题，我通常在 order by 子句中使用序数，而不是列名。
For example:
例如：
SELECT 1, * FROM xxx WHERE z = 'abc'
UNION ALL
SELECT 2, * FROM xxx WHERE z = 'def'
UNION ALL
SELECT 3, * FROM xxx WHERE z = 'ghi'
ORDER BY 1
The dummy ordinal column is also useful for times when I'm going to run two queries and I know only one is going to return any results. Then I can just check the ordinal of the returned results. This saves me from having to do multiple database calls and most empty resultset checking.
当我要运行两个查询并且我知道只有一个会返回任何结果时，虚拟序数列也很有用。然后我可以检查返回结果的序数。这使我不必进行多次数据库调用和大多数空结果集检查。
回答by Ray Booysen
Just found the actual answer.
刚刚找到了真正的答案。
Because UNION removes duplicates it does a DISTINCT SORT. This is done before all the UNION statements are concatenated (check out the execution plan).
因为 UNION 会删除重复项，所以它会执行 DISTINCT SORT。这是在连接所有 UNION 语句之前完成的（查看执行计划）。
To stop a sort, do a UNION ALL and this will also not remove duplicates.
要停止排序，请执行 UNION ALL，这也不会删除重复项。
回答by BradC
If you care what order the records are returned, you MUST use an order by.
如果您关心记录返回的顺序，则必须使用 order by。
If you leave it out, it may appearorganized (based on the indexes chosen by the query plan), but the results you see today may NOT be the results you expect, and it could even change when the same query is run tomorrow.
如果您忽略它，它可能看起来是有组织的（基于查询计划选择的索引），但是您今天看到的结果可能不是您期望的结果，甚至在明天运行相同的查询时它甚至可能会发生变化。
Edit: Some good, specific examples: (all examples are MS SQL server)
编辑：一些好的、具体的例子：（所有例子都是 MS SQL 服务器）
Dave Pinal's blogdescribes how two very similar queries can show a different apparent order, because different indexes are used:
SELECT ContactID FROM Person.Contact SELECT * FROM Person.ContactConor Cunninghamshows how the apparent order can change when the table gets larger (if the query optimizer decides to use a parallel execution plan).
Hugo Kornelisproves that the apparent order is notalways based on primary key. Here is his follow-up postwith explanation.
Dave Pinal 的博客描述了两个非常相似的查询如何显示不同的表观顺序，因为使用了不同的索引：
SELECT ContactID FROM Person.Contact SELECT * FROM Person.ContactConor Cunningham展示了当表变大时表观顺序如何变化（如果查询优化器决定使用并行执行计划）。
Hugo Kornelis证明了明显的顺序并不总是基于主键。这是他的后续帖子，并附有解释。
回答by David Aldridge
A UNION can be deceptive with respect to result set ordering because a database will sometimes use a sort method to provide the DISTINCT that is implicit in UNION , which makes it look like the rows are deliberately ordered -- this doesn't apply to UNION ALL for which there is no implicit distinct, of course.
UNION 在结果集排序方面可能具有欺骗性，因为数据库有时会使用排序方法来提供 UNION 中隐含的 DISTINCT，这使得行看起来像是故意排序——这不适用于 UNION ALL当然，没有隐含的不同。
However there are algorithms for the implicit distinct, such as Oracle's hash method in 10g+, for which no ordering will be applied.
然而，对于隐式不同的算法，例如 10g+ 中的 Oracle 散列方法，将不应用排序。
As DJ says, always use an ORDER BY
正如 DJ 所说，始终使用 ORDER BY
回答by Cruachan
It's very common to come across poorly written code that assumes table data is returned in insert order, and 95% of the time the coder gets away with it and is never aware that this is a problem as on many common databases (MSSQL, Oracle, MySQL). It is of course a complete fallacy and should alwaysbe corrected when it's come across, and always, without exception, use an Order By clause yourself.
遇到假设表数据按插入顺序返回的编写糟糕的代码是很常见的，并且编码人员在 95% 的情况下逃脱了它并且从未意识到这是许多常见数据库（MSSQL、Oracle、 MySQL）。这当然是一个完全的谬论，在遇到它时应该始终予以纠正，并且始终无一例外地自己使用 Order By 子句。
