SQL Case: Does the Order of the When Statements Matter

SQL CASE: Does the order of the WHEN statements matter?

Yes, the order of the case statements does matter. The first matching row will be the one returned.

So, this statement:

(CASE WHEN phone = '(202) 555-5555' AND last_name = 'Smith' and first_name = 'John' THEN 1
      WHEN phone = '(202) 555-5555' AND last_name = 'Smith' THEN 2
      WHEN phone = '(202) 555-5555' THEN 3
 END)

Could return 1, 2, or 3 (or NULL). The following will always return 3 (or NULL) because the last two conditions will never be processed:

(CASE WHEN phone = '(202) 555-5555' THEN 3
      WHEN phone = '(202) 555-5555' AND last_name = 'Smith' THEN 2
      WHEN phone = '(202) 555-5555' AND last_name = 'Smith' and first_name = 'John' THEN 1
 END)

Execution order of WHEN clauses in a CASE statement

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code= '00'                        then 'A1'
 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
 WHEN r.code ='0120'                      then 1/0
 WHEN r.code ='01'                        then 'A4'

could generate a division by zero error even if r.code isn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.

Execution order of CASE statement SQL Server

A case expression is evaluated sequentially.

So, the second then is only evaluated when the first then does not return true. As an extreme example of this, consider:

select (case when 1=1 then 'true'
             when 1/0 = 0 then 'error'
        end)

This returns 'true' instead of error'ing out.

What is order of operational precedence in SQL Case statement?

The CASE statement evaluates its conditions sequentially and stops
with the first condition whose condition is satisfied

From your example, all one can deduct is that @B or @c is zero.

Operational precedence usually refers to which operator is first evaluated ("*" or "-", for example). Your question should probably be titled "Case evaluation order".

http://technet.microsoft.com/en-us/library/ms181765.aspx

SQL: case statement in order by clause

The sort clause is equivalent to the following, which may be slightly more obvious:

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID ELSE null END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID ELSE null END;

So the first sort field is the BusinessEntityID when SalariedFlag = 1, or null.

That will group all the rows where SalariedFlag = 0 together as they all have a null first sort field.

The rows that SalariedFlag = 1 wil be sorted by BusinessEntityID. It looks like nulls get sorted last in a descending sort so all the SalariedFlag != 1 go last.

That's the major sort, for the secondary sort, much the same thing happens:

All the rows where SalariedFlag = 0 will be sorted by BusinessEntityID. Since their primary sort fields were all null, they will end up ordered by BusinessEntityID.

And all the rows where SalariedFlag != 0 will be grouped together with a null secondary ordering. If those rows had SalariedFlag = 1, then they would already have been sorted by the primary ordering.

If SalariedFlag can only be 0 or 1 then this sort can be (slightly) simplified to:

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        , BusinessEntityID;

Does the order of where clauses matter in SQL?

No, that order doesn't matter (or at least: shouldn't matter).

Any decent query optimizer will look at all the parts of the WHERE clause and figure out the most efficient way to satisfy that query.

I know the SQL Server query optimizer will pick a suitable index - no matter which order you have your two conditions in. I assume other RDBMS will have similar strategies.

What does matter is whether or not you have a suitable index for this!

In the case of SQL Server, it will likely use an index if you have:

• an index on (LastName, FirstName)
• an index on (FirstName, LastName)
• an index on just (LastName), or just (FirstName) (or both)

On the other hand - again for SQL Server - if you use SELECT * to grab all columns from a table, and the table is rather small, then there's a good chance the query optimizer will just do a table (or clustered index) scan instead of using an index (because the lookup into the full data page to get all other columns just gets too expensive very quickly).

SQL - Does the order of WHERE conditions matter?

No, the order of the WHERE clauses does not matter.

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

---

Related Topics