SQL CASE表达式是一种通用的条件表达式，类似于其它编程语言中的 if/else 语句：

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

CASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真，那么CASE表达式的结果就是符合条件的result，并且剩下的CASE表达式不会被处理。如果条件的结果不为真，那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真，那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真，结果为空。

例子：

SELECT * FROM test;

 a
---
 1
 2
 3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

所有result表达式的数据类型都必须可以转换成单一的输出类型。 参阅Section 10.5获取细节。

下面这个“简单”形式的CASE表达式是上述通用形式的一个变种：

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

第一个expression会被计算，然后与所有在WHEN子句中的每一个value对比，直到找到一个相等的。如果没有找到匹配的，则返回在ELSE子句中的result（或者控制）。 这类似于 C 里的switch语句。

上面的例子可以用简单CASE语法来写：

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

 a | case
---+-------
 1 | one
 2 | two
 3 | other

CASE表达式并不计算任何无助于判断结果的子表达式。例如，下面是一个可以避免被零除错误的方法：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

在mysql兼容模式下(See lightdb_dblevel_syntax_compatible_type)， 表达式的结果集可以是字符，数值，日期的任意组合，同时包含字符，数值和日期的种的任意两种以上时，返回值的类型为字符串。

SELECT CASE WHEN score >= 60 THEN score ELSE 'not pass' END AS score from students;
 

COALESCE(value [, ...])

COALESCE函数返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值。例如：

SELECT COALESCE(description, short_description, '(none)') ...

如果description不为空，这将会返回它的值，否则如果short_description非空则返回short_description的值，如果前两个都为空则返回(none)。

所有参数都必须转换为一个公共数据类型，它将是结果的类型 (详请参见 Section 10.5 )。

和CASE表达式一样，COALESCE将不会 计算无助于判断结果的参数；也就是说，在第一个非空参数右边的参数不会被计算。这个 SQL 标准函数提供了类似于NVL和IFNULL的能力，它们被用在某些其他数据库系统中。

NULLIF(value1, value2)

当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。 这些可以用于执行前文给出的COALESCE例子的逆操作：

SELECT NULLIF(value, '(none)') ...

在这个例子中，如果value是(none)，将返回空值，否则返回value的值。

这两个参数必须具有可比较的类型。具体来说，它们的比较与你写的 value1 = value2完全一样，因此必须有一个合适的=操作符可用。

结果的类型与第一个参数相同，但有一点细微的区别。实际上返回的是隐含 =操作符的第一个参数，在某些情况下，它将被提升以匹配第二个参数的类型。 例如，NULLIF(1, 2.2) 生成 numeric，因为没有integer = numeric操作符，只有numeric = numeric。

GREATEST(value [, ...])

LEAST(value [, ...])

GREATEST和LEAST函数从一个任意的数字表达式列表里选取最大或者最小的数值。 这些表达式必须都可以转换成一个普通的数据类型，它将会是结果类型 （参阅Section 10.5获取细节）。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。

请注意GREATEST和LEAST都不是 SQL 标准，但却是很常见的扩展。某些其他数据库让它们在任何参数为 NULL 时返回 NULL，而不是在所有参数都为 NULL 时才返回 NULL。

当 GUC 参数 lightdb_dblevel_syntax_compatible_type 设置为 oracle 时，GREATEST 和 LEAST 函数的行为遵循 Oracle 标准。在此模式下，如果任何参数求值为 NULL，则函数返回 NULL。另外，第一个参数用于确定返回类型。如果第一个参数是数值型，则 lightdb 确定具有最高数值优先级的参数，在比较之前隐式转换其余参数为该数据类型，并返回该数据类型。如果第一个参数不是数值型，则在比较之前将第一个参数之后的每个参数隐式转换为第一个参数的数据类型。

  NVL(expr1, expr2)
  

当 expr1 为 NULL 时，返回 expr2。当 expr1 不为 NULL 时，返回 expr1。expr1 和 expr2 的数据类型可以不同。我们支持以下不同的数据类型。

Name |      Result data type       |     Argument data types       
------+-----------------------------+----------------------------------
nvl  | bit                         | bit, "any"                         
nvl  | bytea                       | bytea, "any"                       
nvl  | date                        | date, "any"                        
nvl  | numeric                     | double precision, "any"            
nvl  | numeric                     | integer, "any"                     
nvl  | numeric                     | numeric, "any"                     
nvl  | text                        | text, "any"                        
nvl  | timestamp without time zone | timestamp without time zone, "any" 
nvl  | timestamp with time zone    | timestamp with time zone, "any"    
nvl  | time without time zone      | time without time zone, "any"    

上面给出的 NVL 示例：

lightdb@test=# select nvl(1.1, 'test'::text);
nvl 
-----
1.1
(1 row)

在这个例子中，expr1 的数据类型是数字，而 expr2 的数据类型是文本。因此，调用函数 nvl(numeric, "any") 并返回数字数据类型。

在某些特殊情况下，当 expr1 为 null 时返回的数据类型取决于数据源。当执行以下示例时，根据隐式转换规则，null 实际上是未知类型，因此调用的函数是 nvl(text, 'any')，因此函数返回文本类型。

 lightdb@test=# select pg_typeof(nvl(null, 1)), nvl(null, 1);
 pg_typeof | nvl 
-----------+-----
 text      | 1
(1 row)
 

在以下示例中，因为数据库中的 null 字段是 int 类型，所以该函数返回一个数字类型。

create table test_nvl (id int, func varchar(20));
insert into test_nvl values (null, 'nvl');

lightdb@test=# select pg_typeof(nvl(id, 1)), nvl(id, 1)  from test;
 pg_typeof | nvl 
-----------+-----
 numeric   |   1
(1 row)
  

由于 ltrim 返回的数据类型是文本类型，因此在下面的示例中实际调用的函数是 nvl(text, "any")，因此该函数返回文本类型。

lightdb@test=# select pg_typeof(nvl(ltrim(func, 'nvl'), 1)), nvl(ltrim(func, 'nvl'), 1) from test;
 pg_typeof | nvl 
-----------+-----
 text      | 1
(1 row)
  

上述特性也适用于函数 nvl2、decode 和 decode2。

  NVL2(expr,substitute1,substitute2)
  

NVL2 根据指定值是否为 NULL 返回替代值。当 expr 为 NULL 时，返回 substitute2。当 expr 不为 NULL 时，返回 substitute1。

substitute1 和 substitute2 的数据类型要相同，但 expr 可以不同。对于 substitute1 和 substitute2，我们支持以下数据类型。

  
Name  |      Result data type       |        Argument data types       
------+-----------------------------+------------------------------------
nvl2  | bit                         | bit, "any"                         
nvl2  | bytea                       | bytea, "any"                       
nvl2  | date                        | date, "any"                        
nvl2  | numeric                     | double precision, "any"            
nvl2  | numeric                     | integer, "any"                     
nvl2  | numeric                     | numeric, "any"                     
nvl2  | text                        | text, "any"                        
nvl2  | timestamp without time zone | timestamp without time zone, "any" 
nvl2  | timestamp with time zone    | timestamp with time zone, "any"    
nvl2  | time without time zone      | time without time zone, "any"    

上面给出的 NVL2 示例：

lightdb@test=# select nvl2('2022-01-01'::date,'ab'::text, 'cd'::text);
nvl2 
------
ab
(1 row)

在这个例子中，expr 的数据类型是日期，substitute1 和 substitute2 的数据类型是文本。因此，调用函数 nvlnvl2("any", text, "any") 并返回文本数据类型。

DECODE函数将expr和每个search逐个进行比较，若expr与某个search相等，则返回该search对应的result；若expr与所有search都不相等，则返回default。若没有指定default，则返回null。

DECODE(expr, 
      search, result, 
      [search, result], 
      [search, result]..., 
      [default])

DECODE函数（参数个数大于2）仅在oracle模式下使用，这意味着在使用decode函数前，需要先将GUC参数lightdb_dblevel_syntax_compatible_type设置为oracle。

LightDB会自动将expr和每个search的数据类型转成第一个search的类型后再比较，若类型不能转换则报错；同时会自动将返回值的类型转成第一个result的类型，若类型不能转换则报错。若第一个result为null，则函数的返回值为TEXT类型。

lightdb@oracle=# create table t(a char(10),b varchar2(10));
CREATE TABLE
lightdb@oracle=# insert into t values('a','a');
INSERT 0 1
lightdb@oracle=# select decode(a,b,1,2) from t;
 decode 
--------
      2
(1 row)

lightdb@oracle=# select decode(b,a,1,2) from t;
 decode 
--------
      1
(1 row)

lightdb@oracle=# select decode(trim(a),b,1,2) from t;
 decode 
--------
      1
(1 row)

lightdb@oracle=# select pg_typeof(decode(1,2,'3',4));
 pg_typeof 
-----------
 text
(1 row)

search、result和default可以是复杂的表达式，LightDB会对result和default使用“短路”方式计算上述表达式的值，即每个search仅在与expr比较且比较结果为相等时才会计算result或default值，否则无需计算。该“短路”规则基于一个前提，那就是必须存在合适的类型转换规则，若不存在转换规则则直接报错。另外该“短路”规则对search不适用，也就是说一开始就要将所有的search的类型要全部转成第一个search的类型。

lightdb@oracle=# select decode(3,
                               1,1/0, --short-circuit on result, no division by zero error
                               2,2/0, --short-circuit on result, no division by zero error
                               3,300,
                               'x'    --short-circuit on default, no type convertion error
                               );
 decode 
--------
    300
(1 row)

SELECT DECODE(123, 
  123, 'Number Match', 
  'Not Match', 'Default') --'Not Match' cannot convert to int(type of 1st search)
  FROM DUAL;
ERROR:  invalid input syntax for type numeric: "Not Match"
LINE 3:   'Not Match', 'Default') --'Not Match' cannot convert to in...

在DECODE函数中，LightDB在比较时会认为两个null是相等的，即当expr为null，某个search也为null时，LightDB认为expr与该search相等，所以函数会返回该search对应的result。

lightdb@oracle=# select decode(null,null,1,2);
 decode 
--------
      1
(1 row)