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

dmoney picture dmoney · Nov 18, 2013 · Viewed 26.2k times · Source

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

marc_s picture marc_s · Nov 18, 2013

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!) :