Had an interesting discussion with a colleague today over optimizing case statements and whether it's better to leave a case statement which has overlapping criteria as individual when clauses, or make a nested case statement for each of the overlapping statements.
As an example, say we had a table with 2 integer fields, column a and column b. Which of the two queries would be more processor friendly? Since we're only evaluating if a=1 or a=0 once using the nested statements, would this be more efficient for the processor, or would creating a nested statement eat up that optimization?
Multiple criteria for the case statement:
Select case when a=1 and b=0 THEN 'True' when a=1 and b=1 then 'Trueish' when a=0 and b=0 then 'False' when a=0 and b=1 then 'Falseish' else null end AS Result FROM tableName
Nesting case statements:
Select case when a=1 then case when b=0 then 'True' when b=1 then 'Trueish' end When a=0 then case when b=0 then 'False' when b=1 then 'Falseish' end else null end AS Result FROM tablename