Mysql - join类型和实现的效果

目录

1、inner join

    笛卡尔积

    交集

2、left join

    左表独有，右边不足处用NULL补充

    左边独有

3、right join

    右表为准，左边不足用NULL补充

    右边独有

4、Mysql使用union实现类似Oracle的 full outer join的效果

    并集去重

    并集不去重

    并集去交集【左右表独有】

---

    记得又一次面试问道，需要查询类似Oracle的 full outer join的效果，即 join 查询只有左、右独有的数据。当时就完全懵逼了，现在对Mysql的所有join方式进行整理，下一篇博客对join的具体执行流程进行分析和join的性能优化。为了方便对每种join效果进行数据分析显示，下面先创建两张表，并且添加部分数据：

create table user(
    id int(11) not NULL COMMENT '用户id',
    name VARCHAR(50) not null COMMENT '用户名称',
    dept_id int(11) not null COMMENT '部门id'
);
insert into user VALUES (1,'user-1',1),(2,'user-2',2),(3,'user-3',3),(4,'user-4',4);

create table dept(
    id int(11) not null COMMENT '部门id',
    name VARCHAR(20) not null COMMENT '部门名称'
);
insert into dept VALUES(2,'dept-2'),(3,'dept-3'),(4,'dept-4'),(5,'dept-5');

1、inner join

    笛卡尔积

select * from user inner join dept

    交集

select * from user inner join dept on user.dept_id = dept.id

 

2、left join

    左表独有，右边不足处用NULL补充

select * from user left join dept on user.dept_id = dept.id

    左边独有

select * from user left join dept on user.dept_id = dept.id where dept.id is null

 

3、right join

    

    右表为准，左边不足用NULL补充

select * from user right join dept on user.dept_id = dept.id 

    右边独有

select * from user right join dept on user.dept_id = dept.id where user.dept_id is null

 

4、Mysql使用union实现类似Oracle的 full outer join的效果

    并集去重

select * from user left join dept on user.dept_id = dept.id
UNION
select * from user right join dept on user.dept_id = dept.id

    并集不去重

select * from user left join dept on user.dept_id = dept.id
UNION ALL
select * from user right join dept on user.dept_id = dept.id

    并集去交集【左右表独有】

select * from user left join dept on user.dept_id = dept.id where dept.id is null
UNION All
select * from user right join dept on user.dept_id = dept.id where user.dept_id is null