5、MySQL 七种 Join 形式分析

5、MySQL 七种 Join 形式分析

5.1、建表

CREATE TABLE `t_dept` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR ( 30 ) DEFAULT NULL,
	`address` VARCHAR ( 40 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;
CREATE TABLE `t_emp` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR ( 20 ) DEFAULT NULL,
	`age` INT ( 3 ) DEFAULT NULL,
	`deptId` INT ( 11 ) DEFAULT NULL,
	`empno` INT ( 11 ) NOT NULL,
	PRIMARY KEY ( `id` ),
	KEY `idx_dept_id` ( `deptId` )
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

5.2、Inner Join 形式

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;
1

解释：

• 循环 emp 表，拿 emp 表中的每一行数据，分别与 dept 表中的数据，满足条件的记录下来；

5.3、Left Join 形式

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id;
1

解释：

• 第一步：先执行 Inner Join 的操作；
• 最后，将 emp 中剩下的数据，记录下来且将 dept 部分置 NULL；

5.4、Right Join 形式

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;
1

解释：

• 第一步：先执行 Inner Join 的操作；
• 最后，将 dept 中剩下的数据，记录下来且将 emp 部分置 NULL；

5.5、Union All 形式

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
UNION ALL
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;
1
2
3

解释：

• union all 是将 两个 sql 语句的查询结果合并在一起（不会去除重复行）；

5.6、Union 形式

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
UNION 
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id;
1
2
3

解释：

• 和 union all 类似，但是 union 自动去除重复行；