目录
一、左连接的定义:
左连接(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 的步骤如下:
- 对于左边的表,逐行读取每一行。
- 对于右边的表,逐行扫描,并与左边表的当前行进行比较。
- 如果连接字段的值匹配,则将两个表的匹配行合并,并返回结果。
- 继续对右边的表进行扫描,直到找到所有匹配的行。
- 然后,读取左边表的下一行,重复上述步骤,直到处理完所有行。
嵌套循环 JOIN 算法的时间复杂度为 O(n*m),其中 n 和 m 分别是左右表的行数。
六、排序合并连接
排序合并 JOIN(Merge Join)是一种更高效的 JOIN 算法。它的原理是先对连接字段进行排序,然后通过扫描两个已排序的表进行匹配。这种算法在处理大规模数据和非等值连接时效果显著。
排序合并 JOIN 的步骤如下:
- 对连接字段在左右两个表上进行排序。
- 同时扫描两个表,比较连接字段的值。
- 如果连接字段的值相等,则将两个表的匹配行合并,并返回结果。
- 继续扫描,直到找到所有匹配的行。
- 如果连接字段的值不相等,则根据排序顺序继续扫描。
排序合并 JOIN 利用了排序的优势,减少了扫描次数,提高了 JOIN 的效率。
七、哈希连接
哈希 JOIN(Hash Join)是一种基于哈希表的 JOIN 算法。它通过将连接字段的值映射到哈希表中的桶中,然后对两个表进行哈希连接。哈希 JOIN 需要更多的内存,但对于大规模数据和非等值连接,它可以提供更好的性能。
哈希 JOIN 的步骤如下:
- 对于左边的表,将连接字段的值进行哈希计算,并将每个值存储到哈希表的相应桶中。
- 对于右边的表,逐行扫描并计算连接字段的哈希值。
- 在哈希表中查找匹配的哈希值,找到对应的桶。
- 将匹配的行合并,并返回结果。
- 继续扫描右边的表,直到找到所有匹配的行。
哈希 JOIN 需要更多的内存来存储哈希表,但对于大规模数据和非等值连接,它可以提供更好的性能。
MySQL对JOIN的支持
相对于其他数据库管理系统,MySQL 在 JOIN 操作中的支持相对较少。它主要采用嵌套循环 JOIN 算法,而不支持哈希连接和排序合并连接。不过,在 MySQL 中有一些变种算法,可以帮助 MySQL 提高 JOIN 的执行效率。