SQL 左连接、右链接、内连接

目录

一、左连接的定义：

二、右连接的定义：

三、内链接：

四、全连接

五、嵌套循环连接

六、排序合并连接

七、哈希连接

MySQL对JOIN的支持

---

一、左连接的定义：

   左连接（LEFT JOIN）是指将左边的表的所有行与右边的表进行连接，并返回匹配的结果。如果右边的表中没有与左边表匹配的行，则返回 NULL 值。左连接常用于获取左表中的所有数据以及与之相关的右表数据。

   是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

  下边以A表和B表为例子，

A、B之间的左连接条件为：ano=bno;

查询语句为：SELECT * FROM `A` LEFT JOIN B ON ano=bno;

eg1：A表中的只有1条记录，B表中2条记录，B表的2条记录bno都等于ano,

A表：

B表：

查询语句：

查询结果：将A表的记录都查询出来，B表中bno等于ano的都查询出来了且左侧为ano对应的信息。

eg2:A表中存在的ano，B表中不存在对应的bno;

A表：

B表：

查询语句：

结果：A表的记录全部查询出来而且如果B没有bno=ano的记录时右侧显示为空，B表中只有bno=ano的记录查询出来了。

二、右连接的定义：

  是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

eg:以上边的数据为例子，进行右连接测试如下：

结果：a表只显示和b表id相等的2行数据，b表的记录全部显示出来

三、内链接：

  内连接（INNER JOIN）是最基本的 JOIN 类型之一。它会返回两个表中关联字段匹配的行，排除掉不匹配的行。内连接只返回匹配的结果，因此可以过滤掉不相关的数据，提高查询效率。 

使用比较运算符根据每个表共有的列的值匹配两个表中的行；

eg:继续以之前的数据为例子：

结果：只显示a.aid=b.bid的2行记录

四、全连接

  全连接（FULL JOIN）是将两个表中的所有行进行连接，不论是否匹配。如果两个表中的某行在另一个表中没有匹配，那么将使用 NULL 值填充。全连接返回的结果包含了左连接和右连接的所有数据。

五、嵌套循环连接

嵌套循环 JOIN（Nested Loop Join）是一种简单但效率较低的 JOIN 算法。它的原理是对于左边的表中的每一行，都会与右边的表进行比较，并返回匹配的结果。这种算法适用于小规模数据的 JOIN 操作，但对于大规模数据，性能可能会受到影响。

嵌套循环 JOIN 的步骤如下：

1. 对于左边的表，逐行读取每一行。
2. 对于右边的表，逐行扫描，并与左边表的当前行进行比较。
3. 如果连接字段的值匹配，则将两个表的匹配行合并，并返回结果。
4. 继续对右边的表进行扫描，直到找到所有匹配的行。
5. 然后，读取左边表的下一行，重复上述步骤，直到处理完所有行。

嵌套循环 JOIN 算法的时间复杂度为 O(n*m)，其中 n 和 m 分别是左右表的行数。

六、排序合并连接

排序合并 JOIN（Merge Join）是一种更高效的 JOIN 算法。它的原理是先对连接字段进行排序，然后通过扫描两个已排序的表进行匹配。这种算法在处理大规模数据和非等值连接时效果显著。

排序合并 JOIN 的步骤如下：

1. 对连接字段在左右两个表上进行排序。
2. 同时扫描两个表，比较连接字段的值。
3. 如果连接字段的值相等，则将两个表的匹配行合并，并返回结果。
4. 继续扫描，直到找到所有匹配的行。
5. 如果连接字段的值不相等，则根据排序顺序继续扫描。

排序合并 JOIN 利用了排序的优势，减少了扫描次数，提高了 JOIN 的效率。

七、哈希连接

  哈希 JOIN（Hash Join）是一种基于哈希表的 JOIN 算法。它通过将连接字段的值映射到哈希表中的桶中，然后对两个表进行哈希连接。哈希 JOIN 需要更多的内存，但对于大规模数据和非等值连接，它可以提供更好的性能。

哈希 JOIN 的步骤如下：

1. 对于左边的表，将连接字段的值进行哈希计算，并将每个值存储到哈希表的相应桶中。
2. 对于右边的表，逐行扫描并计算连接字段的哈希值。
3. 在哈希表中查找匹配的哈希值，找到对应的桶。
4. 将匹配的行合并，并返回结果。
5. 继续扫描右边的表，直到找到所有匹配的行。

  哈希 JOIN 需要更多的内存来存储哈希表，但对于大规模数据和非等值连接，它可以提供更好的性能。

MySQL对JOIN的支持

相对于其他数据库管理系统，MySQL 在 JOIN 操作中的支持相对较少。它主要采用嵌套循环 JOIN 算法，而不支持哈希连接和排序合并连接。不过，在 MySQL 中有一些变种算法，可以帮助 MySQL 提高 JOIN 的执行效率。