当未指定“Order by"时,查询为您的记录集选择什么顺序?

2021-12-10 00:00:00 sql tsql sql-server sql-order-by

我的印象是,没有指定Order by"规则的查询会根据您的 where 子句中指定的结果对其进行排序.

例如,我的 where 子句说明:

WHERE RESULTS_I_AM_SEARCHING_FOR IN第 1 项第 2 项第 3 项

我本以为第 1、2 和 3 项返回的结果将按照 where 中指定的顺序进行,但事实并非如此.有谁知道它在未指定时按什么顺序对它们进行排序?

感谢并抱歉问这个非常基本的问题!

达蒙

解决方案

如果你没有指定ORDER BY,那么就定义了NO ORDER.>

结果可以以任意顺序返回 - 而且也可能随着时间的推移而改变.

没有自然秩序";或关系数据库中的任何类似内容(至少在我所知道的所有内容中).获得可靠排序的唯一方法是明确指定ORDER BY 子句.

更新:对于那些仍然不相信我的人 - 这里有两篇出色的博客文章可以说明这一点(带有代码示例!):

  • Conor Cunningham(核心 SQL Server 引擎团队的架构师):没有安全带 - 没有 ORDER BY 的期待订单
  • 亚历山大·库兹涅佐夫:没有 ORDER BY,就没有默认的排序顺序(发布在 Web 档案中)

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.

For instance, my where clause states:

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?

Thanks and sorry for the really basic question!

Damon

解决方案

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

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.

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
  • Alexander Kuznetsov: Without ORDER BY, there is no default sort order (post in the Web Archive)

相关文章