COALESCE处理NULL值

[sql] 
create table plch_employees (   
   employee_id number (4) NOT NULL  
, employee_name varchar2 (10) NOT NULL   
, employee_salary number (7,2) NOT NULL   
, employee_comm number (7,2) );   
  
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)  
                   values ( 7369, 'SMITH' , 800 , NULL);  
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)  
                   values ( 7499, 'ALLEN' , 1600, 300 );  
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)  
                   values ( 7521, 'WARD'  , 1250, 500 );  
insert into plch_employees(employee_id, employee_name, employee_salary, employee_comm)  
                   values ( 7566, 'JONES' , 2975, NULL);  
       这是个非常简单的需求，随手写了一个，发现有问题。
SQL> select e.employee_name,
           e.employee_salary,
           e.employee_comm,
           e.employee_comm + e.employee_salary as total
      from plch_employees e;

EMPLOYEE_N EMPLOYEE_SALARY EMPLOYEE_COMM      TOTAL
---------- --------------- ------------- ----------
SMITH                  800
ALLEN                 1600           300       1900
WARD                  1250           500       1750
JONES                 2975

     这种场景，可以使用coalesce 。
SQL>





 select e.employee_name,
           e.employee_salary,
           e.employee_comm,
           coalesce(e.employee_comm + e.employee_salary, e.employee_salary) as total
      from plch_employees e;

EMPLOYEE_N EMPLOYEE_SALARY EMPLOYEE_COMM      TOTAL
---------- --------------- ------------- ----------
SMITH                  800                      800
ALLEN                 1600           300       1900
WARD                  1250           500       1750
JONES                 2975                     2975

coalesce 解释：
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Similarly,
COALESCE (expr1, expr2, ..., exprn), for n>=3
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END