Change the sort key and the rows physically move. Add a direction, a tie-breaker and a NULLS placement, and watch the query text build itself as you go.
10 rows
#namedeptsalaryhiredbonus
The # column is the row's position in the unsorted table, so you can see exactly which rows moved and which ones only kept their old order because nothing told them otherwise.
ORDER BY: The Only Thing That Guarantees an Order
Without it a result set has no order at all — whatever you saw last time was luck.
Quick Context
A table is a set of rows, and a set has no order. Without an ORDER BY the database is free to return rows in whatever order came out of its plan — insertion order, index order, or whatever the parallel workers finished in. It often looks stable for months and then changes the day the table grows an index or the optimiser picks a different plan.
This is the single most common source of "it worked yesterday" bugs in reporting code. If the order matters, say so.
The shape of the clause
ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST], expr2 ...
Keys are applied left to right. The second key is consulted only for rows the first key could not separate, the third only for rows the first two could not, and so on. ASC is the default and almost never written out.
You can sort by things that are not columns: an expression such as salary * 12, an alias defined in the SELECT list, or a positional number such as ORDER BY2 meaning the second selected column. The positional form is compact and a menace — it silently changes meaning when someone edits the SELECT list.
Ties, and why a second key is not optional
Sort the sample table by salary and four pairs of rows tie. Those eight rows appear in some order, but nothing in your query asked for it, so nothing guarantees it will be the same order tomorrow. That is what the "order is decided" readout is telling you: a sort is only deterministic once the keys you supplied separate every pair of rows.
Adding a tie-breaker that is unique — a primary key is the usual choice — makes the result reproducible. This matters most with pagination: ORDER BYsalaryLIMIT10 OFFSET 10 can return a row you already saw on page one, and skip another entirely, purely because the tied rows shuffled between the two queries.
Where NULLs go
NULL is not a value, so "is it bigger or smaller?" has no natural answer, and engines disagree. PostgreSQL and Oracle treat NULL as the largest value: NULLS LAST for ASC, NULLS FIRST for DESC. MySQL and SQLite treat it as the smallest, so they do the opposite. SQL Server has no NULLS clause at all and sorts NULLs first.
If it matters, be explicit. Where the syntax is unavailable, sort by a flag first: ORDER BY (bonusIS NULL), bonusDESC.
When it runs, and what it costs
ORDER BY is almost last in the logical order of a query: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why an alias from the SELECT list is usable here but not in WHERE, and why LIMIT takes the top of the sorted result rather than an arbitrary handful of rows.
Sorting is O(n log n) and needs memory; when the set does not fit, the engine spills to disk and the query gets dramatically slower. An index whose column order matches the ORDER BY can remove the sort entirely, because the index is already in that order — which is also why (a ASC, b DESC) may not be servable by an index on (a, b).
Interactive Exploration Guide
Sort by salary. Rows slide into place and eight of them light up amber — those are the ties. "Order is decided" reads NO, because your query has not said what should happen inside a tie.
Flip the direction. Set Direction to DESC and the whole list reverses. Note the tied pairs: their internal order does not simply mirror, because reversing the comparison does not reverse the rows the comparison never separated.
Break the ties. Set Then By to name. The amber highlighting clears, "Order is decided" flips to YES, and the result is now reproducible.
Sort by something unique. Set Order By to name. One key is enough here, because no two employees share a name.
Meet the NULLs. Set Order By to bonus. Three rows have no bonus; with NULLS LAST they sink to the bottom.
Move them. Switch NULLS Placement to NULLS FIRST and those three jump to the top without any other row changing place. This is the setting MySQL and PostgreSQL disagree about by default.
Key Takeaway
A result set has no order unless ORDER BY gives it one, and the order is only deterministic once your keys separate every pair of rows — which is why a unique tie-breaker belongs on anything paginated. Keys apply left to right, each one consulted only for the ties the previous keys left behind. NULL placement is engine-specific, so write NULLS FIRST or NULLS LAST rather than trusting a default. And because the sort runs near the end of the query, it can see SELECT aliases, it feeds LIMIT, and it costs real time and memory unless an index already holds the rows in the order you asked for.
Predict, then reveal
About to run: Sort by salary. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
Without scrolling back — what is the one-line takeaway from this module?
A result set has no order unless ORDER BY gives it one, and the order is only deterministic once your keys separate every pair of rows — which is why a unique tie-breaker belongs on anything paginated. Keys apply left to right, each one consulted only for the ties the previous keys left behind. NULL placement is engine-specific, so write NULLS FIRST or NULLS LAST rather than trusting a default.
What does this module say about “Quick Context”?
A table is a set of rows, and a set has no order. Without an ORDER BY the database is free to return rows in whatever order came out of its plan — insertion order, index order, or whatever the parallel workers finished in. It often looks stable for months and then changes the day the table grows an index or the optimiser picks a different plan.
What does this module say about “Ties, and why a second key is not optional”?
Sort the sample table by salary and four pairs of rows tie. Those eight rows appear in some order, but nothing in your query asked for it, so nothing guarantees it will be the same order tomorrow. That is what the "order is decided" readout is telling you: a sort is only deterministic once the keys you supplied separate every pair of rows.
Cheat sheet
ORDER BY in SQL
Change the sort key and the rows physically move. Add a direction, a tie-breaker and a NULLS placement, and watch the query text build itself as you go.