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 a.user_id=b.user_id;
字节跳动面试题：full jion的
显示某时间段内，各个角色当日消耗元宝的付费免费比例
先对付费元宝的表进行分组聚合统计 付费消耗的统计
select coalesce(a.p_date,b.p_date) as p_date,
coalesce(a.server_id,b.server_id) as server_id,
coalesce(a.role_id,b.role_id) as role_id,
coalesce(a.cost,0) as a_cost,
coalesce(b.cost,0) as b_cost,
coalesce(a.cost,0)/coalesce(b.cost,0) as rate
from
（select p_date,server_id,role_id,
sum(cost) as cost
from dm_ paid_buy
where p_date between '2021-01-01' and '2021-01-07'
group by p_date,server_id,role_id） as a 在子查询时使用group by 能减少查询的数据
full join 这里一定要用full join 不能只用jion，不然会丢失比例为0的数据
(select p_date,server_id,role_id, 免费消耗的统计
sum(cost) as cost
from dm_ paid_free
where p_date between '2021-01-01' and '2021-01-07'
group by p_date,server_id,role_id) as b
on a.p_date=b.p_date and a.server_id=b.server_id and a.role_id=b.role_id;
将上面两查出来的表关联full join
使用 with as 优化上面语句
with a as(
select p_date,server_id,role_id,
sum(cost) as cost
from dm_ paid_buy
where p_date between '2021-01-01' and '2021-01-07'
group by p_date,server_id,role_id),
b as(
select p_date,server_id,role_id, 免费消耗的统计
sum(cost) as cost
from dm_ paid_free
where p_date between '2021-01-01' and '2021-01-07'
group by p_date,server_id,role_id)
select coalesce(a.p_date,b.p_date) as p_date,
coalesce(a.server_id,b.server_id) as server_id,
coalesce(a.role_id,b.role_id) as role_id,
coalesce(a.cost,0) as a_cost,
coalesce(b.cost,0) as b_cost,
coalesce(a.cost,0)/coalesce(b.cost,0) as rate
from a
full join b
on a.p_date=b.p_date and a.server_id=b.server_id and a.role_id=b.role_id;