Why are NULLs sorted first?
BOL: A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
NULL means unknown. No other interpretation is valid.
If that's true, wouldn't they sort last, since the value could be greater than all other values?
There is no could be. There is no potential value. Unknown is unknown is unknown.
As to why it appears first, rather than last, this is not catered for by published SQL standards and is unfortunately left to the discretion of the RDBMS vendor:
Wikipedia: The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.
You are correct that NULL
can mean 'Indeterminant' or 'Uknownn' or 'Not known yet' or 'Not applying'. But there is no reason to put the Nulls first or last. If we don't know the actual values, then tehy can be small or large.
I think the standard for determinign the wanted behaviour of Nulls during sorting, is:
ORDER BY
test NULLS LAST --- or NULLS FIRST for the opposite
Unfortunately SQL-Server hasn't adopted this syntax yet. If I'm not wrong PostgreSQL and Oracle have it.
One solution:
ORDER BY
CASE WHEN test IS NOT NULL
THEN 0
ELSE 1
END
, test
Another solution that needs adjustment depending the datatype - but will not preform well, as it can't use an index on (test)
:
ORDER BY
COALESCE(test, 2147483647) --- if it's a 4-byte signed integer
I don't know why it's done that way, but by definition NULLS can't be compared to non-NULLS, so they either have to go at the start or the end (Mark's answer covers this in a lot more detail).
To get the behaviour you want - As far as I know there's no sorting option to put nulls last, so you have to bodge it by using a computed column to force them last. However, in SQL Server you can't order by a computed column (CASE WHEN ...
) when your data contains a set operator (UNION ALL
). So:
CREATE TABLE #sorttest(test int)
INSERT INTO #sorttest values(1)
INSERT INTO #sorttest values(5)
INSERT INTO #sorttest values(4)
INSERT INTO #sorttest values(NULL)
INSERT INTO #sorttest values(3)
INSERT INTO #sorttest values(2)
SELECT test
FROM #sorttest
ORDER BY CASE WHEN test IS NULL THEN 1 ELSE 0 END, test
DROP TABLE #sorttest
Will work for sorting nulls last. If you have to use UNION
(or EXCEPT
or INTERSECTS
) to generate your data set, then dump your data to a temporary table as above.