MYSQL–基础–11–join理解
1、数据初始化
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '课程id',
`name` varchar(10) COMMENT '课程名称',
`tid` bigint(0) COMMENT '教师id',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '课程表' ;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`sid` bigint(0) COMMENT '学生id',
`cid` bigint(0) COMMENT '课程id',
`score` int(0) COMMENT '分数',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '成绩表' ;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 80);
INSERT INTO `score` VALUES (2, 1, 2, 90);
INSERT INTO `score` VALUES (3, 1, 3, 99);
INSERT INTO `score` VALUES (4, 2, 1, 70);
INSERT INTO `score` VALUES (5, 2, 2, 60);
INSERT INTO `score` VALUES (6, 2, 3, 80);
INSERT INTO `score` VALUES (7, 3, 1, 80);
INSERT INTO `score` VALUES (8, 3, 2, 80);
INSERT INTO `score` VALUES (9, 3, 3, 80);
INSERT INTO `score` VALUES (10, 4, 1, 50);
INSERT INTO `score` VALUES (11, 4, 2, 30);
INSERT INTO `score` VALUES (12, 4, 3, 20);
INSERT INTO `score` VALUES (13, 5, 1, 76);
INSERT INTO `score` VALUES (14, 5, 2, 87);
INSERT INTO `score` VALUES (15, 6, 1, 31);
INSERT INTO `score` VALUES (16, 6, 3, 34);
INSERT INTO `score` VALUES (17, 7, 2, 89);
INSERT INTO `score` VALUES (18, 7, 3, 98);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '学生id ',
`name` varchar(10) COMMENT '姓名',
`birth_date` datetime(0) COMMENT '出生日期',
`sex` varchar(10) COMMENT '性别',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '学生表' ;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTO `student` VALUES (2, '钱电', '1990-12-21 00:00:00', '男');
INSERT INTO `student` VALUES (3, '孙风', '1990-12-20 00:00:00', '男');
INSERT INTO `student` VALUES (4, '李云', '1990-12-06 00:00:00', '男');
INSERT INTO `student` VALUES (5, '周梅', '1991-12-01 00:00:00', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-01-01 00:00:00', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1989-01-01 00:00:00', '女');
INSERT INTO `student` VALUES (9, '张三', '2017-12-20 00:00:00', '女');
INSERT INTO `student` VALUES (10, '李四', '2017-12-25 00:00:00', '女');
INSERT INTO `student` VALUES (11, '李四', '2012-06-06 00:00:00', '女');
INSERT INTO `student` VALUES (12, '赵六', '2013-06-13 00:00:00', '女');
INSERT INTO `student` VALUES (13, '孙七', '2014-06-01 00:00:00', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '教师id',
`name` varchar(10) COMMENT '教师名称',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '教师表' ;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');
INSERT INTO `teacher` VALUES (4, '赵六');
INSERT INTO `teacher` VALUES (5, '田七');
SET FOREIGN_KEY_CHECKS = 1;
2、笛卡尔积:CROSS JOIN
要理解各种JOIN首先要理解笛卡尔积。
笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。
也就是说 A 表有5条记录,B表有10条记录,那么笛卡尔积就是50条记录
2.1、有五种产生笛卡尔积的方式如下。
SELECT * FROM course CROSS JOIN teacher;
SELECT * FROM course INNER JOIN teacher;
SELECT * FROM course,teacher;
SELECT * FROM course NcourseTURE JOIN teacher;
select * from course NcourseTURcourse join teacher;
3、介绍
join就是表连接,包括内连接,外连接,右连接,左连接,自然连接,自连接
4、JOIN的执行顺序
一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。
4.1、以下是JOIN查询的通用结构
SELECT <row_list>
FROM <A>
<inner|left|right> JOIN <B>
ON <join condition>
WHERE <where_condition>
它的执行依次如下(SQL语句里第一个被执行的总是FROM子句):
01、 FROM:
- 对左右两张表执行笛卡尔积,产生第一张表vt1。
- 行数为n*m(n为左表的行数,m为右表的行数
02、 ON
- 表vt1 按照 ON的条件逐行依次筛选,将筛选结果放到表vt2中
- vt2表其实是 A,B 表的交集
03、 JOIN
-
如果指定了LEFT JOIN(LEFT OUTER JOIN),会添加外部行,先遍历一遍左表的每一行,将左表未出现在vt2的行插入进vt2,每一行的剩余字段将被填充为NULL,形成vt3
-
如果指定了RIGHT JOIN,参考1。
-
如果指定的是INNER JOIN,则不会添加外部行,上述插入过程被忽略,vt2=vt3,所以INNER JOIN的过滤条件放在ON或WHERE里 执行结果是没有区别的。
04、 WHERE
对vt3进行条件过滤,满足条件的行被输出到vt4
05、 SELECT
取出vt4的指定字段到vt5
4.2、LEFT JOIN 举例
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on c.tid=t.id
WHERE c.id=1
表数据如下
第1步:执行FROM子句对两张表进行笛卡尔积操作
笛卡尔积操作后会返回两张表中所有行的组合,左表course有3行,右表teacher有5行,生成的虚拟表vt1就是3*5=15行。
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on 1
第2步:执行ON子句过滤掉不满足条件的行
on c.tid=t.id
预期结果
第3步:JOIN 添加外部行
LEFT JOIN会将左表未出现在vt2的行插入进vt2,每一行的剩余字段将被填充为NULL,RIGHT JOIN同理
本例中用的是LEFT JOIN,所以会将左表teacher剩下的行都添上,生成表vt3:
第4步:WHERE条件过滤
WHERE c.id=1 生成表vt4
第5步:SELECT
SELECT t.*,c.* 生成vt5
虚拟表vt5作为最终结果返回给客户端
因为我们直接显示所有的信息,如果我们select的是 t.name,那么vt5就是这样的
5、INNER/LEFT/RIGHT/FULL/OUTER JOIN的区别
INNER JOIN…ON…:
- 返回 左右表互相匹配的所有行(因为只执行上文的第二步ON过滤,不执行第三步 添加外部行)
LEFT JOIN…ON…:
返回左表的所有行,若某些行在右表里没有相对应的匹配行,则将右表的列在新表中置为NULL
2. 总结
1. 求两个表的交集外加左表剩下的数据
2. 从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录
RIGHT JOIN…ON…:
返回右表的所有行,若某些行在左表里没有相对应的匹配行,则将左表的列在新表中置为NULL
- 总结
- 两个表的交集外加右表剩下的数据。
- 从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录
5.1、INNER JOIN
拿上文的第3步添加外部行来举例,若LEFT JOIN替换成INNER JOIN,则会跳过第3步,生成的表vt3与vt2一模一样
SELECT t.*,c.*
FROM teacher t
INNER JOIN course c
on c.tid=t.id
5.2、RIGHT JOIN
为了测试,我添加一条数据
INSERT INTO `course`(`id`, `name`, `tid`) VALUES (4, '测试', 999);
备注:我用完就删掉了
若LEFT JOIN替换成RIGHT JOIN,则生成的表vt3如下
SELECT t.*,c.*
FROM teacher t
RIGHT JOIN course c
on c.tid=t.id
因为course(右表)里存在id=4这一行,而teacher(左表)里却找不到这一行的记录(tid=999),所以会在第三步插入以下一行
5.3、FULL JOIN
标准SQL定义了FULL JOIN,但在mysql里是不支持的,不过我们可以通过
LEFT JOIN
+ UNION
+ RIGHT JOIN
来实现FULL JOIN
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on c.tid=t.id
UNION
SELECT t.*,c.*
FROM teacher t
RIGHT JOIN course c
on c.tid=t.id
5.4、OUTER JOIN
- 求两个集合的并集。
- 从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。
- MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做UNION操作来实现。
为了测试,我添加一条数据
INSERT INTO `course`(`id`, `name`, `tid`) VALUES (4, '测试', 999);
备注:我用完就删掉了
sql
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on c.tid=t.id
UNION
SELECT t.*,c.*
FROM teacher t
RIGHT JOIN course c
on c.tid=t.id
6、ON和WHERE的区别
举例说明
-- 第1种情况
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on c.tid=t.id and c.id=1;
-- 第2种情况
SELECT t.*,c.*
FROM teacher t
LEFT JOIN course c
on c.tid=t.id
WHERE c.id=1;
6.1、第1种情况
LEFT JOIN在执行完第二步ON子句后,筛选出满足c.tid=t.id and c.id=1 的行,生成表vt2,然后执行第三步JOIN子句,将外部行添加进虚拟表生成vt3
vt2 虚拟表
vt3 虚拟表
6.2、第2种情况
LEFT JOIN在执行完第二步ON子句后,筛选出满足 c.tid=t.id的行,生成表vt2;
再执行第三步JOIN子句添加外部行生成表vt3;
然后执行第四步WHERE子句,再对vt3表进行过滤生成vt4
vt2 虚拟表
vt3 虚拟表
vt4 虚拟表
如果将上例的LEFT JOIN替换成INNER JOIN,不论将条件过滤放到ON还是WHERE里,结果都是一样的,因为INNER JOIN不会执行第三步添加外部行
7、USING子句
MySQL中连接SQL语句中,ON子句的语法格式为:A.key1 = B.key2
USING是对ON 语法的简化,当key1和key2名称相同的时候,就可以使用USING。
SELECT *时,USING会去除USING指定的列,而ON不会。
7.1、key1!=key2
7.2、key1==key2
7.3、SELECT *时,USING会去除USING指定的列,而ON不会。
8、自然连接:NATURE JOIN
自然连接就是USING子句的简化版,它找出两个表中相同的列作为连接条件进行连接。
有左自然连接,右自然连接和普通自然连接之分。在teacher和course示例中,两个表相同的列是id,所以会拿id作为连接条件。
natural join
相当于 inner join on 共同字段
natural left join
相当于 left join on 共同字段
natural right join
相当于 right join on 共同字段