Case Statement

The CASE statement performs a switch based on a condition. The basic form is identical to the ternary condition used in many programming languages (CASE WHEN cond THEN a ELSE b END is equivalent to cond ? a : b).

-- integers [1, 2, 3]
SELECT i, CASE WHEN i>2 THEN 1 ELSE 0 END FROM integers;
-- 1, 2, 3
-- 0, 0, 1

The WHEN cond THEN expr part of the CASE statement can be chained, whenever any of the conditions returns true for a single tuple, the corresponding expression is evaluated and returned.

-- integers [1, 2, 3]
SELECT i, CASE WHEN i=1 THEN 10 WHEN i=2 THEN 20 ELSE 0 END FROM integers;
-- 1, 2, 3
-- 10, 20, 0

The ELSE part of the CASE statement is optional. If no else statement is provided and none of the conditions match, the CASE statement will return NULL.

-- integers [1, 2, 3]
SELECT i, CASE WHEN i=1 THEN 10 END FROM integers;
-- 1, 2, 3
-- 10, NULL, NULL

After the CASE but before the WHEN an individual expression can also be provided. When this is done, the CASE statement is essentially transformed into a switch statement.

-- integers [1, 2, 3]
SELECT i, CASE i WHEN 1 THEN 10 WHEN 2 THEN 20 WHEN 3 THEN 30 END FROM integers;
-- 1, 2, 3
-- 10, 20, 30

-- this is equivalent to:
SELECT i, CASE WHEN i=1 THEN 10 WHEN i=2 THEN 20 WHEN i=3 THEN 30 END FROM integers;