SQL联结

1. 联结

SQL 最强大的功能之一就是能在数据检索查询的执行中联结表。

1、什么叫联结？

我们可以举一个生活中的例子来理解“联结”：

👇

在大学每一个新学期即将开始的时候，我们都会遇到一个大难题，那就是选课。

假如所有课程的信息和所有教师的信息都存放在同一张表中，我们会发现这显得很繁琐且不便于信息的更改。因为同一个教师开设的不同课程对应的教师信息都是相同的，当教师信息改变时，需要修改多条课程。

因此，为了同学们在选课时操作更便利，我们根据内容将所有数据分解为了两个表，即教师表和课程表。
我们可以将他们称为关系表。关系表就是一类数据一个表，各表通过某些关系互相关联。

🤔
那么问题就来了，数据存储在两个表中，如果我们想用一条 SELECT 语句同时查询课程信息和对应的教师信息该怎么办呢？

答案就是——使用联结。

简单地说，联结是一种机制，用于在一条 SELECT 语句中关联多个表，返回一组输出。

这个时候就要说一下联结中的两大主角——主键（PRIMARY KEY）和外键（FOREIGN KEY）。

以我们使用的教师表和课程表为例：

表1：courses (课程表)

列名	类型	注释
id	int unsigned	主键
name	varchar	课程名称
student_count	int	学生总数
created_at	date	创建课程时间
teacher_id	int	讲师 id

表2：teachers (教师表)

列名	类型	注释
id	int	主键
name	varchar	讲师姓名
email	varchar	讲师邮箱
age	int	讲师年龄
country	varchar	讲师国籍

教师表 teachers 中包含所有教师的信息，每个教师具有唯一的标识，这个标识被称为主键（PRIMARY KEY），可以为 id 或其他唯一值。我们这里选择教师编号（id）为教师表的主键。

而在课程表 courses 中，除了教师编号 teacher_id（即教师表的主键）外不存储任何教师的信息。则教师编号为课程表的外键（FOREIGN KEY）。

教师表的主键又叫作课程表的外键，因此这两个表通过教师编号这一列关联了。

---

2、怎么创建联结？

🤔 那我们又该如何创建联结呢？

So easy！

👉 规定要联结的所有表以及它们如何关联就可以了。

在设置关联条件时，为避免不同表被引用的列名相同，我们需要使用完全限定列名（用一个点分隔表名和列名），否则会返回错误。

用法如下：

`table1`.`common_field` = `table2`.`common_field`

在本例中，教师表的主键 id 为设置的关联条件为：

`teachers`.`id` = `courses`.`teacher_id` 

---

3、JOIN 连接子句

SQL JOIN 连接子句用于将数据库中两个或者两个以上表中的记录组合起来。其类型主要分为 INNER JOIN（内连接）、OUTER JOIN（外连接）、全连接（FULL JOIN）和交叉连接（CROSS JOIN），其中 OUTER JOIN 又可以细分为 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）。

因此，我们主要使用的 JOIN 连接类型如下：

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行
• CROSS JOIN：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积

2. 内连接 INNER JOIN

---

最常用也最重要的多表联结类型就是 INNER JOIN（内连接），有时候也被称作 EQUIJOIN（等值连接）。

内连接根据联结条件来组合两个表中的字段，以创建一个新的结果表。假如我们想将表 1 和表 2 进行内连接，SQL 查询会逐个比较表 1 和表 2 中的每一条记录，来寻找满足联结条件的所有记录对。当联结条件得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

简单的说，内连接就是取两个表的交集，返回的结果就是连接的两张表中都满足条件的部分。

---

基本语法

在对 INNER JOIN（内连接）的概念有基本的了解之后，我们再来学习一下它的基本语法。
基本语法有如下两种写法：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

或

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

注：INNER JOIN 中 INNER 可以省略不写

其中，语法的核心部分如下所示：

FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field

table1 和 table2 是内连接的两个表名，table1.common_field 和 table2.common_field
需要注意的是，联结条件需使用特定的 ON 子句给出。

 

• courses c 等同于 courses AS c ，给courses 表取别名为 c
• teachers t 等同于 teachers AS t ，给 teachers 表取别名为 t
• INNER JOIN 也可写作 JOIN

执行以上代码输出结果如下：

mysql> SELECT `c`.`id`, `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
    -> FROM `courses` `c`
    ->  INNER JOIN `teachers` `t` ON `c`.`teacher_id` = `t`.`id`;
+----+-------------------------+------------------+
| id | course_name             | teacher_name     |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | NULL             |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
+----+-------------------------+------------------+
10 rows in set (0.05 sec)

将课程表 courses 和教师表 teachers 进行内连接，查询 “Eastern Heretic” 老师所教的所有课程的课程名和课程编号 , 且结果列名分别以课程编号 id 、课程名称 course_name 和教师姓名 teacher_name 显示

SELECT `c`.`id`, `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`

FROM `courses` `c`

INNER JOIN `teachers` `t` ON `c`.`teacher_id` = `t`.`id`

WHERE `t`.`name` = 'Eastern Heretic';

 

3. 外连接 OUTER JOIN

外连接在生活中是经常用到的，外连接也是针对于两张表格之间，比如我们实际应用过程会发现，会有一些新任职的教师，还在实习期，并无对应课程安排，那若是按照上一节使用内连接的话，这些教师的课程信息将无法导出来，我们应该如何操作呢？这个就要用到我们的外连接，外连接可以将某个表格中，在另外一张表格中无对应关系，但是也能将数据匹配出来。

在MySQL中，外连接查询会返回所操作的表中至少一个表的所有数据记录。在MySQL中，数据查询通过SQL语句 “OUTER JOIN…ON” 来实现，外连接查询可以分为以下三类：

• 左外连接

• 右外连接

• 全外连接

外连接数据查询语法如下：

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;

在上述语句中，参数 column_name 表示所要查询的字段名字，来源于所连接的表 table1 和 table2，关键字 OUTER JOIN 表示表进行外连接，参数 CONDITION 表示进行匹配的条件。

左外连接（左连接）：表示左边的这张表是主表。

右外连接（右连接）：表示右边的这张表是主表。

判断何时使用left join或者right join？
关键点：分析出谁是主表！上例中，dept是主表，emp是副表，当副表emp中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
from后+主表dept，left join后+副表 emp
from后+副表emp，right join后+主表dept

将教师表 teachers 和课程表 courses 进行左连接，查询来自中国（讲师国籍 country ='CN' ）的教师名称以及所教课程名称，结果列名请分别以课程名称 course_name ，教师名称 teacher_name 显示

select c.name course_name, t.name teacher_name

from teachers t left join courses c

on t.id = c.teacher_id

where t.country = 'CN';

 

将课程表 courses 和教师表 teachers 进行右连接，查询来自中国（讲师国籍 country ='CN' ）的教师姓名，邮箱以及所教课程名称，结果列名请分别以课程名称 course_name ，教师名称 teacher_name ，教师邮箱 teacher_email 显示。

 

select c.name course_name, t.name teacher_name, t.email teacher_email

from courses c right join teachers t

on t.id = c.teacher_id

where t.country = 'CN';

 

将课程表 courses 和教师表 teachers 进行全外连接，查询所有课程名称以及与其相互对应的教师名称和教师国籍，结果列名请分别以课程名称 course_name 、教师名称 teacher_name 、教师国籍 teacher_country 显示。

SELECT c.name AS course_name, t.name AS teacher_name, t.country AS teacher_country

FROM courses c

LEFT JOIN teachers t ON c.teacher_id = t.id

UNION

SELECT c.name AS course_name, t.name AS teacher_name, t.country AS teacher_country

FROM courses c

RIGHT JOIN teachers t ON c.teacher_id = t.id;

 

4. 交叉连接 CROSS JOIN

---

4.1 什么是交叉连接

与内连接和外连接相比，交叉连接非常简单，因为它不存在 ON 子句，那怎么理解交叉连接呢？

交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。即将两个表的数据一一对应，其查询结果的行数为左表中的行数乘以右表中的行数。

CROSS JOIN（交叉连接）的结果也称作笛卡尔积，我们来简单了解一下什么是笛卡尔积：

笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。

4.2 交叉连接的两种定义方式

交叉连接有两种定义方式，分为隐式连接和显式连接。两种定义方式的查询结果是相同的。

• 隐式交叉连接：不需要使用 CROSS JOIN 关键字，只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可，这种方式基本上可以被任意数据库系统支持。

基本语法如下：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`,`table2`;

假如我们想将课程表和教师表进行隐式的交叉连接，查询该学期所有开课老师和他们开课的课程，我们可以使用下列 SQL 语句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` ,`teachers`;

为了编写的便利和简洁，我们一般会给表取别名，如本题中给教师表 courses 取别名为 c，给教师表teachers 取别名为 t：

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`,`teachers` `t`;

• 显式交叉连接：与隐式交叉连接的区别就是它使用 CROSS JOIN 关键字，用法与 INNER JOIN 相似。

基本语法如下：

SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
CROSS JOIN `table2`;

使用显式交叉连接来解决上文相同的问题，我们可以使用下列 SQL 语句：

SELECT  `courses`.`name` AS `course_name`, `teachers`.`name` AS `teacher_name`
FROM `courses` 
CROSS JOIN `teachers`;

或

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`
CROSS JOIN `teachers` `t`;

 

---