Join系列-Inner,Left,Right
数据准备
create table all_users(
id int comment '用户id',
name VARCHAR(20) comment '用户姓名',
sex VARCHAR(20) comment '性别',
age int comment '年龄'
) comment '银行用户信息表';
insert into all_users values
(1,'张三','男',20),
(2,'李四','男',29),
(3,'王五','男',21),
(4,'赵六','女',28),
(5,'田七','女',22);
select * from all_users;
create table black_list(
user_id int comment '用户编号',
type VARCHAR(20) comment '风控类型'
)comment '银行黑名单信息表';
insert into black_list values
(1,'诈骗'),
(2,'逾期'),
(3,'套现');
select * from black_list;
Inner Join
得到两个表的公共部分
查询黑名单用户
-- 写法一
select *
from all_users a
inner join black_list b
on a.id=b.user_id;
-- 写法二,省略inner
select *
from all_users a
join black_list b
on a.id=b.user_id;
-- 写法三
select *
from all_users a ,black_list b
where a.id=b.user_id;
Left Join
使用Left Join判断是否在黑名单中
select a.*,
if(b.user_id is not null, 'YES', 'NO') `黑名单用户`
from all_users a
left join black_list b
on a.id = b.user_id;
Right Join
上述问题,使用Right Join再做一遍
select b.*,
if(a.user_id is not null, 'YES', 'NO') flag
from black_list a
right join all_users b
on a.user_id = b.id;
左连接和右连接区别不大,掌握好Left Join 即可。