Should SQL have a compulsory ORDER BY clause?

How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.

Oracle clearly say,

“Use the ORDER BY clause to order rows returned by the statement. Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”

This also applies for the GROUP BY clause, that haunted people during their 10g upgrades.

“The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.”

Both these statements can be found in the docs for the SELECT statements.

So why not make the ORDER BY clause compulsory? The statement could include an explicit NOORDER, like this.

SELECT * FROM emp e ORDER BY e.empname

SELECT * FROM emp e ORDER BY NOORDER

Of course, if that happened I would probably be in the group of people complaining about how ridiculous it is to force us to specify a clause we don’t need, but it would make people think… 🙂

Cheers

Tim…

Update: There seems to be a little confusion about what I am saying. I am not suggesting ordering should be mandatory. I am suggesting the ORDER BY clause could be mandatory, making you have to explicitly say if the output from the query is ordered or not. So these two statements would be functionally identical.

SELECT * FROM emp

SELECT * FROM emp ORDER BY NOORDER