Does DB2 order data by the primary key

Question

I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key.

After a lengthy discussion in which I tried to explain that he can't guarantee ordering based on the primary key the final conclusion was that he wasn't going to push for the MSSQL queries to be changed.
But he was still going to change the DB2 queries.

I couldn't immediately find an article disproving that DB2 orders queries by the primary key, and am currently wondering whether or not it does.

So my question is, how does DB2 order a query if there is no order by clause? Does it use the primary key?
How can you guarantee data is coming out ordered correctly, without an order by clause, in a parallel system?

Answer 1

No, your colleague is wrong.

All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query.

As the IBM DB2 docs mention:

Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.

Answer 2

As was pointed out in ypercube's answer, when there is no ORDER BY clause there is no defined order.

What I would like to add is that it's important to realise that SQL is very much an abstraction, it does not specify step by step what the DBMS is to do but rather specifies your requirements of the end result.

This implies that if the data is already looked up in a way that ensures the correct order, this is so because the DBMS decided on such a strategy and it will then already know that no additional sorting work is necessary to meet the requirements of the ORDER BY clause.

On the other hand, if you do not specify that the order is significant to you (even though it actually is?), the DBMS will take this into account and if it finds a different strategy that is more efficient to find the right data in some other order it will take advantage of the fact that you apparently did not care about the order. (The chosen strategy could very well change over time as well, based on increasing amount of data in the relevant tables, software patches, etc.)

Ie, removing the ORDER BY clause has very little upside (slightly smaller query size) but potentially huge downside if the order is actually important. Especially so, as this could very well work with the test dataset and get through acceptance testing only to cause serious problems down the line.