Case Expression vs Case Statement

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.

Leave a Comment

error code: 523