Why do NULL values come first when ordering DESC in a PostgreSQL query?
Actually, with default sort order (ASCENDING
) NULL values come last.
Logic dictates that the sort order be reversed with the DESCENDING
keyword, so NULLs come first in this case.
But the best part comes last: you can choose which way you want it:
- Use the
NULLS FIRST | LAST
clause.
Quoting the current manual, version 9.3 as of writing:
If
NULLS LAST
is specified, null values sort after all non-null values; ifNULLS FIRST
is specified, null values sort before all non-null values. If neither is specified, the default behavior isNULLS LAST
whenASC
is specified or implied, andNULLS FIRST
whenDESC
is specified (thus, the default is to act as though nulls are larger than non-nulls). WhenUSING
is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.
Bold emphasis mine.
The simple answer is because that's how the people who wrote Postgres designed it. To quote:
The null value sorts higher than any other value. In other words, with ascending sort order, null values sort at the end, and with descending sort order, null values sort at the beginning.
This assumes that you have specified an ORDER BY clause, if you haven't then the rows are returned randomly.
If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.