MySQL的7种join连接，分析+图解

7种join语句的分析：

首先创建两张表：

       People表用于存放学生信息（有：id，姓名，年龄，部门字段）

       Dept表存放部门信息（有：部门编号，部门名称，部门位置字段）

创建脚本如下：

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `deptno` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `people` VALUES (1, '张三', 20, '1');
INSERT INTO `people` VALUES (2, '李四', 23, '1');
INSERT INTO `people` VALUES (3, '王五', 25, '2');
INSERT INTO `people` VALUES (4, '赵柳', 20, '2');
INSERT INTO `people` VALUES (5, '张无忌', 21, '3');
INSERT INTO `people` VALUES (6, '赵敏', 18, '3');
INSERT INTO `people` VALUES (7, '唐三', 19, '3');
INSERT INTO `people` VALUES (8, '赵英俊', 50, '3');
INSERT INTO `people` VALUES (9, '无名', 72, '4');
INSERT INTO `people` VALUES (10, '小舞', 18, '3');
INSERT INTO `people` VALUES (11, '红毛', 17, '3');
INSERT INTO `people` VALUES (12, '叶凡', 20, NULL);

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptno` int(10) NOT NULL AUTO_INCREMENT COMMENT '部门主键',
  `deptname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `dept` VALUES (1, '社会部', '一楼大厅');
INSERT INTO `dept` VALUES (2, '小说部', '二楼会议室');
INSERT INTO `dept` VALUES (3, '动漫部', '三楼小广场');
INSERT INTO `dept` VALUES (4, '其他部', '楼顶办公室');
INSERT INTO `dept` VALUES (5, '市场部', '室外活动室');

创建两张表结果为：

一：内连接

 

SQL语句：

select * from people inner join dept on people.deptno = dept.deptno

得到的结果为：

 

二：左外连接

SQL语句：

select * from people left join dept on people.deptno = dept.deptno

得到的结果为：

 

三：右外连接

SQL语句：

select * from people right join dept on people.deptno = dept.deptno

得到的结果为：

 

四：左连接

SQL语句：

select * from people left join dept on people.deptno = dept.deptno where dept.deptno is null

得到的结果为：

 

五：右连接

SQL语句：

select * from people left join dept on people.deptno = dept.deptno where dept.deptno is null

得到的结果为：

六：全连接

SQL语句：

select * from people right join dept on people.deptno = dept.deptno
union
select * from people left join dept on people.deptno = dept.deptno

得到的结果为：

 

七：两张表都没有出现交集的数据集

SQL语句：

select * from people right join dept on people.deptno = dept.deptno where people.deptno is null
union
select * from people left join dept on people.deptno = dept.deptno where dept.deptno is null

得到的结果为：