When is the CASE statement evaluated?

Question

I have the following query on the AdventureWorks2012 database

SELECT  productid,
        productname,
        unitprice,
        CASE
            WHEN unitprice < 20.0 THEN 'LOW'
            WHEN unitprice < 40.0 THEN 'MEDIUM'
            WHEN unitprice >= 40.0 THEN 'HIGH'
        END pricerange
FROM    Production.Products
ORDER BY
    CASE
        WHEN pricerange < 'LOW' THEN 1
        WHEN pricerange < 'MEDIUM' THEN 2
        WHEN pricerange >= 'HIGH' THEN 3
    END ASC
GO

The ORDER BY happens after the SELECT statement, but the pricerange column name cannot be accessed? I assume that pricerange in the SELECT statement is calculated after ORDER BY is called? Why is this?

Answer 1

SQL is a declarative language, not an imperative one. The order of execution isn't defined, and it isn't always the same.

In any case, order of execution isn't really important. The major point is that of scope - and pricerange isn't in scope anywhere within that select statement. Rather than thinking about the order in which lines of code appear (as in imperative programming), you should think about how each expression wraps another expression.

In this case, you're doing something like this:

Select(OrderBy(From(Products), ...), ...)

You have two ways around this - one option is to use the same case in the order by as the one you use in the select (don't worry, the engine is smart enough not to do the work twice). The second is to wrap your query in another query that does the actual ordering:

select * from
(
  SELECT  productid,
          productname,
          unitprice,
          CASE
              WHEN unitprice < 20.0 THEN 'LOW'
              WHEN unitprice < 40.0 THEN 'MEDIUM'
              WHEN unitprice >= 40.0 THEN 'HIGH'
          END pricerange
  FROM Production.Products
)
ORDER BY
    CASE
       WHEN pricerange < 'LOW' THEN 1
       WHEN pricerange < 'MEDIUM' THEN 2
       WHEN pricerange >= 'HIGH' THEN 3
    END ASC

But keep in mind that you're dealing with expression trees here, not command lists. You're describing what you want, not how it's going to execute. The how is the execution engine's job.

In the end, the execution engine might make the same execution plan for both variants of the query - they aren't really different; although there might be some corner cases around NULLs for example, I'm not sure.