CASE Clause

Description

CASE clause uses a rule to return a specific result based on the specified condition, similar to if/else statements in other programming languages.

Syntax

  1. CASE [ expression ] { WHEN boolean_expression THEN then_expression } [ ... ]
  2.     [ ELSE else_expression ]
  3. END

Parameters

  • boolean_expression

    Specifies any expression that evaluates to a result type boolean. Two or more expressions may be combined together using the logical operators ( AND, OR ).

  • then_expression

    Specifies the then expression based on the boolean_expression condition; then_expression and else_expression should all be same type or coercible to a common type.

  • else_expression

    Specifies the default expression; then_expression and else_expression should all be same type or coercible to a common type.

Examples

  1. CREATE TABLE person (id INT, name STRING, age INT);
  2. INSERT INTO person VALUES
  3.     (100, 'John', 30),
  4.     (200, 'Mary', NULL),
  5.     (300, 'Mike', 80),
  6.     (400, 'Dan', 50);
  8. SELECT id, CASE WHEN id > 200 THEN 'bigger' ELSE 'small' END FROM person;
  9. +------+--------------------------------------------------+
  10. |  id  | CASE WHEN (id > 200) THEN bigger ELSE small END  |
  11. +------+--------------------------------------------------+
  12. | 100  | small                                            |
  13. | 200  | small                                            |
  14. | 300  | bigger                                           |
  15. | 400  | bigger                                           |
  16. +------+--------------------------------------------------+
  18. SELECT id, CASE id WHEN 100 then 'bigger' WHEN  id > 300 THEN '300' ELSE 'small' END FROM person;
  19. +------+-----------------------------------------------------------------------------------------------+
  20. |  id  | CASE WHEN (id = 100) THEN bigger WHEN (id = CAST((id > 300) AS INT)) THEN 300 ELSE small END  |
  21. +------+-----------------------------------------------------------------------------------------------+
  22. | 100  | bigger                                                                                        |
  23. | 200  | small                                                                                         |
  24. | 300  | small                                                                                         |
  25. | 400  | small                                                                                         |
  26. +------+-----------------------------------------------------------------------------------------------+
  28. SELECT * FROM person
  29.     WHERE 
  30.         CASE 1 = 1 
  31.             WHEN 100 THEN 'big' 
  32.             WHEN 200 THEN 'bigger'
  33.             WHEN 300 THEN 'biggest' 
  34.             ELSE 'small'
  35.         END = 'small';
  36. +------+-------+-------+
  37. |  id  | name  |  age  |
  38. +------+-------+-------+
  39. | 100  | John  | 30    |
  40. | 200  | Mary  | NULL  |
  41. | 300  | Mike  | 80    |
  42. | 400  | Dan   | 50    |
  43. +------+-------+-------+