Select without order by

Question

It is my understanding that select is not guaranteed to always return the same result.

Following query is not guaranteed to return the same result every time:

select * from myTable offset 10000 limit 100

My question is if myTable is not changed between executions of select (no deletions or inserts) can i rely on it returning the same result set every time?

Or to put it in another way if my database is locked for changes can I rely on select returning the same result?

I am using postgresql.

No, not even without any changes can you rely on the order. e.g. Postgres has a feature called "synchronized seq scan" which means that two users running the same query share the same "seq scan" process which might yield a different order for each query — , May 17 '16 at 12:55
Why not just add an order and not worry about it anymore? (if what you propose fell on my plate in the wild I'd be not too happy with "whoever t.f. did this...") — jleach, May 17 '16 at 12:56
The same question and the response : http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause — POIR, May 17 '16 at 12:58
I can never understand these sort of questions, unless there's some wierd SQL engines that charge you base on character or keyword counts? "I've got something where I need something done in a particular order, and I've got access to the `ORDER BY` clause that lets me tell the system what I want - but is there a way I can get away without having to type those extra 30 characters?" — Damien_The_Unbeliever, May 17 '16 at 13:13
`(no deletions or inserts)` Updates can also affect the order in which rows are encountered, even non-key updates. — joop, May 17 '16 at 14:46
@Damien_The_Unbeliever, @jleach the reason why I'm looking for an answer to this is because I already performed a delete operation having simply forgotten to add the order_by clause, and I'm trying to work out how much of a mess I've made. The question doesn't say "I don't want to use the `ORDER_BY` clause" so please don't make those assumptions and criticise the OP unfairly. — Toby 1 Kenobi, Dec 11 '18 at 23:44

score 8 · Accepted Answer · answered May 17 '16 at 13:03

Tables and result sets (without order by) are simply not ordered. It really is that simple.

In some databases, under some circumstances, the order will be consistent. However, you should never depend on this. Subsequent releases, for instance, might invalidate the query.

For me, I think the simplest way to understand this is by thinking of parallel processing. When you execute a query, different threads might go out and start to fetch data; which values are returned first depends on non-reproducible factors.

Another way to think of it is to consider a page cache that already has pages in memory -- probably from the end of the table. The SQL engine could read the pages in any order (although in practice this doesn't really happen).

Or, some other query might have a row or page lock, so that page gets skipped when reading the records.

So, just accept that unordered means what ordered means. Add an order by if you want data in a particular order. If you use a clustered index key, then there is basically no performance hit.

Select without order by

1 Answers1