This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
SQL: Rely on implicit ordering
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A SQL query produces reliable ordering only if:
- An ORDER BY clause is provided, explicitly.
ORDER BYclause is deterministic.
SELECT * FROM BOOK ORDER BY title, id
In many of the above examples, it would be weird if the RDBMS didn't produce any implicit ordering. But since SQL is a 4GL, and optimisers are free to produce any execution plan that implements the query's specifications (which don't specify any ordering explicitly), your assumptions may break at any time.
SELECT TITLE FROM BOOK;
You might think that rows are simple read out of a table, but what if this table is a view? What if it is partitioned? What if it is distributed? What if a covering index suddenly applies?
SELECT TITLE FROM ( SELECT TITLE FROM BOOK ORDER BY TITLE ) AS B;
The derived table specifies an explicit ordering, but the outer query does not. Nothing prevents an optimiser from deciding that in this case, the explicit ordering is unnecessary, even if it might be unlikely to do so for arbitrary reasons.
(SELECT TITLE FROM BOOK ORDER BY TITLE) UNION (SELECT 'Not really a book')
The UNION subquery specifies an explicit ordering, but there's no requirement at all for the
UNION operator to maintain this ordering. In fact,
UNION might be implemented using hashing, so the ordering wouldn't be stable.
SELECT TITLE, ROW_NUMBER () OVER (ORDER BY TITLE) FROM BOOK;
A window function may be ordered, and that ordering may be stable within a query, such that by accident, the results are as one would expect. But there's no guarantee for this. The RDBMS may produce results in any other order than the one from the window function.
SELECT AUTHOR_ID, COUNT(*) FROM BOOK GROUP BY AUTHOR_ID;
The GROUP BY clause may be implemented using a sort operation, but it may as well be implemented using hashing. There's no guarantee of one algorithm being used rather than the other. With sorting, there might be an accidental order that remains stable, but there's no guarantee for that.
SELECT ID, TITLE FROM BOOK ORDER BY TITLE;
In our schema,
TITLE is not a
UNIQUE column, so multiple books could share the same title. There's no guarantee of any stable ordering among the resulting
ID values, unless
ID is included in the
ORDER BY clause.