从多个表中返回丢失的数据，从sql语句出发

绝地灬酷狼 2022-06-08 13:12 54阅读 0赞

从多个表中返回丢失的数据  
1 同时返回多个表中丢失的数据，例如：从表DEPT中返回EMP表中不存在的部门号记录。  
语句：SELECT \* FROM DEPT D WHERE NOT EXISTS(SELECT 1 FROM EMP E WHERE D.DEPTNO=E.DEPTNO);结果如图1

![Center][]  
其中EMP表存在10、20、30部门，却不存在部门40，所以该语句是有效的。  
2 以上是使用EXISTS来查找相关的结果，若是使用外连接呢？  
语句：SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO=E.DEPTNO;其结果如图2

![Center 1][]  
其中第15行记录便是要输出的结果  
问题：若是一个员工没有部门号，该如何输出图3的结果呢？

![Center 2][]  
1' 我们先来创建一个表，其中表结构与EMP表一致，在此基础之上添加一行数据，如下：  
语句：CREATE TABLE EMP1 AS SELECT \* FROM EMP;  
插入一条数据，语句：INSERT INTO EMP1 SELECT 1111,'YODA','JEDI',NULL,HIREDATE,SAL,COMM,NULL FROM EMP WHERE ENAME='KING';结果如图4

![Center 3][]  
2' 我们需要输出的结果是如图3的情况，同样将上述的连接拿来使用：  
语句：SELECT D.DNAME,E.DEPTNO,E.ENAME FROM EMP1 E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;  
问题：如果需要同时显示图1中以及图2中第15行的结果呢？如图5

![Center 4][]  
分析：结合以上连接的内容，很自然我们可疑设想先将其中一个结果展示出来，如图2，然后再连接表，达到最后的目的：  
步骤1：语句：SELECT D.DNAME AS DNAME,D.DEPTNO AS DEPTNO,E.ENAME AS ENAME,E.EMPNO AS EMPNO FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO;其结果如图6

![Center 5][]  
步骤2：在步骤1的基础上，以此作为基础临时表和EMP1表进行关联，得到输出结果  
语句：SELECT E2.DEPTNO, E2.DNAME, E3.ENAME FROM EMP1 E3  
FULL OUTER JOIN (SELECT D.DNAME AS DNAME,  
D.DEPTNO AS DEPTNO,  
E.ENAME AS ENAME,  
E.EMPNO AS EMPNO  
FROM DEPT D  
LEFT JOIN EMP E  
ON D.DEPTNO = E.DEPTNO) E2  
ON E2.EMPNO = E3.EMPNO;  
其中在此处将EMPNO作为外层关联条件，避免重复数据产生  
其他的解决办法1：使用基于公共部分的完全外连接来返回丢失的数据  
语句：SELECT E.ENAME,D.DNAME,D.DEPTNO FROM EMP1 E FULL OUTER JOIN DEPT D ON D.DEPTNO=E.DEPTNO;  
这种方法较之上述更加简单明了，区别之一在于临时表的与否，实际上此处才是FULL OUTER JOIN 的正确使用；  
其他的解决办法2：既然上述所使用的方法是先做出一个结果，再达到最后的目的，何不使用UNION语句直 接做并集呢？  
语句：SELECT E.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT JOIN EMP E ON E.DEPTNO=D.DEPTNO

UNION  
SELECT E.DEPTNO,D.DNAME,E.ENAME FROM EMP1 E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;  
同样也能达到效果;

[Center]: /images/20220608/37c19c1632764db583aecb77114b2dc6.png
[Center 1]: /images/20220608/1d4f9a48fec04d30be310a5ffe8df07e.png
[Center 2]: /images/20220608/16cb351f4d21402b86f20f4618f20d9b.png
[Center 3]: /images/20220608/04391021b1de4008beb1f867d8b5cacc.png
[Center 4]: /images/20220608/26c645d2591641f2bbd06e383b15d455.png
[Center 5]: /images/20220608/b0c9bb986b6742b4b279845effb1978d.png