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