0

Let us say we are using more than three left joins in a query. We execute the query at time T1 and get the result which is R number of records.

Is there a possibility where the same query executed at time T2 yields a result that has the same R number of records but with the order of the records changed?

Assume that the data and order of records in the table remains the same.

I have never seen this happening, but is there a possibility for the order of records to change when working with a large number of records?

Sanz
  • 21
  • 1
  • 5
    It really is a very simple rule to learn, but people nevertheless always seem to be looking for some "hidden" secret they can rely on instead. The **only** thing that guarantees you the ordering of a result set is *the `ORDER BY` clause you specify when querying that result set*. – Damien_The_Unbeliever Jun 08 '21 at 08:39
  • 1
    Yes, unless you specify an `ORDER BY` the engine is completely free to return the records in whatever order is convenient at that time. This depends on number of records, indices, server activity and wind direction, to name a few. – HoneyBadger Jun 08 '21 at 08:40
  • 1
    In Postgres (and I think Oracle as well) two statements running at the same time could return the rows in different order if one re-uses a table scan of the other by sharing the same I/O workers. –  Jun 08 '21 at 09:00
  • There is no "order of the records". What do you mean by that, justified by what research or authoritative research? This is a faq. – philipxy Jun 08 '21 at 11:23

2 Answers2

0

The best solution to your current problem is to not even consider relying on any perceived internal order to your SQL tables, because there isn't any such order. Instead, you should always use a proper ORDER BY clause at the end of your query if you want to sort your data a certain way.

Regarding your direct question, I suppose it might be possible that in between your select queries, the index structure(s) of your table(s) could be reorganized such that the default ordering changes. Or another process could come in and insert/update/delete data. But again, don't even bother trying to plan for this, and instead use an ORDER BY clause.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Is there a possibility where the same query executed at time T2 yields a result that has the same R number of records but with the order of the records changed?

Absolutely! This is not just a possibility, it is how the SQL language is defined. SQL tables and results sets represent unordered (multi)sets. The only exception is for a result set generated by a query that has an explicit ORDER BY in the outermost query.

When does this occur? Actually, it occurs regularly on any query running on largish tables in a parallel environment. In a parallel environment, the work is distributed to nodes and when the nodes complete, the results are gathered and returned to the user. Further, the algorithms used to run the query might incorporate randomness for distributing records to the nodes.

In practice, you never see a difference on queries on small tables. These normally fit on a single data page and SQL engines are only using the simplest processing.

If you care about the ordering of the result set, then include ORDER BY. If you just want to see if the query returns the same results as the last time it was run, then use ORDER BY, so you can easily compare row-by-row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786