Flow control functions-PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X supports the following flow control functions for conditional logic in SQL queries.

Function	Description
`CASE`	Returns a value based on the first matching condition
`IF(expr, v1, v2)`	Returns `v1` if `expr` is true; otherwise returns `v2`
`IFNULL(v1, v2)`	Returns `v1` if `v1` is not NULL; otherwise returns `v2`
`NULLIF(expr1, expr2)`	Returns NULL if `expr1 = expr2`; otherwise returns `expr1`

CASE

CASE evaluates conditions in order and returns the result of the first condition that is true.

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE result
END

CASE returns the result of the first matching WHEN clause and stops evaluating subsequent conditions. If no condition matches, the ELSE result is returned.

Examples:

SELECT CASE 1
  WHEN 1 THEN 'one'
  WHEN 2 THEN 'two'
  ELSE 'other'
END;
-- Result: 'one'

IF(expr, v1, v2)

Returns v1 if expr evaluates to true; otherwise returns v2.

SELECT IF(1 > 2, 'yes', 'no');
-- Result: 'no'

SELECT IF(1 < 2, 'yes', 'no');
-- Result: 'yes'

IFNULL(v1, v2)

Returns v1 if v1 is not NULL. Returns v2 if v1 is NULL.

SELECT IFNULL('hello', 'fallback');
-- Result: 'hello'

SELECT IFNULL(NULL, 'fallback');
-- Result: 'fallback'

NULLIF(expr1, expr2)

Returns NULL if expr1 = expr2. Otherwise returns expr1.

SELECT NULLIF(1, 1);
-- Result: NULL

SELECT NULLIF(1, 2);
-- Result: 1