coalesce的用法（将join后为null的值设置一个默认值）

需求:C表的uid和B表的id进行关联,如果关联上则取值为’是’,否则为’否’

c.txt
1,北京
2,上海
3,合肥

b.txt
2,嘉定
3,六安
4,昌平

--建表语句
create table test.tb_c (
uid int ,
addr string 
)
row format delimited fields terminated by ',' ;
load data local inpath '/doit17/c.txt' into table tb_c ;


create table test.tb_b(
id int ,
addr string 
)
row format delimited fields terminated by ',';
load data local inpath '/doit17/b.txt' into table tb_b;

C表 left join B表的时候

select 
tb_c.addr,
tb_b.addr
from 
tb_c left join 
tb_b
on 
tb_c.uid =tb_b.id

+------------+------------+
| tb_c.addr  | tb_b.addr  |
+------------+------------+
| 北京         | NULL     |
| 上海         | 嘉定     |
| 合肥         | 六安     |
+------------+------------+

加上coalesce

select 
tb_c.addr,
coalesce( tb_b.addr,'空') as addr 
from 
tb_c left join 
tb_b
on 
tb_c.uid =tb_b.id


+------------+-------+
| tb_c.addr  | addr  |
+------------+-------+
| 北京         | 空   |
| 上海         | 嘉定 |
| 合肥         | 六安 |
+------------+-------+

但是coalesce是多个条件进行判断的,有的情景可能不适用