Where do NULLs sort in an ORDER BY, and how do you control their placement portably?

## Why NULL is a problem for sorting Sorting needs a total order: for any two rows the engine must be able to say which comes first. NULL breaks that, because NULL is a marker for *no value*, and every comparison against it (`NULL < 5`, `NULL = NULL`) yields UNKNOWN rather than true or false. If ORDER BY used ordinary comparison semantics, a NULL could never be placed anywhere. The standard resolves this with a special rule that applies only to ordering: for the purpose of ORDER BY, all NULLs are considered equal to each other, and they are considered either greater than every non-NULL value or less than every non-NULL value. Which of the two is implementation-defined. Note the two separate guarantees — NULLs cluster together, and they cluster at one end — with only the choice of end left open. ## Where the majors actually put them | Engine | NULLs treated as | Position under ASC | Position under DESC | |---|---|---|---| | PostgreSQL | largest | last | first | | Oracle | largest | last | first | | MySQL | smallest | first | last | | SQL Server | smallest | first | last | Two things follow. First, a query that reads correctly on one engine can put NULLs at the opposite end on another, which is a classic surprise when a report is ported. Second, adding DESC to a key flips the NULLs along with everything else — people frequently forget that reversing the sort also moves the NULL block from one end to the other. ## The standard clause: NULLS FIRST and NULLS LAST A sort specification may carry an explicit null ordering: ```sql SELECT order_id, shipped_at FROM orders ORDER BY shipped_at DESC NULLS LAST; ``` The null ordering is per key, like the direction, and it overrides the engine default. PostgreSQL and Oracle accept it; MySQL and SQL Server do not, so a query using it is not universally portable — check your own engine's documentation before relying on it. ## The portable idiom: sort by a flag first When you cannot rely on NULLS FIRST/LAST, add a computed key that encodes the placement you want and put it *before* the real key: ```sql SELECT order_id, shipped_at FROM orders ORDER BY CASE WHEN shipped_at IS NULL THEN 1 ELSE 0 END, -- NULLs last shipped_at; ``` The CASE produces 0 for rows with a value and 1 for rows without, so the flag key splits the result into two blocks and the second key orders within them. Swap 1 and 0 to put NULLs first. This works on every engine, since it uses nothing but CASE, IS NULL and a two-key ORDER BY — and it does not need to appear in the SELECT list, because ORDER BY may sort by an expression it does not return (the exception is SELECT DISTINCT, where sort keys must be select-list items). A shorter variant, `ORDER BY shipped_at IS NULL, shipped_at`, relies on a boolean-valued expression being usable as a sort key. That is fine on engines with a boolean type but not universal, so the CASE form is the safer default. ## What about COALESCE? A tempting alternative is `ORDER BY COALESCE(shipped_at, DATE '9999-12-31')`. It works, but it is fragile: you must invent a sentinel of the right type that is guaranteed larger (or smaller) than any real value, and the day a real row carries that sentinel value the ordering quietly merges the two groups. The CASE flag has no such failure mode because the flag is computed from IS NULL, not from the data's range. ## Interaction with direction Direction and null ordering are independent knobs on the same key. `ORDER BY score DESC NULLS LAST` means "highest score first, and rows with no score at the bottom" — a common reporting requirement that neither DESC alone nor NULLS LAST alone expresses. When you emulate this with the flag trick, remember the flag key itself normally stays ASC while the real key takes DESC: ```sql ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC; ``` ## Practical guidance - Decide deliberately where NULLs belong; do not inherit whatever the engine defaults to. - If the query must run on more than one product, use the CASE flag. - If you use NULLS FIRST/LAST, note in the query or the migration guide which engines you are targeting. - Remember NULLs move ends when you flip ASC to DESC. - Sorting NULLs is a placement question only; it never removes them. If NULL rows should not be in the result at all, that is a WHERE ... IS NOT NULL decision.