Sql语句，join常见用法

left join ：将两表相联，交集部分显示，左表一定是全显示，右表没有数据的显示null
on作为两表关联条件
select a.*
    if(b.user_id is not null,"YES","NO") as flag
from all_users a
left join black_list black_list
on a.id=b.id;    

left semi join ：两表相联，取两表的交集数据，只显示左表字段，不显示右表字段，只显示两表交集的左表的字段数据
select *
from all_users a
left semi join black_list b 
on a.id=b.id;

left anti join ：两表相联，取非交集部分    将在海量数据集表里面，剔除掉无关业务的其他数据
select *
from all_users a
left anti join black_list b 
on a.d=b.id;

full join ：两表关联，并显示两表的全部数据    

coalesce()函数：返回第一个非空参数，解决full join出现的交叉出现null的情况

select coalesce(a.user_id , b.user_id) as user_id_new ,
       coalesce(a.amount,0)as deposit_amount,
       coalesce(b.amount,0)as deposit_amount,
from deposit a
full join debt b 
on