七种Join方式

20.mysql高级 - 七种join方式 - 图1
下面我们创建部门表tbl_dept和员工表tbl_emp对上述7种方式进行逐一实现：

  • 部门表：主键id、部门名称deptName，部门楼层locAdd | 123456 | mysql> CREATE TABLE tbl_dept ( -> id INT(11) NOT NULL AUTO_INCREMENT, -> deptName VARCHAR(30) DEFAULT NULL, -> locAdd VARCHAR(40) DEFAULT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; | | :—- | :—- |
  • 员工表：主键id，姓名name、所属部门deptId | 12345678 | mysql> CREATE TABLE tbl_emp ( -> id INT(11) NOT NULL AUTO_INCREMENT, -> name VARCHAR(20) DEFAULT NULL, -> deptId INT(11) DEFAULT NULL, -> PRIMARY KEY (id), -> KEY fk_dept_id (deptId) -> #CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES tbl_dept (id) -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; | | :—- | :—- |


插入一些测试数据：

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758 mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘技术部’,11);Query OK, 1 row affected (0.07 sec)mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘美工部’,12);Query OK, 1 row affected (0.08 sec)mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘总裁办’,13);Query OK, 1 row affected (0.06 sec)mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘人力资源’,14);Query OK, 1 row affected (0.11 sec)mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES(‘后勤组’,15);Query OK, 1 row affected (0.10 sec)mysql> insert into tbl_emp(name,deptId) values(‘jack’,1);Query OK, 1 row affected (0.11 sec)mysql> insert into tbl_emp(name,deptId) values(‘tom’,1);Query OK, 1 row affected (0.08 sec)mysql> insert into tbl_emp(name,deptId) values(‘alice’,2);Query OK, 1 row affected (0.08 sec)mysql> insert into tbl_emp(name,deptId) values(‘john’,3);Query OK, 1 row affected (0.13 sec)mysql> insert into tbl_emp(name,deptId) values(‘faker’,4);Query OK, 1 row affected (0.10 sec)mysql> insert into tbl_emp(name) values(‘mlxg’);Query OK, 1 row affected (0.13 sec)mysql> select from tbl_dept;+——+—————+————+| id | deptName | locAdd |+——+—————+————+| 1 | 技术部 | 11 || 2 | 美工部 | 12 || 3 | 总裁办 | 13 || 4 | 人力资源 | 14 || 5 | 后勤组 | 15 |+——+—————+————+5 rows in set (0.00 sec)mysql> select from tbl_emp;+——+———-+————+| id | name | deptId |+——+———-+————+| 1 | jack | 1 || 2 | tom | 1 || 3 | alice | 2 || 4 | john | 3 || 5 | faker | 4 || 7 | ning | NULL || 8 | mlxg | NULL |+——+———-+————+7 rows in set (0.00 sec)


两表的关联关系如图所示：
20.mysql高级 - 七种join方式 - 图2

1、左连接（A独有+AB共有）

查询所有部门以及各部门的员工数：

1234567891011 mysql> select t1.id,t1.deptName,count(t2.name) as emps from tbl_dept t1 left join tbl_emp t2 on t2.deptId=t1.id group by deptName order by id;+——+—————+———+| id | deptName | emps |+——+—————+———+| 1 | 技术部 | 2 || 2 | 美工部 | 1 || 3 | 总裁办 | 1 || 4 | 人力资源 | 1 || 5 | 后勤组 | 0 |+——+—————+———+5 rows in set (0.00 sec)

2、右连接（B独有+AB共有）

查询所有员工及其所属部门：

12345678910111213 mysql> select t2.id,t2.name,t1.deptName from tbl_dept t1 right join tbl_emp t2 on t2.deptId=t1.id;+——+———-+—————+| id | name | deptName |+——+———-+—————+| 1 | jack | 技术部 || 2 | tom | 技术部 || 3 | alice | 美工部 || 4 | john | 总裁办 || 5 | faker | 人力资源 || 7 | ning | NULL || 8 | mlxg | NULL |+——+———-+—————+7 rows in set (0.04 sec)

3、内连接（AB共有）

查询两表共有的数据：

12345678910 mysql> select deptName,t2.name empName from tbl_dept t1 inner join tbl_emp t2 on t1.id=t2.deptId;+—————+————-+| deptName | empName |+—————+————-+| 技术部 | jack || 技术部 | tom || 美工部 | alice || 总裁办 | john || 人力资源 | faker |+—————+————-+

4、A独有

即在（A独有+AB共有）的基础之上排除B即可（通过b.id is null即可实现）：

123456 mysql> select a.deptName,b.name empName from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null;+—————+————-+| deptName | empName |+—————+————-+| 后勤组 | NULL |+—————+————-+

5、B独有

与（A独有）同理：

1234567 mysql> select a.name empName,b.deptName from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;+————-+—————+| empName | deptName |+————-+—————+| ning | NULL || mlxg | NULL |+————-+—————+

6、A独有+B独有

使用union将（A独有）和（B独有）联合在一起：

12345678 mysql> select a.deptName,b.name empName from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null union select b.deptName,a.name emptName from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;+—————+————-+| deptName | empName |+—————+————-+| 后勤组 | NULL || NULL | ning || NULL | mlxg |+—————+————-+

7、A独有+AB公共+B独有

使用union（可去重）联合（A独有+AB公共）和（B独有+AB公共）

12345678910111213 mysql> select a.deptName,b.name empName from tbl_dept a left join tbl_emp b on a.id=b.deptId union select a.deptName,b.name empName from tbl_dept a right join tbl_emp b on a.id=b.deptId;+—————+————-+| deptName | empName |+—————+————-+| 技术部 | jack || 技术部 | tom || 美工部 | alice || 总裁办 | john || 人力资源 | faker || 后勤组 | NULL || NULL | ning || NULL | mlxg |+—————+————-+

————————————————

原文链接：https://zhenganwen.top/posts/9982416a/