SQL Server UNION - 默认的 ORDER BY 行为是什么 [英] SQL Server UNION - What is the default ORDER BY Behaviour

问题描述

如果我有几个 UNION 语句作为人为的例子:

If I have a few UNION Statements as a contrived example:

SELECT * FROM xxx WHERE z = 1
UNION 
SELECT * FROM xxx WHERE z = 2
UNION
SELECT * FROM xxx WHERE z = 3

默认的order by行为是什么?

What is the default order by behaviour?

我看到的测试数据基本上没有按照上面指定的顺序返回数据.IE.数据是有序的，但我想知道这方面的优先规则是什么.

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.

另一件事是，在这种情况下，xxx 是一个视图.该视图将 3 个不同的表连接在一起以返回我想要的结果.

Another thing is that in this case xxx is a View. The view joins 3 different tables together to return the results I want.

推荐答案

没有默认顺序.

如果没有 Order By 子句，返回的顺序是未定义的.这意味着 SQL Server 可以按照它喜欢的任何顺序将它们带回来.

Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes.

根据我所看到的，如果没有 Order By，结果返回的顺序取决于查询计划.因此，如果它正在使用一个索引，结果可能会按该顺序返回，但同样无法保证.

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.

这篇关于SQL Server UNION - 默认的 ORDER BY 行为是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！