mysql七种Join方式

mysql七种Join方式

下面我们创建部门表tbl_dept和员工表tbl_emp对上述7种方式进行逐一实现：

• 部门表：主键id、部门名称deptName，部门楼层locAdd

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

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`)
   -> ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入一些测试数据，表数据如下：

mysql> select * from tbl_emp;
+----+-------+--------+
| id | name  | deptId |
+----+-------+--------+
|  1 | jack  |      1 |
|  2 | tom   |      1 |
|  3 | alice |      2 |
|  4 | john  |      3 |
|  5 | faker |      4 |
|  6 | mlxg  | NULL   |
+----+-------+--------+
6 rows in set (0.10 sec)


mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | 技术部   | 11     |
|  2 | 美工部   | 12     |
|  3 | 总裁办   | 13     |
|  4 | 人力资源 | 14     |
|  5 | 后勤组   | 15     |
+----+----------+--------+
5 rows in set (0.10 sec)

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

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

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 |
+----+----------+------+

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

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

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 | 人力资源 |
|  6 | mlxg  | NULL     |
+----+-------+----------+

3、内连接（AB共有） inner join

查询两表共有的数据：

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即可实现）：

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独有）同理：

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 |
+---------+----------+
| mlxg    | NULL     |
+---------+----------+

6、A独有+B独有

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

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     | mlxg    |
+----------+---------+

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

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

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     | mlxg    |
+----------+---------+