Full version first published on my blog.
A common misconception is that rows in a relational database table has some inherent order. This is not true.
So many developers falls victim of this misconception, even though it’s being repeatedly refuted in multiple professional blogs and forums that I figured it can’t hurt to write about that in one more place.
I want to make this absolutely clear:
In the relational model published by E.F. Codd, relation are defined as an unsorted set of tuples.
In relational databases, relations are implemented as tables, and tuples are implemented as rows, and while it is true that rows are stored inside the database in a specific order,
there is no relational database that guarantees the order of the rows returned from a
select statement without an
order by clause.
Even if you do specify an
order by clause, if the values of the column(s) listed in the
order by clause are not unique, there order of the rows where such duplicate values exists is also arbitrary.
A quick example (T-SQL):
CREATE TABLE Demo ( Id int NOT NULL, Val char(1) NOT NULL ); INSERT INTO Demo(Id, Val) VALUES (1, 'A'), (2, 'A'), (3, 'B'), (4, 'C'); -- 1: -- No order by clause at all SELECT Id, Val FROM Demo; -- 2: -- Order by a constant SELECT Id, Val FROM Demo ORDER BY @@SPID -- In SQL Server, @@SPID Returns the session ID of the current user process. -- This order the rows using a constant value. -- 3: -- Order by a non-unique value SELECT Id, Val FROM Demo ORDER BY Val;
In examples 1 and 2 above, (no order by and order by a constant) are completely equivalent - the database is free to return the rows at any order.
In example 3 there's a column with duplicate values (at least for some of the rows) in the
order by clause - so the rows that contains the duplicate value are arbitrarily ordered - there are two legitimate results for this query:
Id Val 1 A 2 A 3 B 4 C
Id Val 2 A 1 A 3 B 4 C
can be returned by the database.
Please note that this rule also holds true for window functions such as