SQL Hive - Replace null values with 0 (Hadoop Hive)
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 cherryfrom 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.