Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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.
- That
ORDER BYclause is deterministic.
For example:
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.
Implicit row insertion ordering
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?
Implicit derived table ordering
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.
Implicit union subquery ordering
(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.
Implicit window ordering
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.
Implicit GROUP BY ordering
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.
Implicit non-deterministic ordering
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.
Feedback
Do you have any feedback about this page? We'd love to hear it!