COALESCE 处理NULL值

      现在有需求，计算出每个雇员的收入，收入=工资+福利。

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