Case Expression vs Case Statement

by

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.

More Related Contents:

  1. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  2. How do I use properly CASE..WHEN in MySQL
  3. Case statement in MySQL
  4. How does MySQL CASE work?
  5. MySQL update table based on another tables value
  6. On Duplicate Key Update same as insert
  7. Ordering by the order of values in a SQL IN() clause
  8. LAST_INSERT_ID() MySQL
  9. SQL Selecting from two Tables with inner join and limit [duplicate]
  10. How to export and import a .sql file from command line with options? [duplicate]
  11. a count for each join – optimisation
  12. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  13. Are column and table name case sensitive in MySQL?
  14. MySQL – SELECT WHERE field IN (subquery) – Extremely slow why?
  15. How to add not null constraint to existing column in MySQL
  16. Special Characters in MySQL Table Name
  17. connect ECONNREFUSED – node js , sql
  18. MySQL Aggregate Functions without GROUP BY clause
  19. Count days between two dates, excluding weekends (MySQL only)
  20. How to insert values in table with foreign key using MySQL?
  21. Is there a way to “limit” the result with ELOQUENT ORM of Laravel?
  22. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  23. How to set root password to null
  24. the MySQL service on local computer started and then stopped
  25. Empty IN clause parameter list in MySQL
  26. creating a random number using MYSQL
  27. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  28. Storing images in MySQL [closed]
  29. Would it help to add index to BIGINT column in MySQL?
  30. Mysql stored procedure don’t take table name as parameter

Leave a Comment