使用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.共有字段;3.2.3 示例sql语句
SELECT e1.ename '员工姓名',e2.ename '领导姓名'
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr=e2.empno;
/*
查询员工姓名以及领导员工的信息(若该员工没有领导,也应该显示出来的)
*/3.2.4 分析过程
a 别名为e1的表
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
b 别名为e2的表
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
c 拿e1表的第一条数据中的mgr字段的值7698去e2表进行逐行匹配(从第一行开始,没有就继续往下找),在e2表的第13行找到符合条件的empno字段,此时就把e1表的第一行和e2表的第13行拼接成一行,就像下面这样
EMPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
d 拿e1表的第二条数据中的mgr字段的值7902去e2表进行逐行匹配(从第一行开始,没有就继续往下找),在e2表的第6行找到符合条件的empno字段,此时就把e1表的第一行和e2表的第6行拼接成一行,就像下面这样
EMPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
f 后面依次拿e1表的其他行数据依次与e2表的数据去依次匹配,逻辑和c、d操作的逻辑是一样的,这里就不赘述了
h 需要注意的是e1表的第9行数据mgr为null,发现e2表中没有任何一个empno能够与之匹配,那么就会把e2表的拼接部分字段全部以null值填充(入下表所示)
h1 表的结构如下所示
MPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
h2.示例sql语句如下所示
SELECT e1.*,e2.*
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr=e2.empno;
/*
emp的字段是没有加上约束的,若在建表的时候对字段进行非空约束的,如果还是第9行去进行拼接的话,默认是会以
相应字段的默认值去进行填充的
*/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.共有字段;3.3.3 示例sql语句
SELECT e2.ename '员工姓名',e1.ename '领导姓名'
FROM emp e1
RIGHT JOIN emp e2
ON e2.mgr=e1.empno;
/*
查询员工姓名以及领导员工的信息(若该员工没有领导,也应该显示出来的)
*/3.3.4 分析过程
a 别名为e2的表(主表)
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
b 别名为e1的表(副表)
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
c 拿e2表的第一条数据中的mgr字段的值7698去e1表进行逐行匹配(从第一行开始,没有就继续往下找),在e1表的第13行找到符合条件的empno字段,此时就把e2表的第一行和e1表的第13行拼接成一行,就像下面这样
EMPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
d 拿e2表的第二条数据中的mgr字段的值7902去e1表进行逐行匹配(从第一行开始,没有就继续往下找),在e1表的第6行找到符合条件的empno字段,此时就把e2表的第一行和e1表的第6行拼接成一行,就像下面这样
EMPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
f 后面依次拿e2表的其他行数据依次与e1表的数据去依次匹配,逻辑和c、d操作的逻辑是一样的,这里就不赘述了
h 需要注意的是e2表的第9行数据mgr为null,发现e1表中没有任何一个empno能够与之匹配,那么就会把e1表的拼接部分字段全部以null值填充(入下表所示)
h1 表的结构如下所示
MPNO(e2) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | EMPNO(e1) | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
h2.示例sql语句如下所示
SELECT e2.*,e1.*
FROM emp e1
RIGHT JOIN emp e2
ON e2.mgr=e1.empno;
/*
emp的字段是没有加上约束的,若在建表的时候对字段进行非空约束的,如果还是第9行去进行拼接的话,默认是会以
相应字段的默认值去进行填充的
*/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语句,那么用右外连接同样可以实现
