使用mysql语句进行外连接

1 由来

之前我们提到过内连接,它虽然可以进行多表联查,但是在连接条件里面遇到匹配不上的条件,就会该条数据给丢失掉,但是有的时候我们又不想要一个表的数据有丢失,就需要使用外连接技术

2 作用

当某个表为主表,该表的一条数据都不会丢失,就算该表没有匹配到数据也没有关系,保证主表的数据一条都不能丢失,如果匹配不到,该行数据的副表的对应的字段会全部补充null值(字段没有设置非空约束的情况下)

3 如何用

3.1 前提

展示数据的长度=副表有效长度(副表中能与主表产生联系的行数)+主表中未匹配成功的数据的条数(这个结果有可能为0)

3.2 左外连接

3.2.1 本质

把LEFT JOIN左边的这个表当成主表,把LEFT JOIN 右边的这个表当成副表

要求主表的一条数据都不能丢失,然后副表的每条数据都会于主表去进行匹配得到合适的结果

注意:并不是主表有多少条数据行,就只会匹配到多少行数据

3.2.2 语法

SELECT 别名1.字段1...别名1 字段n,别名2.字段1...别名2 字段n
FROM 主表 别名1
LEFT JOIN
副表 别名2
ON 别名1.共有字段=别名2.共有字段;
1
2
3
4
5

3.2.3 示例sql语句

SELECT e1.ename '员工姓名',e2.ename '领导姓名'
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr=e2.empno;
/*
查询员工姓名以及领导员工的信息(若该员工没有领导,也应该显示出来的)
*/
1
2
3
4
5
6
7

3.2.4 分析过程

a 别名为e1的表

b 别名为e2的表

c 拿e1表的第一条数据中的mgr字段的值7698去e2表进行逐行匹配(从第一行开始,没有就继续往下找),在e2表的第13行找到符合条件的empno字段,此时就把e1表的第一行和e2表的第13行拼接成一行,就像下面这样

d 拿e1表的第二条数据中的mgr字段的值7902去e2表进行逐行匹配(从第一行开始,没有就继续往下找),在e2表的第6行找到符合条件的empno字段,此时就把e1表的第一行和e2表的第6行拼接成一行,就像下面这样

f 后面依次拿e1表的其他行数据依次与e2表的数据去依次匹配,逻辑和c、d操作的逻辑是一样的,这里就不赘述了

h 需要注意的是e1表的第9行数据mgr为null,发现e2表中没有任何一个empno能够与之匹配,那么就会把e2表的拼接部分字段全部以null值填充(入下表所示)

h1 表的结构如下所示

h2.示例sql语句如下所示

SELECT e1.*,e2.*
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr=e2.empno;
/*
emp的字段是没有加上约束的,若在建表的时候对字段进行非空约束的,如果还是第9行去进行拼接的话,默认是会以
相应字段的默认值去进行填充的
*/
1
2
3
4
5
6
7
8

h.3 示例sql语句运行截图如下所示

3.2.5 示例sql语句执行过程

3.3 右外连接

3.3.1 本质

把RIGHT JOIN右边的这个表当成主表,把RIGHT JOIN 左边的这个表当成副表

要求主表的一条数据都不能丢失,然后副表的每条数据都会于主表去进行匹配得到合适的结果

注意:并不是主表有多少条数据行,就只会匹配到多少行数据

3.3.2 语法

SELECT 别名1.字段1...别名1 字段n,别名2.字段1...别名2 字段n
FROM 副表 别名1
RIGHT JOIN 主表 别名2
ON 别名1.共有字段=别名2.共有字段;
1
2
3
4

3.3.3 示例sql语句

SELECT e2.ename '员工姓名',e1.ename '领导姓名'
FROM emp e1
RIGHT JOIN emp e2
ON e2.mgr=e1.empno;
/*
查询员工姓名以及领导员工的信息(若该员工没有领导,也应该显示出来的)
*/
1
2
3
4
5
6
7

3.3.4 分析过程

a 别名为e2的表(主表)

b 别名为e1的表(副表)

c 拿e2表的第一条数据中的mgr字段的值7698去e1表进行逐行匹配(从第一行开始,没有就继续往下找),在e1表的第13行找到符合条件的empno字段,此时就把e2表的第一行和e1表的第13行拼接成一行,就像下面这样

d 拿e2表的第二条数据中的mgr字段的值7902去e1表进行逐行匹配(从第一行开始,没有就继续往下找),在e1表的第6行找到符合条件的empno字段,此时就把e2表的第一行和e1表的第6行拼接成一行,就像下面这样

f 后面依次拿e2表的其他行数据依次与e1表的数据去依次匹配,逻辑和c、d操作的逻辑是一样的,这里就不赘述了

h 需要注意的是e2表的第9行数据mgr为null,发现e1表中没有任何一个empno能够与之匹配,那么就会把e1表的拼接部分字段全部以null值填充(入下表所示)

h1 表的结构如下所示

h2.示例sql语句如下所示

SELECT e2.*,e1.*
FROM emp e1
RIGHT JOIN emp e2
ON e2.mgr=e1.empno;
/*
emp的字段是没有加上约束的,若在建表的时候对字段进行非空约束的,如果还是第9行去进行拼接的话,默认是会以
相应字段的默认值去进行填充的
*/
1
2
3
4
5
6
7
8

h.3 示例sql语句运行截图如下所示

3.2.5 示例sql语句执行过程

4 注意点

4.1 判断是内连接还是外连接的依据

FROM 子句中有LEFT JOIN或RIGHT JOIN,算外连接,

FROM子句中若只有JOIN或者是INNER JOIN,那就代表这是内连接

虽然内连接的INNER关键词可以省略,但是不建议省略,因为阅读性不太好

4.2 内连接和外连接的本质区别什么呢?

本质区别: 需不需要保存一个表的完整数据不丢失

若想要不丢失,就用外连接,若丢不丢失没有影响的话,是可以使用内连接的

4.3 在使用外连接时,我们是选择左外连接好呢还是选择右外连接好呢？

萝卜白菜,各有所爱。选择最适合自己的方式就行。

因为但凡能用左外连接实现的sql语句,那么用右外连接同样可以实现