The ORDER BY clause imposes a deterministic sequence on rows returned by a query based on specified sort keys.
In relational database theory, tables represent unordered mathematical sets of tuples with no inherent sequence.
Without an explicit ORDER BY clause, the database engine returns rows in an arbitrary order dictated by storage allocation.
Relying on default table ordering is a critical mistake, as concurrent writes, vacuuming, and server restarts alter retrieval order.
Sorting keys are evaluated sequentially from left to right, resolving ties in earlier columns using subsequent columns.
For example, sorting by country in ascending order and then revenue in descending order organises regional sales clearly.
Text columns sort according to collation rules, which dictate character casing precedence and accent sensitivity.
The NULLS FIRST and NULLS LAST clauses give explicit control over whether missing values appear at the beginning or end of output.
Paired with sorting, LIMIT or FETCH clauses restrict the output to a specified count of leading rows.
This combination is essential for top-N analysis, such as identifying the five highest-earning store locations.
Sorting massive datasets requires considerable memory; when data exceeds RAM, the engine spills temporary runs onto disk storage.
External disk sorting creates substantial input-output latency, slowing down large analytical queries significantly.
Database administrators build B-tree indexes matching common sort keys to allow pre-sorted retrieval without on-the-fly computation.
Column positional numbers can be used in ORDER BY, but naming columns explicitly makes queries more robust to schema edits.
Analysts should apply sorting only when necessary for presentation or ranking, avoiding wasted compute on unordered backend jobs.