When no 'Order by' is specified, what order does a query choose for your record set?

Question

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

Answer 1

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)