1

I know that if an SQL query is not sorted by an Order By argument, it's not sorted at all. When running a query without an Order By, I'd expect to see the same results in a differing order.

However, the results are still returned in an order (i.e. there is a row 1, a row 2 and so on), just not a dependable one - so if they're not sorted, what determines (presumably on a case-by-case basis) the order in which rows are returned.

Specifically, my experience is with MySQL, but I'm curious about this from a theoretical point, rather than trying to solve a specific problem.

Edit: I always use an Order By if I actually want to order a query - this is just curiosity.

Sinister Beard
  • 3,570
  • 12
  • 59
  • 95
  • 3
    Not a downvoter, but possible duplicate of [The order of a SQL Select statement without Order By clause](http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause) and many others – LittleBobbyTables - Au Revoir Dec 08 '14 at 14:27
  • @LittleBobbyTables - Thanks, but that's asking if there is a hidden default order. I know there isn't, but I'm just curious about what determines the order of the rows given that - is it just the database equivalent of 52 card pickup? All of the similar questions to this just say "add an Order By", which of course I do in real life - this is just me wanting to understand the theory behind it, rather than fix it. Also, nice username! – Sinister Beard Dec 08 '14 at 14:36
  • 2
    Also not a downvoter, but this is probably a better question for the DBA stack exchange, since it isn't about programming. – Tab Alleman Dec 08 '14 at 14:38
  • 1
    What you are asking is officially undefined behavior, which allows every implementer to return rows in the cheapest way possible, ie in the way the rows come after processing by all previous stages (filters, joins, aggregates, whatever). There's no guarantee they will com in physical, index or any other order. – Panagiotis Kanavos Dec 08 '14 at 14:39

3 Answers3

2

For the obvious reason database vendors do not publish information about what order rows in an unordered result set will appear in (it would only lead to people relying on the unreliable current implementation).

Common sense, however, tells us that the most likely factors contributing to apparent order would include:

  1. Sequential order of the rows in whatever format they're stored in (which might be clustered index, row-added order for databases that store invariant length segments of records in SAM type files or use a variable-length mechanism like XML, or row-added-except-for-moved-rows for database that store variable length records and move them to the end of the storage file when the row size increases), at least for searches requiring a table scan.
  2. Index order for instances in which a covering index exists and the actual table is never consulted.
  3. One or the other of the above for indexed-based searches that require additional data from the table depending on the implementation.
Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
1

The answer as to what determines the row order when no ORDER BY is specified is: a lot of internal considerations that can depend on how the data is stored on disk (or in-memory), what types of operations the db does to execute your query (e.g. JOINs), and many other factors that can vary by database engine.

Bottom-line is that the SQL standards all say that the dbms does not have to guarantee anything at all about ordering result sets unless you use ORDER BY. So, there is no theoretical default. If you need to have your data sorted in a certain way, always use ORDER BY.

SlimsGhost
  • 2,849
  • 1
  • 10
  • 16
0

It depends on the database: under some curcunmstances SQL Server will sort on the clustered index if there is no ORDER BY specified, as that approximates to how the rows are stored on disk.

With other DBs, all bets are off, and you can see why: even though other vendors do not necessarily adopt the same mechanism as SQL Server, they certainly want to have the freedom to re-engineer stuff at disc/buffer/log/block level without having to re-write parts of the datase/query-parsing engine.

And secondly, SQL as a declarative language has an agreement with you, the user, that you tell it what you want, and it decides how to get it. If you don't specify sorting, then you cannot make any assumption about the order of the data you get back.

EDIT: added in proviso pointed out in the comments.

davek
  • 22,499
  • 9
  • 75
  • 95
  • *"SQL Server sorts on the clustered index if there is no ORDER BY specified"* [No, it doesn't](http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx). – Mike Sherrill 'Cat Recall' Dec 08 '14 at 14:54
  • @Mike: thank you for that link: I stand corrected! Just out of interest: Was that *ever* the case, or is it just one of those of myths that get perpetuated out of sloppiness (as I have demonstrated above:) ) – davek Dec 08 '14 at 15:01
  • Just a myth. You can build tables and queries that *often* return rows in clustered index order without an `order by` clause. That's enough to perpetuate the myth. – Mike Sherrill 'Cat Recall' Dec 08 '14 at 15:24