MySQL - 左连接、右连接、内连接、完全外连接、交叉连接 & 一对多、多对一、多对多 & 联合连接

前言

做服务端接口开发时，经常对数据库进行查询操作，了解并掌握数据库的左连接、右连接、内连接、完全外连接、交叉连接，一对多、多对一、多对多，联合连接等概念和使用就很重要了。

首先给出两个数据表，分别为学生表和课程表，假设一个学生只能选一门课程。

表1：Student-------------| ID | Name |-------------| 1  | 阿猫 || 2  | 阿狗 || 3  | 阿猪 |-------------表2：Course-------------| ID | Cname|-------------| 1  | 篮球 || 2  | 足球 || 4  | 排球 |-------------

一、外连接（左外连接、右外连接、完全外连接）

 1.左外连接，简称左连接

语法：left join 或 left outer join

语句：

select * from studentleft join course on student.ID=course.ID

特点：取两表交集且并保留左表

结果：

--------------------------| ID | Name | ID | Cname |--------------------------| 1  | 阿猫 | 1  | 篮球 || 2  | 阿狗 | 2  | 足球 || 3  | 阿猪 |NULL| NULL |--------------------------

  2.右外连接，简称右连接

语法：right join 或 right outer join

语句：

select * from studentright join course on student.ID=course.ID

特点：取两表交集且并保留右表

结果：

--------------------------| ID | Name | ID | Cname |--------------------------| 1  | 阿猫 | 1  | 篮球 || 2  | 阿狗 | 2  | 足球 ||NULL| NULL | 4  | 排球 |--------------------------

 3.完全外连接，简称全连接

语法：full join 或 full outer join

语句：

select * from studentfull join course on student.ID=course.ID

特点：包含左右两表中所有的行

结果：

--------------------------| ID | Name | ID | Cname |--------------------------| 1  | 阿猫 | 1  | 篮球 || 2  | 阿狗 | 2  | 足球 || 3  | 阿猪 |NULL| NULL ||NULL| NULL | 4  | 排球 |--------------------------

二、内连接

 1.内连接

语法：join 或 inner join

语句：

select * from student inner join course on student.ID=course.ID

特点：显示两张表所有记录一一对应，没有对应上的将会被过滤

结果：

--------------------------| ID | Name | ID | Cname |--------------------------| 1  | 阿猫 | 1  | 篮球 || 2  | 阿狗 | 2  | 足球 |--------------------------

三、交叉连接

 1.交叉连接

语法：cross join

语句：

select * from student cross join course

特点：对两个或者多个表进行笛卡儿积操作

结果：

--------------------------| ID | Name | ID | Cname |--------------------------| 1  | 阿猫 | 1  | 篮球 || 2  | 阿狗 | 1  | 篮球 || 3  | 阿猪 | 1  | 篮球 || 1  | 阿猫 | 2  | 足球 || 2  | 阿狗 | 2  | 足球 || 3  | 阿猪 | 2  | 足球 || 1  | 阿猫 | 4  | 排球 || 2  | 阿狗 | 4  | 排球 || 3  | 阿猪 | 4  | 排球 |--------------------------

技巧：如果我们在此时给这条语句加上`where`子句的时候，比如【select * from student cross join course where student.ID = course.ID】，结果和inner join所示执行结果一样

四、联合连接

 1.联合连接

语法：union 或 union all

语句：

select ... from table1unionselect ... from table2

特点：两个或多个的表，相同字段合并起来

五、一对多、多对一、多对多

疑问：当然上面两表为一对一关系，那么如果表A和表B为一对多、多对一或多对多的时候，我们又该如何写连接SQL语句呢？

解答：其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多，只是查询的结果不一样，当然两表也要略有改动。

---------------------|      student      |---------------------| Sno | Name | Cno |----------------------------------------|     classes     |-------------------|  Cno  |  CName  |

1.一对多

场景：一个班级可以有多个学生

语句：

select * from classes where Cno = 1;

结果： 查询班级为一班的所有学生

2.多对一

场景：多个学生属于某个班级

语句：

select c.CName FROM classes as cJOIN student as s ON s.Cno = c.Cno WHERE s.Name in ('小明', '小花', '小白', '小红');

结果： 查询这几个学生所在的班级的名称

3.多对多

场景：一个学生可以选择多门课程，一门课程可以被多个学生选择，因此学生表student和课程表course之间是多对多的关系

技巧：当两表为多对多关系的时候，我们需要建立一个中间表student_course，中间表至少要有两表的主键，当然还可以有别的内容

---------------|   student   |---------------| Sno | Name |-------------------------------|    course    |----------------| Cno | Cname |------------------------------|     SC     |--------------| Sno | Cno |--------------

语句：

selects.Name, c.Cname from student_course as sc left join student as s on s.Sno = sc.Sno left join course as c on c.Cno = sc.Cno

结果： 查询每个学生的姓名，以及所在的班级的名称

六、说明

（1）在MySQL数据库中，left join、right join、full join、inner join的执行速度是怎样的？

答：left join、right join、full join、inner join的执行速度取决于多个因素，包括表的大小、索引的使用情况、查询条件等等。一般来说，inner join的执行速度最快，因为它只返回两个表中匹配的行，而不返回任何不匹配的行。left join和right join的执行速度通常比inner join慢一些，因为它们需要返回匹配和不匹配的行。full join的执行速度通常是最慢的，因为它需要返回两个表中所有的行。