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 11.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 11.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 11.5获取细节）。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。

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

lightdb@oracle=# select pg_typeof(least('2020-9-25'::oracle.date,'2021-9-25'::oracle.date)), least('2020-9-25'::oracle.date,'2021-9-25'::oracle.date);
  pg_typeof  |        least        
-------------+---------------------
 oracle.date | 2020-09-25 00:00:00
(1 row)

lightdb@oracle=# select pg_typeof(greatest('2020-9-25'::oracle.date,'2021-9-25'::oracle.date)), greatest('2020-9-25'::oracle.date,'2021-9-25'::oracle.date);
  pg_typeof  |      greatest       
-------------+---------------------
 oracle.date | 2021-09-25 00:00:00
(1 row)
       

  NVL(expr1, expr2)
  

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

Name |      Result data type       |        Argument data types         
-----+-----------------------------+------------------------------------
nvl  | bit                         | bit, "any"                         
nvl  | blob                        | blob, "any"                        
nvl  | clob                        | clob, "any"                        
nvl  | date                        | date, "any"                        
nvl  | numeric                     | double precision, "any"            
nvl  | numeric                     | integer, "any"                     
nvl  | numeric                     | numeric, "any"                     
nvl  | oracle.date                 | oracle.date, "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_nvl;
 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_nvl;
 pg_typeof | nvl 
-----------+-----
 text      | 1
(1 row)

如果第一个参数是oracle.date类型，则函数返回值类型是oracle.date类型。如果第一个参数是null并且第二个参数类型是oracle.date则函数返回值类型也是oracle.date。

lightdb@oracle=# select pg_typeof(nvl('2024-09-12'::oracle.date, null));
  pg_typeof  
-------------
 oracle.date
(1 row)

lightdb@oracle=# select pg_typeof(nvl(null, '2024-09-12'::oracle.date));
  pg_typeof  
-------------
 oracle.date
(1 row)

  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)