当没有指定“Order by”时，查询为您的记录集选择什么顺序?

I was always of the impression that a query with no specified 'Order by' rule, would order this by the results by what was specified within your where clause.

我一直认为，如果查询没有指定的“Order by”规则，那么它将按照where子句中指定的内容按结果排序。

For instance, my where clause states:

例如，我的where子句状态:

WHERE RESULTS_I_AM_SEARCHING_FOR IN
ITEM 1
ITEM 2
ITEM 3

I would have imagined that the results returned for items 1, 2 and 3 would be in the order specified in the where, however this is not the case. Does anyone know what order it sorts them in when not specified?

我本以为第1、2和3项的结果会按照where中指定的顺序返回，但事实并非如此。有人知道它在未指定的时候是按什么顺序排序吗?

Thanks and sorry for the really basic question!

谢谢你，很抱歉问了这个最基本的问题!

Damon

达蒙

3 个解决方案

#1

If you don't specify an ORDER BY, then there is NO ORDER defined.

如果没有指定ORDER BY，则没有定义ORDER。

The results can be returned in an arbitrary order - and that might change over time, too.

结果可以以任意顺序返回——而且随着时间的推移，这种情况也会改变。

There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY clause.

在关系数据库中没有“自然顺序”或类似的东西(至少在我所知道的所有数据库中没有)。获得可靠排序的唯一方法是显式地指定ORDER by子句。

Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :

更新:对于那些仍然不相信我的人——这里有两个很好的博文说明了这一点(用代码示例!)

Conor Cunningham (Architect on the Core SQL Server Engine team): No Seatbelt - Expecting Order without ORDER BY
康纳·坎宁安(核心SQL Server引擎团队的架构师):没有安全带——没有订单
Alexander Kuznetsov: Without ORDER BY, there is no default sort order
Alexander Kuznetsov:没有订单，就没有默认的排序顺序。

#2

With SQL Server, if no ORDER BY is specified, the results are returned in the quickest way possible.

对于SQL Server，如果没有指定ORDER BY，则以最快的方式返回结果。

Therefore without an ORDER BY, make no assumptions about the order.

因此，在没有订单的情况下，不要对订单做任何假设。

#3

As it was already said you should never rely on the "default order" because it doesn't exist. Anyway if you still want to know some curious details about sql server implementation you can check this out:

正如它已经说过的，你不应该依赖“默认顺序”，因为它不存在。无论如何，如果您还想了解sql server实现的一些奇怪细节，您可以查看以下内容:

http://exacthelp.blogspot.co.uk/2012/10/default-order-of-select-statement-in.html

#1