0

Is there a preferred (or more performant) way of writing a CASE with multiple WHEN conditions that generate the same value?

For example:

SELECT 
  CASE
    WHEN 1 > 0 AND 2 > 0 THEN 1
    WHEN 0 < 1 AND 0 < 2 THEN 1
    ELSE 0
  END AS multiple_WHEN,
  CASE
    WHEN (1 > 0 AND 2 > 0) OR (0 < 1 AND 0 < 2) THEN 1
    ELSE 0
  END AS single_OR

Would the remainder of the WHEN conditions need to be evaluated before reaching the end of the CASE, and would an OR condition act any differently?

  • 1
    Caution: When you simplify a query for us, we will help you with the simplified query. The advice may not apply to the real query. – Rick James Sep 16 at 4:18
  • Perhaps a little oversimplified, but in my case I'm dealing with numerical comparisons. I had read that considerations are needed when dealing with calculated values or parameters. – Ryan Dunphy Sep 16 at 4:24
  • sqlperformance.com/2014/06/t-sql-queries/… seemed to provide a decent explanation but didn't quite cover what I was looking for – Ryan Dunphy Sep 16 at 4:25
  • 1
    T-SQL and MySQL are different animals. Advice about one may or may not apply to the other. – Rick James Sep 16 at 4:27
  • Furthermore, some of the T-SQL syntax is not available in MySQL. – Rick James Sep 16 at 4:32
0

The performance difference between the two suggestions is insignificant.

As a rule of thumb, fetching a row is far more costly than any expressions that need to be evaluated for that row.

Do whichever is easier for you.

The expressions in those examples will be evaluated before it starts to perform the query. Perhaps your real query is more involved than that? In particular, if 2 > 0 is, instead, some complex subquery, then I need to reconsider my answer.

These may apply to MySQL:

  • "short circuit" -- But for simple expressions, you would be hard put to measure the performance difference.
  • When possible MATCH .. AGAINST (FULLTEXT) is performed first. (But this applies to WHERE, not CASE.)
  • User functions can be defined as DETERMINISTIC or non. This should impact the evaluation. (But I don't have proof of such.)
  • @variables are probably soon to be deprecated due to evaluation issues.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.