Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
Oracle lists it last. That screwed me up once, believing it should behave like SQL Server. – Andrei Rinea Mar 16 '12 at 15:37
1  
"If that's true, wouldn't they sort last, since the value could be greater than all other values". The value could be less than all other values too. To me, it's intuitive that a falsey value like null should be on the lower end. And practical, since in practice, you often want to use desc ordering to show the biggest or most recent things, in which case I'd be glad for null things to be last. – mahemoff Oct 8 '13 at 23:10
up vote 15 down vote accepted

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.

share|improve this answer
    
So, it's a judgment call. That makes a lot of sense. Thanks! – Richard Nov 28 '11 at 23:24

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
share|improve this answer
    
In this way ORDER BY COALESCE(test, 2147483647) SQL server can not use Index. – Ardalan Shahgholi Nov 4 '14 at 1:48
    
@ArdalanShahgholi Yes, I didn't test but you may be right. – ypercubeᵀᴹ Nov 4 '14 at 2:01

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.

share|improve this answer
    
...or use the UNIONed output as a derived table. – Andriy M Apr 7 at 11:49

If you are dealing with numbers you can also use

ORDER BY -test DESC

NULL are the lowest possible values, therefore DESC puts them at the end. Meanwhile the not null values have the sign inverted so DESC actually is an ASC on the real values. This should be faster than CASE and I suppose that query optimizer can also use indexes on test column.

share|improve this answer
3  
No, it wouldn't be able to use an index for the sort. Unless you have an index on the computed expression (- test). – ypercubeᵀᴹ Apr 7 at 9:17
1  
Clever, even though limited to numeric data only (appropriate for OP's example anyway). I'm not sure if this would indeed be faster than using CASE but I'm sure it wouldn't use an index (unless it's what @ypercubeᵀᴹ says – but then a CASE expression could be indexed in exactly the same way). – Andriy M Apr 7 at 11:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.