【MySQL】内外连接

表的连接分为内连和外连。

一、内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选。只不过为了让sql的可读性更好，我们使用其他的关键字进行内连接。

语法：

SELECT ... FROM t1 INNER JOIN t2 ON 连接条件 [INNER JOIN t3 ON 连接条件] ... AND 其他条件;

说明：

• SQL中大写的表示关键字，[ ]中代表的是可选项。

---

下面是一些练习的数据表

雇员信息表数据库文件

• 三张表单

• 员工表（emp）
  雇员编号（empno）， 雇员姓名（ename），雇员职位（job），雇员领导编号（mgr），雇佣时间（hiredate）。
  工资月薪（sal），奖金（comm），部门编号（deptno）。

• 部门表（dept）

部门编号（deptno），部门名称（dname），部门所在地点（loc）。

• 工资等级表（salgrade）

等级（grade），此等级最低工资（losal），此等级最高工资（hisal）。

---

1.1 显示SMITH的名字和部门名称

• 很明显，员工名称和部门名称分别在两张表中，emp，dept。
• 我们想要同时显示员工名称和部门名称就需要我们对表进行内连接emp inner join dept
• 由于内连接是按照笛卡尔积的方式进行连接的，连接后有一些数据并不是真实有效的，所以我们还要对数据进行筛选on emp.deptno=dept.deptno。
• 最后我们只要员工SMITH的相关信息，所以我们可以使用where条件进行筛选。

select ename, dname from emp inner join dept on emp.deptno=dept.deptno where ename='SMITH';

可以看出这种写法我们将笛卡儿积的过滤条件单独挑出，这让我们的where子句的筛选条件更加清晰。

二、外连接

外连接分为左外连接和右外连接。

1、左外连接

如果进行联合查询，要求要保证左侧的表要完全显示，我们就说是左外连接。

SELECT ... FROM t1 LEFT JOIN t2 ON 连接条件 [LEFT JOIN t3 ON 连接条件] ... AND 其他条件;

---

给出一张学生表，学生表中的name代表的是学生的姓名，id代表的是学生的学号。如下：

再给出一张成绩表，其中的id代表的是考试学生的学号，grade代表的是学生的成绩。如下：

注意观察这两张表：

• 学生表中的3,4号学生在成绩表中是没有成绩的（可能是缺考了）

• 成绩表中的11号学生，在学生表中是不存在的，（可能学生表统计漏了）

现在我们如果使用内连接进行连接，显示学号很姓名的，我们发现有一些信息会因为不存在，而被筛选掉。

select * from stu inner join exam on stu.id=exam.id;

---

1.1 查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

可以看出题目要求学生表的信息要被完全展示，而成绩表中的信息如果实在不满足连接条件可以被忽略。

所以我们可以使用左外连接保证左边的表要被完全显示：

select * from stu left join exam on stu.id=exam.id;

观察表格：

• 学生表中的信息被完全被展示出来了！就算在成绩表中找不到id与之匹配。同时因为找不到所以其对应的列全部以NULL进行填充

• 而成绩表中的11号id，因为无法匹配而被筛选掉了。

2、右外连接

如果联合查询，右侧的表要求要被完全显示，我们就说是右外连接。

语法：

SELECT ... FROM t1 RIGHT JOIN t2 ON 连接条件 [RIGHT JOIN t3 ON 连接条件] ... AND 其他条件;

2.1 查询所有的成绩，就算这个成绩没有学生与它对应，也要将成绩信息显示出来

可以看出题目要求成绩表的信息要被完全展示，而学生表中的信息如果实在不满足连接条件可以被忽略。

所以我们可以使用右连接保证右边的表要被完全显示，(当然也可以调换表的顺序然后使用左外连接)：

select * from stu right join exam on stu.id=exam.id;

观察表格：

• 成绩表中的信息被完全被展示出来了！就算在学生表中找不到id与之匹配。同时因为找不到所以其对应的列全部以NULL进行填充

• 而学生表中的3，4号id，因为无法匹配而被筛选掉了。

案例：列出部门名称和这些部门的员工信息，同时列出没有员工的部门

• 部门名称和的员工信息显然来自于不同的表中，所以我们需要连接两张表。
• 同时列出没有员工的部门，意味着我们要以部门为主。所以我们可以使用外连接。
• 连接条件就是部门的部门号要等于员工所在的部门号相等，即dept.deptno=emp.deptno。

select dept.dname, emp.ename from dept left join emp on dept.deptno=emp.deptno;