21 SQL Join1

优质

小牛编辑

129浏览

2023-12-01

1. 定义

维基百科：SQL 中的连接（Join）语句用于将数据库中的两个或多个表组合起来，由连接生成的数据集合，可以被保存为表，也可以被当成表来使用。

慕课解释：连接操作是 SQL 操作的重中之中，是关系数据库中体现关系的核心指令；连接操常用于合并拥有关联关系的两表或者多表，并从中获取数据。

2. 前言

前面的小节中，我们谈到了外键是体现数据关系中的核心点，那么定义好的外键如何被使用了？

连接操作是使用外键最主要的方式，通过连接可以将两个或多个拥有外键关联的数据表的数据进行合并，然后选择需要的数据字段。

SQL 有五种连接方式：内连接（Inner），全外连接（Full Outer），左外连接（Left Outer），右外连接（Right Outer）和交叉连接（Cross）。

本小节，我们将学习五种连接中比较基础的交叉连接和内连接。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_class;
CREATE TABLE imooc_class
(
  id int PRIMARY KEY,
  class_name varchar(20)
);
INSERT INTO imooc_class(id,class_name) VALUES(1,'SQL必知必会'), (2,'C语言入门'),
(3,'JAVA高效编程'),(4,'JVM花落知多少');

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  class_id int references imooc_class(id)
);
INSERT INTO imooc_user(id,username,class_id) VALUES(1,'pedro', 1), (2,'peter', 1),
(3,'faker', 2), (4,'lucy', 4),(5,'jery', NULL);

说明： 我们分别新建了 imooc_class 表——课程表，和 imooc_user 表——用户表；其中 imooc_user 表中的 class_id 作为外键指向 imooc_class 的主键 id；若 class_id 为 NULL 则表示该用户暂时还未加入任何课程，否则 class_id 表示用户参加课程的 id 。

注意： 为了保证 SQL 可以在每个数据库中执行，所以没有使用 AUTO_INCREMENT 等约束，但真实业务场景下请添加上。

3. SQL Cross Join

交叉连接（Cross Join）,又称笛卡尔连接（Cartesian Join），其作用是返回两表的笛卡尔积。

交叉连接可用于任意两表进行连接，即使两表之间不存在关联关系。

3.1 例1 笛卡尔积

请书写 SQL 语句得到imooc_class和imooc_user两表的笛卡尔积。

分析：

使用 Select 搭配 Cross Join 得到两表的笛卡尔积即可。

语句：

SELECT * FROM imooc_class CROSS JOIN imooc_user;

+----+---------------+----+----------+----------+
| id | class_name    | id | username | class_id |
+----+---------------+----+----------+----------+
| 1  | SQL必知必会    | 1  | pedro    | 1        |
| 2  | C语言入门      | 1  | pedro    | 1        |
| 3  | JAVA高效编程   | 1  | pedro    | 1        |
| 4  | JVM花落知多少  | 1  | pedro    | 1        |
| 1  | SQL必知必会    | 2  | peter    | 1        |
| 2  | C语言入门      | 2  | peter    | 1        |
| 3  | JAVA高效编程   | 2  | peter    | 1        |
| 4  | JVM花落知多少  | 2  | peter    | 1        |
| 1  | SQL必知必会    | 3  | faker    | 2        |
| 2  | C语言入门      | 3  | faker    | 2        |
| 3  | JAVA高效编程   | 3  | faker    | 2        |
| 4  | JVM花落知多少  | 3  | faker    | 2        |
| 1  | SQL必知必会    | 4  | lucy     | 4        |
| 2  | C语言入门      | 4  | lucy     | 4        |
| 3  | JAVA高效编程   | 4  | lucy     | 4        |
| 4  | JVM花落知多少  | 4  | lucy     | 4        |
| 1  | SQL必知必会    | 5  | jery     | <null>   |
| 2  | C语言入门      | 5  | jery     | <null>   |
| 3  | JAVA高效编程   | 5  | jery     | <null>   |
| 4  | JVM花落知多少  | 5  | jery     | <null>   |
+----+---------------+----+----------+----------+

从结果中可以看出，交叉连接就是将一张表的每一条记录与另一张表的每一条记录进行连接成为一条新记录，排列组合完毕后得到两张表的笛卡尔积。

交叉连接还可以通过隐式的连接方式来实现：

SELECT * FROM imooc_class,imooc_user;

4. SQL Inner Join

内连接（Inner Join），是将一张表的每一条记录与另一张表的每一行记录进行比较，得到两张表匹配的记录集合。

维恩图表示如下：
图片描述
图中深色部分便是最后的返回结果。

4.1 例2 内连接

请书写 SQL 语句，返回imooc_class和imooc_user两表的内连接集合。

分析：

使用 Select 搭配 Inner Join 即可。

语句

SELECT * FROM imooc_user INNER JOIN imooc_class ON imooc_user.class_id = imooc_class.id;

结果如下：

+----+----------+----------+----+---------------+
| id | username | class_id | id | class_name    |
+----+----------+----------+----+---------------+
| 1  | pedro    | 1        | 1  | SQL必知必会   |
| 2  | peter    | 1        | 1  | SQL必知必会   |
| 3  | faker    | 2        | 2  | C语言入门     |
| 4  | lucy     | 4        | 4  | JVM花落知多少 |
+----+----------+----------+----+---------------+

一般情况下，交叉连接是默认的连接方式，因此我们可以省略INNER关键字：

SELECT * FROM imooc_user JOIN imooc_class ON imooc_user.class_id = imooc_class.id;

这样也能得到同样的结果；使用 Join 和 On 关键字可以显式连接两表，我们也可以通过 Where 进行隐式的连接：

SELECT * FROM imooc_user, imooc_class WHERE imooc_user.class_id = imooc_class.id;

5. 小结

交叉连接和内连接真实的使用场景其实较少，不过使用也比较简单，多加操练记住即可。
本小节的例子中，imooc_user 与 imooc_class 的外键方式其实是不推荐的，真实业务中的外键方式将在后面的实战部分介绍。