MySql 7大join连接关系

小鱼儿 2022-06-06 03:49 154阅读 0赞

join连接是sql编写过程经常使用的连接表的方式，用于表集合的计算。这里可以把表当成数据的集合，其实sql的计算过程，就是集合的运算。集合的运算有笛卡尔积，交，差，并，补运算，可以依次从join中体现出来。

数据准备：建立两张表部门表与员工表

create table dept (
    id int PRIMARY key auto_increment,
    dept_name varchar(50)
    )engine=INNODB auto_increment = 1 default charset utf8;
    create table employee(
    emp_id int PRIMARY key auto_increment,
    emp_name varchar(50),
    dept_id int
    )engine=INNODB auto_increment = 1 default charset utf8;
    
    insert into dept (dept_name) values ('开发部'),('运营部'),('销售部'),('人事部'),('财务部'),('总经理办公室'),('后勤部'),('市场部');
    insert into employee (emp_name,dept_id) values ('方祖阳','1'),('张信杰','1'),('周思蒙','1'),('卜奕炜','1'),('焦宇阳','1'),
    ('杨仁翔','2'),('赵睿','2'),('杨志康','2'),('陈智轩','2'),('汪豪','3'),('阚志伟','3'),('朱畅',13),('刘海笛','4'),('吴迪','5');

将两张表数据全部筛选出来

select \* from employee;

![Image 1][]

select \* from dept;  
![Image 1][]

将员工表的数据当成A集合，部门表当成B集合

**第一种情况：将A表所有数据保留**

![SouthEast][]

查看所有员工的部门信息

select * from employee a left join dept b on a.dept_id = b.id;

即使B表中没有的数据也会被筛选出来。

**第二种情况：只保留A表独有的数据**

![SouthEast 1][]

筛选没有部门的员工信息

select * from employee a left join dept b on a.dept_id = b.id where b.id is null;

![Image 1][]

**第三种情况： 保留B表中的所有数据**

![SouthEast 2][]

筛选所有部门的人员信息，即使部门内没有人也筛选出来

select * from employee a right join dept b on a.dept_id = b.id ;

![Image 1][]

**第四种情况：只筛选B集合独有的数据，即第三种情况中标红的数据**

![SouthEast 3][]

筛选没有员工的部门

select * from employee a right join dept b on a.dept_id = b.id where a.dept_id is null;

![Image 1][]

**第五种情况：交集，**

![SouthEast 4][]

筛选已有部门的员工信息 inner join

select * from employee a inner join dept b on a.dept_id = b.id ;

等同于

select * from employee a ,dept b where a.dept_id = b.id ;

![Image 1][]

**第六种情况：A集合独有的和B集合独有的**

![SouthEast 5][]

筛选没有部门的员工和没有员工的部门。第三种情况和第四种情况的并集

select * from employee a left join dept b on a.dept_id = b.id where b.id is NULL
    UNION
    select * from employee a right join dept b on a.dept_id = b.id where a.dept_id is null;

但是与Oracle来说有更简单的方式 full outer join 但是mysql不支持这种关键字。

select * from employee a full outer join dept b on a.dept_id = b.id where b.id is null or a.dept_id is null
     
     
     
    
     
     第七种情况：全集
     在Oracle中可以简单的用这种方式
     
     select * from employee a full outer join dept b on a.dept_id = b.id但是MySql不支持，还是用union关键字，并集，并且只保留一份重复的数据
     
     
     select * from employee a left join dept b on a.dept_id = b.id 
    union
    select * from employee a right join dept b on a.dept_id = b.id

[Image 1]: 
[SouthEast]: /images/20220606/bdb7a7b638f642b6b21b9790488d9250.png
[SouthEast 1]: /images/20220606/7642d91409b74976baee445b1a59fccd.png
[SouthEast 2]: /images/20220606/3b75804b86824c44a65468ed25393ac3.png
[SouthEast 3]: /images/20220606/7fb89e0e191a4c678d4c2c6cce65e0de.png
[SouthEast 4]: /images/20220606/aae62a33cd554be7adbcf5a85f97a936.png
[SouthEast 5]: /images/20220606/18f6515a23264a18bb4364f175e28d82.png