Is there an implicit order of rows returned from a database when multiple records have the same values for columns defined in "Order By" clause?

Question

I've been working mostly in Oracle and PostgreSQL databases specifically, but I am curious if there is any common standard. As the title suggests, I have been looking for answers to a question concerning the Order By clause of a SQL statement. For example, if I had some basic table Users:

-------------------------------------------
| id |     name     | birth_date | gender |
-------------------------------------------
|  1 |  xx_coolKid  | 12-DEC-1960|   M    |
-------------------------------------------
|  2 |  [email protected] | 24-JUN-1976|   F    |
-------------------------------------------
|  3 |    NULL?     | 30-AUG-1990|   M    |
-------------------------------------------
|  4 |    JeffR     | 12-DEC-1960|   M    |
-------------------------------------------
|  5 |    lol19     | 12-DEC-1960|   F    |
-------------------------------------------

and I were to run the query:

SELECT * FROM Users ORDER BY gender, birth_date;

Is there any particular order to the rows returned that match on both gender and birth_date? Or is the return order of those matching rows not guaranteed, as if no ORDER BY clause were defined?

Answer 1

The return order is not guaranteed. There is explicitly no standard in this area. The return order when keys have the same value is arbitrary, and may change between runs on the same database on the same data.

Some sort algorithms are stable, meaning that record order on the inputs is an implicit final order key. However, databases do not generally implement stable sorts.

The Microsoft SQL documentation on order by actually explains this quite well:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

(1) The underlying data that is used by the query must not change. That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation. For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

(2) The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

These conditions would be true in most databases.