MYSQL--基础--11--join理解

2023-10-28

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

总结
1. 两个表的交集外加右表剩下的数据。
2. 从笛卡尔积的角度描述，右连接就是从笛卡尔积中挑出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 共同字段

测试（失败）

在这里插入图片描述

实际上应该有内容

在这里插入图片描述

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)