Coalesce usage based on hive

Syntax: coalesce (T V1, t V2 ,...)
The first non-null value in the returned parameter. If all values are null, null is returned.

Take the EMP table as an example:

desc emp;empno                   int                     None                ename                   string                  None                job                     string                  None                mgr                     int                     None                hiredate                string                  None                sal                     double                  None                comm                    double                  None                deptno                  int                     None                

 

select * from emp;7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    307782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    207900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    307902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    207934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

The comm field has a null value (null)

select empno,ename,job,mgr,hiredate,sal, COALESCE(comm, 0),deptno from emp;7369    SMITH   CLERK   7902    1980-12-17      800.0   0.0     207499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   307521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   307566    JONES   MANAGER 7839    1981-4-2        2975.0  0.0     207654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  307698    BLAKE   MANAGER 7839    1981-5-1        2850.0  0.0     307782    CLARK   MANAGER 7839    1981-6-9        2450.0  0.0     107788    SCOTT   ANALYST 7566    1987-4-19       3000.0  0.0     207839    KING    PRESIDENT       NULL    1981-11-17      5000.0  0.0     107844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     307876    ADAMS   CLERK   7788    1987-5-23       1100.0  0.0     207900    JAMES   CLERK   7698    1981-12-3       950.0   0.0     307902    FORD    ANALYST 7566    1981-12-3       3000.0  0.0     207934    MILLER  CLERK   7782    1982-1-23       1300.0  0.0     10

 

Observe the comm field value.

 

Coalesce usage based on hive