【MySQL】内外连接——内连接、外连接、左外连接、右外连接、内外连接的区别、左外连接和右外连接的区别

文章目录

MySQL

MySQL表的内连接和外连接

MySQL 中的内连接（INNER JOIN）和外连接（包括左外连接 LEFT JOIN 和右外连接 RIGHT JOIN）是 SQL 查询中用于从两个或多个表中获取数据的重要工具。这些连接操作基于两个表之间的某些相关列之间的关系。下面，我将详细介绍这些连接的概念和用法。

1. 内连接

内连接（INNER JOIN）：内连接返回两个表中满足连接条件的所有行。如果某行在其中一个表中没有匹配的行，那么该行就不会出现在结果集中。内连接是最常见的连接类型，通常用于获取两个表中具有相关数据的行。

SELECTcolumnsFROM table1  
INNERJOIN table2  
ON table1.column_name = table2.column_name;

简单示例：我们创建一张表用于员工信息的存储：

  其中包括员工的编号 id ，员工的姓名和工作，员工的薪资和入职日期，以及员工的部门编号和所对应的领导编号。其中编号 empno 作为我们的主键。

mysql>createtable emp(-> empno intunsignedprimarykey,-> ename varchar(20),-> job varchar(20),-> salary int,-> hiredate date,-> deptno int,->);

  忘了添加或者后续想要添加关于员工其他的列信息?我们可以使用 alter 进行对应表的修改以此来添加我们所需要的列信息。

mysql>altertable emp
    ->addcolumn mgr int;

查看表的结构：

desc emp;

插入信息：

mysql>insertinto emp values(1001,'张三','总经理',10000,'2000-1-1',10,NULL);
mysql>insertinto emp values(1002,'张花','员工',5000,'2005-7-4',10,1001);
mysql>insertinto emp values(1003,'李四','经理',8000,'2002-3-12',20,NULL);
mysql>insertinto emp values(1004,'李华','销售',8000,'2006-3-12',10,1001);
mysql>insertinto emp values(1005,'王五','销售',7800,'2007-11-28',20,1002);
mysql>insertinto emp values(1006,'赵六','销售',6700,'2008-5-18',10,1001);
mysql>insertinto emp values(1007,'小美','员工',5000,'2010-2-08',20,1003);
mysql>insertinto emp values(1008,'小帅','经理',9000,'2002-3-12',30,NULL);
mysql>insertinto emp values(1009,'小蓝','员工',7600,'2007-5-22',30,NULL);

查看表的所有信息：

mysql>select*from emp;

显示张花的名字和部门名称：

  在之前我们通常使用笛卡尔积进行两张表的连接，使用 select+where 查询 就是属于内连接的一种方法。

select ename,dname 
from emp,dept 
where emp.deptno=dept.deptno and ename='张花';

  使用这个内连接就是相当于换另一种写法，结果还是和 使用 select+where 查询后的结果 效果是一样的。

select ename,dname 
from emp
innerjoin dept 
on emp.deptno=dept.deptno and ename='张花';

2. 外连接

  外连接：外连接返回左表（对于左外连接）或右表（对于右外连接）中的所有行，以及右表（对于左外连接）或左表（对于右外连接）中满足连接条件的行。（如果某行在其中一个表中没有匹配的行，那么结果集中该行的对应列将包含 NULL 值）。

2.1 左外连接

**  左外连接（LEFT JOIN 或 LEFT OUTER JOIN）：左外连接返回左表中的所有行，以及右表中满足连接条件的行**（如果左表中的某行在右表中没有匹配的行，那么结果集中该行的右表列将包含 NULL 值）。

SELECTcolumnsFROM table1  
LEFTJOIN table2  
ON table1.column_name = table2.column_name;

使用左外连接查询所有的信息：

select*from emp 
leftjoin dept 
on emp.deptno=dept.deptno;

2.2 右外连接

**  右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：右外连接返回右表中的所有行，以及左表中满足连接条件的行**（如果右表中的某行在左表中没有匹配的行，那么结果集中该行的左表列将包含 NULL 值）。

SELECTcolumnsFROM table1  
RIGHTJOIN table2  
ON table1.column_name = table2.column_name;

使用右外连接查询所有的信息：

select*from emp 
rightjoin dept 
on emp.deptno=dept.deptno;

  结合内连接查询来看：

  三种查询的结果是一致的，就是在顺序上有一些区别，内连接的查询结果和右连接查询结果是完全一样的因为它们都是以empno编号先为顺序进行连接，而左外连接是先以deptno的编号先为顺序进行连接。我们根据不同的需要选择我们合适的连接方式。

**  注意一下：外连接的连接方式不可以省略，内连接可以。**

  当使用LEFT JOIN（或任何JOIN类型）时，你必须指定连接条件。你的查询没有提供ON子句来指定emp表和dept表如何关联。因此，MySQL不知道如何在两个表之间建立连接。查询应该包含一个ON子句，指明连接条件。

3. 内外连接的区别

**  内连接和外连接之间的主要区别：**

  （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）用途

  内连接：通常用于查询两个或多个表中具有相关数据的行，例如查询订单和订单详情。

  外连接：用于查询一个表中的所有记录，以及与之相关的另一个表中的记录，即使某些记录在另一个表中没有匹配项。这在需要显示完整列表，包括没有相关数据的项时非常有用，例如在报告或数据分析中。

4. 左外连接和右外连接的区别

  左外连接和右外连接的主要区别：

**  左外连接：返回左表中的所有记录和右表中匹配的记录。** 如果右表中没有与左表匹配的记录，则结果集中对应的右表字段将为NULL。这种连接类型特别适用于你想要获取左表中的所有记录，并查看哪些右表的记录与之匹配的场景。

**  而右外连接：返回右表中的所有记录和左表中匹配的记录。** 如果左表中没有与右表匹配的记录，则结果集中对应的左表字段将为NULL。这种连接类型则适用于你想要获取右表中的所有记录，并查看哪些左表的记录与之匹配的情况。