Menu
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?
233
The CASE statement has two types: simple CASE statement and searched CASE statement. Both types of the CASE statements support an optional ELSE clause.
A variation of the if-then-else programming statement that is used when several ifs are required in a row. The following C example tests the variable KEY1 and performs functions based on the results. For an example of how the case statement was used in an actual program, see event loop.
The CASE statement chooses from a sequence of conditions and runs a corresponding statement. The simple CASE statement evaluates a single expression and compares it to several potential values. The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE .
Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. A selector is an expression, the value of which is used to select one of several alternatives.
The CASE expression evaluates to a value, i.e. it is used to evaluate to one of a set of results, 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 a set of 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 they are similar, but the statement does not evaluate to a value and can be used on its own, while the expression needs to be a part of an expression, e.g. a query or an assignment. You cannot use the statement in a query, since a query cannot contain statements, only expressions that need to evaluate to something (the query itself is a statement, in a way), e.g. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE makes no sense.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
