Case Expressions

Case expressions are expressions that lets you use the logic from the conditional statements. It also resembles switch expressions from most programming languages.

Case expressions are mostly written in SELECT statements.

Let us have an example using the case expression to better understand its purpose.

SELECT 

  name,

  age,

  CASE 

    WHEN age <= 13 THEN 'Child'

    WHEN age <= 18 THEN 'Teenager'

    ELSE 'Adult'

  END AS age_group

FROM users;

This query displays three columns, the two are from the table, and the other is a case expression that depends on the age column. The block of code highlighted in yellow is a case expression. The expression starts with the keyword CASE to indicate the beginning of it, and ends with the keyword END to, literally, indicate the end.

The WHEN keyword poses a condition that could depend on some columns, then the THEN keyword takes a value to set if a corresponding condition is satisfied. The ELSE keyword is optional; it only applies if any of the declared conditions are not satisfied. Otherwise, it will return the value that is set on the clause, or it will return NULL if no ELSE clause is set.

In this example, if age is less than or equal to 13, it will then return "Child". If it is less than or equal to 18, it will return "Teenager". Otherwise, if nothing satisfied, it returns "Adult".

A column alias is also provided for the case expression by using the AS keyword.

The following figure illustrates the flow of the case expression: