数据库系统概论——连接

左手的ㄟ右手 2024-04-18 12:36 82阅读 0赞

### 文章目录 ###

*  1. 内连接——交集
 *  2. 交叉连接——并集
 *  3. 外连接
 *  4. 自然连接
 *  5. 全外连接
 *  6. ON 与 WHERE 区别
 *  7. 交叉连接、内连接、自然连接

> JOIN 用于把来自两个或多个表的记录关联起来，实现嵌套查询的效果，主要包括：全外连接、外连接、交叉连接、内连接、自然连接等。

![在这里插入图片描述][20190906170810851.png]

# 1. 内连接——交集 #

INNER JOIN：获取两个表中符合字段匹配关系的记录。

根据比较运算符类型，分为两类：

1.  **等值连接**：使用 = 比较被连接列的列值，列出符合等值关系的所有列，包括重复列；
2.  **不等值连接**：使用 >、>=、<=、<、!>、!< 和 <> 比较被连接列的列值。

内连接的四种写法：

*  **WHERE**：`SELECT * FROM Person p, Address a WHERE p.PersonId = a.PersonId`；
 *  **JOIN**：`SELECT * FROM Person p JOIN Address a ON p.PersonId = a.PersonId`；
 *  **INNER JOIN**：`SELECT * FROM Person p INNER JOIN Address a ON p.PersonId = a.PersonId`；
 *  **STRAIGHT\_JOIN**：`SELECT * FROM Person p STRAIGHT_JOIN Address a ON p.PersonId = a.PersonId`。

# 2. 交叉连接——并集 #

CROSS JOIN：结果是被连接的两个表所有记录的笛卡尔积，记录数为两个表的记录数之积。**CROSS JOIN 不会和 ON 同时出现**。

SELECT * FROM Person p, Address a
    SELECT * FROM Person p JOIN Address a
    SELECT * FROM Person p CROSS JOIN Address a

# 3. 外连接 #

LEFT/RIGHT JOIN：以某一张表为基表，进行字段匹配并连接。结果包含主表的所有记录，不论能否符合匹配条件，对于不能匹配其他表的字段置 null。

SELECT * FROM Person p LEFT/RIGHT JOIN Address a ON p.PersonId = a.PersonId

# 4. 自然连接 #

NATURAL JOIN：特殊的等值连接，要求两个关系中进行比较的列必须是相同的名称，并且在结果中把重复的列去掉。**NATURAL JOIN 不会和 ON 同时出现**。

SELECT * FROM Person p NATURAL JOIN Address a

# 5. 全外连接 #

FULL JOIN：结果中除包括满足连接条件的记录外，还包括各表中所有不满足连接条件的行。

SELECT * FROM Person p FULL JOIN Address a

Oracle 支持 FULL JOIN，然而 MySQL 不支持 FULL JOIN，可以使用 union 达到目的。

SELECT * FROM Person p LEFT JOIN Address a ON p.PersonId = a.AddressId
    UNION
    SELECT * FROM Person p RIGHT JOIN Address a ON p.PersonId = a.AddressId

# 6. ON 与 WHERE 区别 #

SQL 执行顺序：FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT。

1.  `FROM` 将各表从数据库文件加载到内存；
2.  `JOIN` 对各表做乘法运算（求笛卡尔积）；
3.  `ON` 从**笛卡尔积**中筛选符合**表连接条件**的记录，生成临时表；
4.  `WHERE` 从**临时表**中筛选符合**数据过滤条件**的记录；
5.  `GROUP BY` 对筛选结果进行分组；
6.  `HAVING` 从**分组**中筛选符合**分组过滤条件**的分组；
7.  `SELECT` 选取特定的字段，生成**查询结果**；
8.  `ORDER BY` 对查询结果进行排序；
9.  `LIMIT` 返回部分或全部插叙那结果。

ON 在 JOIN 之前执行，也就是说两张表数据行之间进行匹配时，会先判断是否符合 ON 后面的条件，再生成临时表；WHERE 是生成临时表后，对数据的进一步过滤。

内连接可以使用 WHERE 代替 ON。但是，WHERE 没有 ON 执行效率高。当两张表的数据量比较大，应该使用 JOIN，避免使用 WHERE，以减少临时表的规模。

外连接需要严格区分 WHERE 和 ON 的使用：

1.  ON 是在生成临时表时使用的条件，不管 ON 中的条件是否为真，都会返回主表中的记录。
2.  WHERE 是在临时表生成好后，再对临时表进行过滤的条件。条件不为真的就全部过滤掉。

# 7. 交叉连接、内连接、自然连接 #

\*\*交叉连接（笛卡尔积）\*\*是对两个关系 R 和 S 进行组合，结果中元组个数为两个关系中元组个数之积。

\*\*内连接（等值/不等值连接）\*\*是在笛卡尔积的结果上再进行选择操作，筛选关系第 i 个分量与第 j 个分量值相等的元组。

**自然连接**是在等值连接的基础上再进行投影操作，去掉右表中的公共属性列，当两个关系没有公共属性时，自然连接转化成笛卡尔积。

综上所述：

1.  自然连接一定是等值连接，但等值连接不一定是自然连接。
2.  等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。
3.  等值连接不把重复的属性除去；而自然连接要把重复的属性除去。

[20190906170810851.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/43cb0e1806bb4b28a050ed08909ee2c2.png