Why and How do ORDER BY CASE Queries Work in SQL Server?

Question

Let's look at the following table:

| col1     | col2           |
| -------- | -------------- |
| 1        | NULL           |
| 23       | c              |
| 73       | NULL           |
| 43       | a              |
| 3        | d              |

Suppose you wanted to sort it like this:

| col1     | col2           |
| -------- | -------------- |
| 1        | NULL           |
| 73       | NULL           |
| 43       | a              |
| 23       | c              |
| 3        | d              |

With the following code this would be almost trivial:

SELECT *
FROM dbo.table1
ORDER BY col2;

However, to sort it in the following, non-standard way isn't that easy:

| col1     | col2           |
| -------- | -------------- |
| 43       | a              |
| 23       | c              |
| 3        | d              |
| 1        | NULL           |
| 73       | NULL           |

I made it with the following code

SELECT *
FROM dbo.table1
ORDER BY CASE WHEN col2 IS NULL THEN 1 ELSE 0 END, col2;

Can you explain to me 1) why and 2) how this query works? What bugs me is that the CASE-statement returns either 1 or 0 which means that either ORDER BY 1, col2 or ORDER BY 0, col2 will be executed. But the following code gives me an error:

SELECT *
FROM dbo.table1
ORDER BY 0, col2;

Yet, the overall statement works. Why?

Answer 1

How does this work?

ORDER BY (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END),
         col2;

Well, it works exactly as the code specifies. The first key for the ORDER BY takes on the values of 1 and 0 based on col2. The 1 is only when the value is NULL. Because 1 > 0, these are sorted after the non-NULL values. So, all non-NULL values are first and then all NULL values.

How are the non-NULL values sorted? That is where the second key comes in. They are ordered by col2.

Answer 2

This is a description for oracle database SQL's ORDER BY:

ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
  [, { expr | position | c_alias }
     [ ASC | DESC ]
     [ NULLS FIRST | NULLS LAST ]
  ]...

We can see that position and expr were depicted as separate paths in the diagram. From the fact, we can conclude that the 0 and 1 are not categorized as position because the CASE expression is not position even though the expression would be evaluated to a number, which is can be viewed as position value.

I think this view can be applied to T-SQL too.