81

What is the default order of a query when no ORDER BY is used?

Devin Burke
  • 13,642
  • 12
  • 55
  • 82
Vijayan
  • 1,015
  • 2
  • 9
  • 12
  • 2
    I believe it depends on the storage engine and potentially indices. – smp7d Jan 05 '12 at 17:10
  • Helpful answers can be found in similar questions such as [SQL best practice to deal with default sort order](http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order) and [MySQL row order for “SELECT * FROM table_name;”](http://stackoverflow.com/questions/1949641/mysql-row-order-for-select-from-table-name). – Wiseguy Jan 05 '12 at 17:26
  • ORDER BY rowid. – mac Mar 04 '21 at 10:56
  • An useful answer - https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql – Payel Senapati Sep 18 '21 at 07:36
  • Although, generally I find it in ascending order of `PRIMARY KEY`. See - https://stackoverflow.com/questions/21829957/is-a-mysql-primary-key-already-in-some-sort-of-default-order – Payel Senapati Sep 18 '21 at 07:43

4 Answers4

77

There is no such order present. Taken from What is The Default Sort Order of SELECTS with no ORDER BY Clause?

  • Do not depend on order when ORDER BY is missing.

  • Always specify ORDER BY if you want a particular order -- in some situations the engine can eliminate the ORDER BY because of how it does some other step.

  • GROUP BY forces ORDER BY. (This is a violation of the standard. It can be avoided by using ORDER BY NULL.)

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    Great answer, and it is in harmony with http://dba.stackexchange.com/q/6051/877 since there is no guarantee between storage engines and between versions that an ordering is predetermined at all. +1 !!! – RolandoMySQLDBA Jan 05 '12 at 17:26
26

There's none. Depending on what you query and how your query was optimised, you can get any order. There's even no guarantee that two queries which look the same will return results in the same order: if you don't specify it, you cannot rely on it.

alf
  • 8,377
  • 24
  • 45
  • 1
    still, if you know the engine and the existing indexes, you can predict the order =) – newtover Jan 05 '12 at 18:47
  • 2
    @newtover true, but adding `ORDER BY` is so much easier than predicting query plan and recalling the physical layout that I'd suggest sticking to the former :) Nice to meet you here, anyway. – alf Jan 05 '12 at 19:02
  • predicting the order is rather useful when explicit ordering might result in temporary tables and filesorts, though the required order is already there. Nevertheless, explicit is better than implicit. Nice to meet you too :) – newtover Jan 05 '12 at 19:13
  • 2
    @newtover it would be a very fragile design to my taste: I fail to imagine a situation where "explicit ordering might result in temporary tables and filesorts" and at the same time the required order would be already there (i.e. we have properly sorted indexes, but did not use those for sorting?); on the other hand, DB upgrade or storage change can ruin this trick... It would be interesting to see a situation where it pays off. – alf Jan 05 '12 at 19:17
  • 2
    I described an example in a post: http://newtover.tumblr.com/post/15403298770/mysql-on-implicit-order-by – newtover Jan 06 '12 at 17:11
8

I've found SQL Server to be almost random in its default order (depending on age and complexity of the data), which is good as it forces you to specify all ordering.

(I vaguely remember Oracle being similar to SQL Server in this respect.)

MySQL by default seems to order by the record structure on disk, (which can include out-of-sequence entries due to deletions and optimisations) but it often initially fools developers into not bother using order-by clauses because the data appears to default to primary-key ordering, which is not the case!

I was surprised to discovere today, that MySQL 5.6 and 4.1 implicitly sub-order records which have been sorted on a column with a limited resolution in the opposite direction. Some of my results have identical sort-values and the overall order is unpredictable. e.g. in my case it was a sorted DESC by a datetime column and some of the entries were in the same second so they couldn't be explicitly ordered. On MySQL 5.6 they select in one order (the order of insertion), but in 4.1 they select backwards! This led to a very annoying deployment bug.

I have't found documentation on this change, but found notes on on implicit group order in MySQL:

By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well.

However:

Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause.

So in agreement with the other answers - never rely on default or implicit ordering in any database.

scipilot
  • 6,681
  • 1
  • 46
  • 65
3

The default ordering will depend on indexes used in the query and in what order they are used. It can change as the data/statistics change and the optimizer chooses different plans.

If you want the data in a specific order, use ORDER BY