SQL Hive - Replace null values with 0 (Hadoop Hive)

Question

After conducting a left join, I was left with many null (empty) values. How may I replace those null values with a 0 in only certain columns within the same query?

select 
    m1.*, t2.Apple, t3.Berry, t4.Cherry
from
    table1 as t1
    left join table2 as t2 on t1.id = t2.id
    left join table3 as t3 on t1.id = t3.id
    left join table3 as t4 on t1.id = t4.id
;

Example Output

ID  Apple     Berry    Cheery
1   1         NULL     1
2   1         NULL     NULL
3   NULL      1        NULL
4   NULL      NULL     NULL

score 5 · Accepted Answer · answered Sep 14 '20 at 21:22

You can use coalesce() to replace null values with 0s:

select 
    t1.*, 
    coalesce(t2.Apple,  0) as apple, 
    coalesce(t3.Berry,  0) as berry, 
    coalesce(t4.Cherry, 0) as cherry
from
    table1 as t1
    left join table2 as t2 on t1.id = t2.id
    left join table3 as t3 on t1.id = t3.id
    left join table4 as t4 on t1.id = t4.id
;

Note that this assumes that all 3 fruit columns are of a numeric datatype.

Side note: I fixed a few typos on table aliases in your original query.

SQL Hive - Replace null values with 0 (Hadoop Hive)

1 Answers1