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

Author: Tim...

DBA, Developer, Author, Trainer.

9 thoughts on “Should SQL have a compulsory ORDER BY clause?”

  1. Nice idea. If we could redefine the SQL standard, the redundant BY could be dropped and it could be

    SELECT name, deptno FROM emp e NOORDER

    SELECT name, deptno FROM emp e ORDER

    with the default of ORDER being that of the columns in the SELECT.

    The slight drawback being the effort of re-writing every SQL statement in every application in use. But think of the overtime πŸ™‚

  2. Sounds good in the first instance, but it would be detrimental to performance if devs started putting ORDER BY xxxx all over the place (on unindexed columns probably!).

  3. Generally I like the idea – mostly I’d feel ridiculous using it in queries intended to return just one row πŸ˜‰

    How about for starters adding a check for missing ORDER BY clause to PL/SQL compile time warnings? At least that could intelligently skip the warning when the SQL is a select into that it knows is supposed to return just one row?

  4. First, Tim, I so sympathize with the notion of helping people avoid unexpected changes to behavior that is only a side effect of physical ordering or row source delivery methods.

    However a fundamental underpinning of relational set theory and part of why it so effective is not mandating order. Codd’s original paper explains this better than I ever could. Chris Date has suggested that every database professional should read the 1969 paper, its 1970 update, or both at least annually. I agree.

    And despite my sympathy, I would add mandating an order by clause to the flaw side of the ledger of the SQL definition.

    When Oracle only had a sort method to resolve group by projection, it was a rock solid sort cut (against which Oracle always had a warning against relying). For a few releases – during V4 and V5 if I remember correctly – having the order by after a group by actually performed a duplicate sort. When that was expensive enough to notice some folks dropped the order by, which often then became sloppy habit, and instead of being carefully recorded in change history notes for repair when Oracle stopped doing the redundant order by or started implementing something like hash group by resolution this practice resulted in piles of code that broke. This was exactly what Oracle had warned against all along. (Do they still warn that NULL might [correctly] be distinct from the empty string at some point in the future?)

    How deep in the stack of subqueries would the order by requirement be pushed? To prevent the “user trap” associated with a presumed constancy of delivery order of tuples, it would have to be all the way to the bottom of the stack (or else the semantics of the complete stack of queries would have to be analyzed to prove the return order couldn’t matter, which would be pretty damn hard). A side effect would be a need to inject an order by that might be unneeded after every hash join and hash group by.

    So the defense against foolish reliance on an undirected order should be education, not mandating an order.

    Regards,

    Mark

  5. Chris: I never said all sets should be ordered. I simply said in SELECT statements an ORDER BY could be compulsory. That has nothing to do with forcing order on the sets.

    Mark: I understand your points and it was only a talking point, not a serious, hence my last sentence.

    Regarding performance implications, since queries not requiring orders would have “ORDER BY NOORDER”, which the optimizer could completely ignore, like there was no ORDER BY even mentioned, then I don’t see how this could effect performance. If a specific order is required either by user SQL or recursive SQL it is already present anyway, so this is not an extra burden. You imply a statement saying “DO NOTHING” forces something to be done, which is would not.

    Cheers

    Tim…

Comments are closed.