MySQL 中的内连接(INNER JOIN)和外连接(包括左外连接 LEFT JOIN 和右外连接 RIGHT JOIN)是 SQL 查询中用于从两个或多个表中获取数据的重要工具。这些连接操作基于两个表之间的某些相关列之间的关系。下面,我将详细介绍这些连接的概念和用法。
内连接(INNER JOIN):内连接返回两个表中满足连接条件的所有行。如果某行在其中一个表中没有匹配的行,那么该行就不会出现在结果集中。内连接是最常见的连接类型,通常用于获取两个表中具有相关数据的行。
简单示例:我们创建一张表用于员工信息的存储:
其中包括员工的编号 id ,员工的姓名和工作,员工的薪资和入职日期,以及员工的部门编号和所对应的领导编号。其中编号 empno 作为我们的主键。
忘了添加或者后续想要添加关于员工其他的列信息?我们可以使用 alter 进行对应表的修改以此来添加我们所需要的列信息。
查看表的结构:
插入信息:
查看表的所有信息:
显示张花的名字和部门名称:
在之前我们通常使用笛卡尔积进行两张表的连接,使用 select+where 查询 就是属于内连接的一种方法。
使用这个内连接就是相当于换另一种写法,结果还是和 使用 select+where 查询后的结果 效果是一样的。
外连接:外连接返回左表(对于左外连接)或右表(对于右外连接)中的所有行,以及右表(对于左外连接)或左表(对于右外连接)中满足连接条件的行。(如果某行在其中一个表中没有匹配的行,那么结果集中该行的对应列将包含 NULL 值)。
2.1 左外连接
左外连接(LEFT JOIN 或 LEFT OUTER JOIN):左外连接返回左表中的所有行,以及右表中满足连接条件的行(如果左表中的某行在右表中没有匹配的行,那么结果集中该行的右表列将包含 NULL 值)。
使用左外连接查询所有的信息:
2.2 右外连接
右外连接(RIGHT JOIN 或 RIGHT OUTER JOIN):右外连接返回右表中的所有行,以及左表中满足连接条件的行(如果右表中的某行在左表中没有匹配的行,那么结果集中该行的左表列将包含 NULL 值)。
使用右外连接查询所有的信息:
结合内连接查询来看:
三种查询的结果是一致的,就是在顺序上有一些区别,内连接的查询结果和右连接查询结果是完全一样的因为它们都是以empno编号先为顺序进行连接,而左外连接是先以deptno的编号先为顺序进行连接。我们根据不同的需要选择我们合适的连接方式。
注意一下:外连接的连接方式不可以省略,内连接可以。
当使用LEFT JOIN(或任何JOIN类型)时,你必须指定连接条件。你的查询没有提供ON子句来指定emp表和dept表如何关联。因此,MySQL不知道如何在两个表之间建立连接。查询应该包含一个ON子句,指明连接条件。
内连接和外连接之间的主要区别:
(1)返回的行数:
内连接:只返回满足连接条件的行。如果某行在其中一个表中没有匹配的行,那么该行不会出现在结果集中。
外连接:返回至少一个表中的所有行,以及另一个表中满足连接条件的行。对于没有匹配的行,结果集中对应的列将包含 NULL 值。
(2)结果集内容
内连接:结果集仅包含两个表中共有的数据,即两个表中都有匹配的行。
外连接:结果集不仅包含共有的数据,还包含至少一个表中独有的数据(以 NULL 值表示)。
(3)类型
内连接:只有一种类型,即标准的 INNER JOIN。
外连接:有三种类型:LEFT JOIN(或 LEFT OUTER JOIN)、RIGHT JOIN(或 RIGHT OUTER JOIN)和 FULL JOIN(或 FULL OUTER JOIN)。LEFT JOIN 返回左表中的所有行和右表中匹配的行;RIGHT JOIN 返回右表中的所有行和左表中匹配的行;FULL JOIN 返回两个表中的所有行。
(4)用途
内连接:通常用于查询两个或多个表中具有相关数据的行,例如查询订单和订单详情。
外连接:用于查询一个表中的所有记录,以及与之相关的另一个表中的记录,即使某些记录在另一个表中没有匹配项。这在需要显示完整列表,包括没有相关数据的项时非常有用,例如在报告或数据分析中。
左外连接和右外连接的主要区别:
左外连接:返回左表中的所有记录和右表中匹配的记录。 如果右表中没有与左表匹配的记录,则结果集中对应的右表字段将为NULL。这种连接类型特别适用于你想要获取左表中的所有记录,并查看哪些右表的记录与之匹配的场景。
而右外连接:返回右表中的所有记录和左表中匹配的记录。 如果左表中没有与右表匹配的记录,则结果集中对应的左表字段将为NULL。这种连接类型则适用于你想要获取右表中的所有记录,并查看哪些左表的记录与之匹配的情况。