About this question
Had an interesting discussion with a colleague today over optimising 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 table name
