Case to Case Expression

Question

What is the difference between Case Expression and Case Statement in MySQL? When can they be used and what are the advantages of using one over the other?

Case syntax:

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE 

The syntax of an expression is:

 CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

They look almost identical, but the original description for Case statements is that The CASE statement for stored programs implements a complex conditional construct.

So the significant difference is that one is used in stored programs and not used in regular queries? I tried this on the query I was playing with and it failed - sqlfiddle . If so, why not just use an Expression expression in a saved program?

Are there any other syntactic differences to be aware of as they seem identical?

Answer 1

The CASE expression is evaluated by a value, that is, it is used to evaluate one of the result set based on some condition.
Example:

 SELECT CASE WHEN type = 1 THEN 'foo' WHEN type = 2 THEN 'bar' ELSE 'baz' END AS name_for_numeric_type FROM sometable` 

The CASE statement executes one of many statements based on some condition.
Example:

 CASE WHEN action = 'update' THEN UPDATE sometable SET column = value WHERE condition; WHEN action = 'create' THEN INSERT INTO sometable (column) VALUES (value); END CASE 

You see how similar they are, but the operator does not evaluate the value and can be used on its own, while the expression should be part of the expression, for example. request or task. You cannot use an operator in a query, since the query cannot contain operators, but only expressions that must evaluate something (the query itself is an expression in a sense), for example. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE does not make sense.