What is the difference between a Case Expression and a Case Statement in MySQL? When can they be used, and what are the benefits of using one over the other?
Case Statement syntax:
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Case Expression syntax:
CASE
WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END
These look almost identical, but the initial description for Case Statements is that The CASE statement for stored programs implements a complex conditional construct.
So is the significant difference that one is used in stored programs and not usable in normal queries? I tried this out on a query I was playing with and it failed - sqlfiddle. If this is the case though, why not just use the Case Expression in a stored program?
Are there any other syntactical differences to be aware of, since they seem to be identical?