Why are NULLs sorted first?

Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?

select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test

results in

NULL
1
2
3
4

I keep thinking that NULL meant "Indeterminant" or possible "Unknown". If that's true, wouldn't they sort last, since the value could be greater than all other values? (Or is this a sorting option somewhere?)

I'm on SQL Server 2008R2, but I suspect this is true across all SQL Servers, and probably across all RDBMSs.

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.

StackExchange Database Administrators Q#8504, answer score: 20