What is the default sorting (order) of the result of an SQL query?

Question

Suppose I have a table called "student" in the database, and its columns are nome, matricula and curso, your primary key being the column matricula. The column matricula does not have auto_increment and a larger registration number does not necessarily mean that it was entered after a smaller registration number. Example: registration number 201033 was inserted after 201941. If I turn the remote:

SELECT * FROM aluno;

I know all records are returned, but the first row returned is the row that was inserted first and the last returned is the last that was inserted? What is the default order of the query result? In this specific case, it would be possible to order the query by insertion order, since I do not have a field in the table that represents the order in which the lines (tuples) were inserted?

Answer 1

The pattern SQL does not guarantee that the recovered data has a standard ordering. Without a ORDER BY specific, the order of its results will always be undetermined.

In the MySQL, the order of columns which are not part of the clause ORDER BY is determined by the manner in which the planner/optimizer decided to create the result set to be returned.

Simple queries like SELECT * FROM tabela are likely to be returned in the same order in which they were stored on the disk, and may be ordered by the primary key, in the order in which they were inserted, or in any other random order.

The rule is: NEVER trust the order pattern, it can change from night to day! Use at all times the clause ORDER BY whether sorting data is important to you.

References:

https://stackoverflow.com/questions/8746519/sql-what-is-the-default-order-by-of-queries

https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

https://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775

https://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order

https://en.wikipedia.org/wiki/Order_by

Answer 2

The result will appear in order that comes, depending on the operations you do in the table, the order may not be the same as the insertion order.

To ensure that the records will come in the insertion order it is necessary to have a field with AUTO INCREMENT.

An analogy is the concept of set in mathematics (SET or BAG). In sets, the order of elements is irrelevant. Therefore there is the concept of ordered set: each element of the set is an ordered pair containing an integer and the element itself. And the order is established by order of the whole number. The AUTO INCREMENT or SEQUENCE, in the case of the bank, establish the order list.

---

In fact the order is not indeterminate, it is deterministic. However, in some cases, it would take a super computer to predict the result of the query. For practical purposes it shall be considered undetermined.

This phenomenon is called deterministic chaos.

https://en.wikipedia.org/wiki/Chaos_theory

Small differences in initial conditions (such as those caused by rounding errors in numerical computing) produce widely divergent results for such dynamic systems, making long-term forecasting generally impossible. [2] This happens despite these systems being deterministic, which means that their future behavior is totally determined by their initial conditions, with no random elements involved. [3] In other words, the deterministic nature of these systems does not make them predictable. [4][5] This behavior is known as deterministic chaos, or simply chaos.