Conditional expressions

CASE

The standard SQL CASE expression has two forms. The "simple" form searches each value expression from left to right until it finds one that equals expression:

CASE expression
    WHEN value THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

The result for the matching value is returned. If no match is found, the result from the ELSE clause is returned if it exists, otherwise null is returned. Example:

SELECT a,
       CASE a
           WHEN 1 THEN 'one'
           WHEN 2 THEN 'two'
           ELSE 'many'
       END

In addition to bare values, the simple form's WHEN clauses can carry a predicate fragment. The fragment's left-hand side is the surrounding CASE expression, so WHEN > 100 reads as "when expression > 100":

CASE expression
    WHEN > 100              THEN result
    WHEN BETWEEN 1 AND 10   THEN result
    WHEN IN (a, b, c)       THEN result
    WHEN IS NULL            THEN result
    WHEN LIKE 'a%'          THEN result
    WHEN IS DISTINCT FROM x THEN result
    [ WHEN value THEN result ]
    [ ELSE result ]
END

The supported predicate fragments are:

Fragment	Reads as
op value	expression op value for comparison op
op {ANY | ALL | SOME} (query)	expression op {ANY | ALL | SOME} (query)
[NOT] BETWEEN low AND high	expression [NOT] BETWEEN low AND high
[NOT] IN (value, ...)	expression [NOT] IN (value, ...)
[NOT] IN (subquery)	expression [NOT] IN (subquery)
[NOT] LIKE pattern [ESCAPE c]	expression [NOT] LIKE pattern [ESCAPE c]
IS [NOT] NULL	expression IS [NOT] NULL
IS [NOT] DISTINCT FROM value	expression IS [NOT] DISTINCT FROM value

Bare-value and predicate-fragment WHEN clauses may be mixed in the same CASE. The clauses are tried left to right and the first match wins, just like the bare-value form. Example:

SELECT a,
       CASE a
           WHEN 0                THEN 'zero'
           WHEN > 100            THEN 'big'
           WHEN BETWEEN 1 AND 10 THEN 'small'
           WHEN IS NULL          THEN 'unknown'
           ELSE 'other'
       END

The CASE operand is evaluated exactly once per row, regardless of how many WHEN clauses reference it.

The "searched" form evaluates each boolean condition from left to right until one is true and returns the matching result:

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

If no conditions are true, the result from the ELSE clause is returned if it exists, otherwise null is returned. Example:

SELECT a, b,
       CASE
           WHEN a = 1 THEN 'aaa'
           WHEN b = 2 THEN 'bbb'
           ELSE 'ccc'
       END

SQL UDFs can use CASE statements that use a slightly different syntax from the CASE expressions. Specifically note the requirements for terminating each clause with a semicolon ; and the usage of END CASE.

IF

The IF expression has two forms, one supplying only a true_value and the other supplying both a true_value and a false_value:

if(condition, true_value)

Evaluates and returns true_value if condition is true, otherwise null is returned and true_value is not evaluated.

if(condition, true_value, false_value)

Evaluates and returns true_value if condition is true, otherwise evaluates and returns false_value.

The following IF and CASE expressions are equivalent:

SELECT
  orderkey,
  totalprice,
  IF(totalprice >= 150000, 'High Value', 'Low Value')
FROM tpch.sf1.orders;

SELECT
  orderkey,
  totalprice,
  CASE
    WHEN totalprice >= 150000 THEN 'High Value'
    ELSE 'Low Value'
  END
FROM tpch.sf1.orders;

SQL UDFs can use IF statements that use a slightly different syntax from IF expressions. Specifically note the requirement for terminating each clause with a semicolon ; and the usage of END IF.

COALESCE

coalesce(value1, value2[, ...])

Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary.

NULLIF

nullif(value1, value2)

Returns null if value1 equals value2, otherwise returns value1.

TRY

try(expression)

Evaluate an expression and handle certain types of errors by returning NULL.

In cases where it is preferable that queries produce NULL or default values instead of failing when corrupt or invalid data is encountered, the TRY function may be useful. To specify default values, the TRY function can be used in conjunction with the COALESCE function.

The following errors are handled by TRY:

• Division by zero
• Invalid cast or function argument
• Numeric value out of range
• Invalid JSON literal
• JSON input or output conversion errors
• JSON path evaluation errors
• JSON value function result errors

Examples

Source table with some invalid data:

SELECT * FROM shipping;

 origin_state | origin_zip | packages | total_cost
--------------+------------+----------+------------
 California   |      94131 |       25 |        100
 California   |      P332a |        5 |         72
 California   |      94025 |        0 |        155
 New Jersey   |      08544 |      225 |        490
(4 rows)

Query failure without TRY:

SELECT CAST(origin_zip AS BIGINT) FROM shipping;

Query failed: Cannot cast 'P332a' to BIGINT

NULL values with TRY:

SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;

 origin_zip
------------
      94131
 NULL
      94025
      08544
(4 rows)

Query failure without TRY:

SELECT total_cost / packages AS per_package FROM shipping;

Query failed: Division by zero

Default values with TRY and COALESCE:

SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;

 per_package
-------------
          4
         14
          0
         19
(4 rows)