【Mysql】内外连接

1.内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中使用的最多的连接查询

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；
1

---

案例:显示员工SMITH的名字和部门名称

SMITH的名字在员工表, 部门名称在部门表当中, 这里是多表查询

粗暴写法:将员工表和部门表做笛卡尔积,然后过滤非法数据,然后再进行条件筛选,显示我们想要的列

聪明做法：在员工表当中把SMITH员工的名字和部门号查询出来,当成一张新表 ,然后和部门表做笛卡尔积, 然后根据 部门号要相同进行过滤非法数据 ,之后得到的就是SMITH员工在部门表的数据

---

做法2：使用内连接, 把员工表和部门表做内连接

这3种写法都可以,但是它们还是有一些区别：

1. 最下面的写法是: 先生成两张表的笛卡尔积,然后再根据where后面的条件进行过滤.
2. inner join ... on 条件则是根据on后面的条件emp.deptno=dept.deptno and ename='SMITH',在生成笛卡尔积时就进行过滤,不满足这些条件的元组不会生成笛卡尔积.
3. 第一种写法 和第二种写法 它们的意义也不同,使用on的时候：是根据两个条件再生成笛卡尔积的时候就进行过滤非法数据, 改成where以后, inner join ... on 条件根据的条件则是先根据on后面的条件, emp.deptno=dept.deptno进行过滤生成笛卡尔积之后,然后再筛选where ename='SMITH'.

---

在使用内连接过滤笛卡尔集中非法数据的时候,我们是直接抛弃的,如果是左外连接,就不能直接抛弃,需要将右半部分置空并保留下来,反之右外连接

2.外连接

外连接就是为了显示出匹配不上的数据,从而确定哪些数据不满足条件

测试表:

--建两张表
create table stu(id int,name varchar(30));--学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam(id int,grade int);--成绩表
insert into exam values(1,56),(2,76),(11,8);
1
2
3
4
5

通过外键id来关联这两个表,因为没有加外键约束,所以exam表中存在一个id为11的学生,这个学生是非法的

如果单纯的进行内连接+条件过滤: 只有左表和右表匹配的信息,满足过滤条件才会显示出来

---

(1)左外连接

如果联合查询,左侧的表完全显示我们就说是左外连接,拿着左表的信息去右表筛选,满足的都要显示出来

左外连接和内连接几乎没有区别,唯一的区别就是,拿着左表的数据到右表进行拼接,如果匹配不上不会过滤右表数据而是置空显示

• 当左边表和右边表没有匹配时,也会显示左边表的数据,右边表的数据显示为NULL

select 字段名 from 表名1 left join 表名2 on 连接条件;
1

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

需求:找到非法存在的学生

• 左外连接以左表为主, 有成绩,但是右表当中学生id为空的就是非法的学生

err写法:

注意:不能使用and,如果使用and的话, exam.id = stu.id and stu.id is null,这样在生成笛卡尔积时就进行过滤,不满足这些条件的元组不会生成笛卡尔积 ,然而stu表当中并没有id为空的,所以不会有结果,右表都是空信息

正确写法:

使用where此时是先根据的条件则是先根据id相同进行过滤, 然后再根据stu表当中id为空进行筛选.

(2)右外连接

如果联合查询,右侧的表完全显示我们就说是右外连接,

select 字段 from 表名1 right join 表名2 on 连接条件；
1

案例: 对stu表和exam表联合查询,把所有的成绩都显示出来,即使这个成绩没有学生与它对应,也要显示出来

• 拿着学生表在成绩表里面找, 找到了就拼接,没有找到就显示为NULL

需求：如果想要找到没有参加考试的同学 -> 即在上面的查找数据当中找到成绩表当中没有成绩的同学

---

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

要列出没有员工的部门 -> 以部门表为主表,在员工表里面找, 如果有员工就拼接,没有找到员工,那么这个部门保留,员工为NULL

• 使用右连接时,当左边表和右边表没有匹配时,也会显示右边表的数据：
• 使用左连接时,当左边表和右边表没有匹配时,也会显示左边表的数据：

---

因为以部门表作为主表,所以如果部门表在左边,就写成左外连接, 否则写成右外连接

需求:如果想找到没有员工的部门: 在上面查找出来的数据中,找到员工表当中的员工编号是空的就是了

---

3.小结

查询的时候,有大概率会涉及多张表,往往需要把多张表"合并"成一张表,所有查询的本质都是转化为一张表的查询

• 自连接,from(子查询),内外连接,本质都是回答如何完成多张表的合并工作！！
• 内连接:保留连接条件大家所共有的,左外连接:保留左侧表的全部信息,右外连接:保留右侧表的全部信息,