Oracle PL/SQL 基础教程（5）复杂查询（二）

SQL干货分享 2021-08-13

378

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家早上好，自从开办每周三的 PL/SQL 入门系列教程以来，小刘在后台收到很多小伙伴的热烈反响，大家都对新栏目赞不绝口

在此小刘感谢大家的关注和支持，我一定坚持做好这套教程

在前几节课程中，我们已经了解了单表查询语句一些基本的语法规则，今天我们来探讨一下如何使用 SQL 进行多表联合查询

PL/SQL 基础课程第五课：多表联结查询

在实际的生产应用中，绝大多数情况下，仅查询一张表中存储的数据是远远不够的。我们经常需要将多张表的数据结合在一起才能得到所需的完整信息，这就需要使用联结查询语句来实现

笛卡尔积

在数据库中，我们常将表理解成为无序集合，这是针对表中的记录来讲的，也就是说表中的每一行要作为一个整体充当集合中的一个元素

首先我们从两个集合间最简洁直白的运算——笛卡尔积（直积，Cartesian product）开始，理解表的联结

假设集合 A={a, b}，集合 B={0, 1, 2}，则两个集合的笛卡尔积为 A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

注意，如果我们把这个例子套用到表连接上，A 与 B 各为一张表，而例如 A 中的元素 a 和 b，它们其实是表中的两个行，而非字段或者单元格

笛卡尔积是我们理解表联结运算的理论基础，而在 SQL 中，实现类似笛卡尔积这样运算的，称为“交叉联结”，使用关键字 CROSS JOIN 实现

SELECT d.deptno, d.dname, e.deptno, e.empno, e.ename
  FROM emp e
 CROSS JOIN dept d;

上例中，EMP 表中有 14 行数据，DEPT 表有 4 行数据，故交叉连接后，得出 56 行数据

在本例中，还体现出了表别名的重要作用，尤其在表间出现了同名字段的情况下，表别名更加清楚地表达了语句的逻辑。很多资料中甚至要求编码者：在多表联结时，必须使用表别名

内联结

交叉联结是最直白简洁的表联结方式，但几乎没有多少应用场景，毕竟当我们需要将多张表联结在一起查询时，总归是由于它们之间有某种内在的联系，尤其是在基于面向对象思想的经典 E-R 数据模型中

例如 EMP 表中的 DEPTNO 字段，代表员工所属的部门编号，同样地，在 DEPT 表中，DEPTNO 字段也代表部门编号，所以在两表联结查询时，要求按它们的 DEPTNO 字段相等为联结条件，查询出的结果将更具实际应用意义

如此，能够实现表间按照内在联系进行关联查询的联结规则，称作内联结

另外，上面所说的 DEPT 表，是存储部门信息的表，DEPTNO 字段是用以唯一标记每个部门的，所以可以称 DEPTNO 是 DEPT 表的主键（Primary key）

而 EMP 表是存储员工信息的表，DEPTNO 字段在其中只是标记员工供职的部门的，等于是引用外来的 DEPT 表，所以称 DEPTNO 是 EMP 表的一个外键（Foreign key）

进而我们可以总结出，内联结最主要的应用场景是实现主外键的关联查询

内联结的语法中，INNER JOIN 关键字（INNER可以省略）两边分别放置要进行内联结的表

在 ON 关键字后声明表间的关联条件，习惯上将关联条件整体用括号包裹以使语句结构清晰，尽管语法上并没有要求这样做

如果还有其它对表数据的筛选条件，可在随后的 WHERE 部分写明

SQL> SELECT e.empno, e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e
  3    JOIN dept d
  4      ON (e.deptno = d.deptno);


EMPNO ENAME      DEPTNO DEPTNO DNAME
----- ---------- ------ ------ --------------
 7369 SMITH          20     20 RESEARCH
 7499 ALLEN          30     30 SALES
 7521 WARD           30     30 SALES
 7566 JONES          20     20 RESEARCH
 7654 MARTIN         30     30 SALES
 7698 BLAKE          30     30 SALES
 7782 CLARK          10     10 ACCOUNTING
 7788 SCOTT          20     20 RESEARCH
 7839 KING           10     10 ACCOUNTING
 7844 TURNER         30     30 SALES
 7876 ADAMS          20     20 RESEARCH
 7900 JAMES          30     30 SALES
 7902 FORD           20     20 RESEARCH
 7934 MILLER         10     10 ACCOUNTING


14 rows selected

当然，所谓实体表之间的内在联系，并不一定是类似主外键这样的等同联结，也可能是非等同的联结关系，换句话说就是使用不等号表达的联结关系

非等同联结同样可以使用内联结实现，如我们在上例的基础上，引入工资等级表 SALGRADE

SQL> SELECT * FROM salgrade;


     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999


SQL> SELECT e.empno, e.ename, e.deptno, d.dname, e.sal, g.grade
  2    FROM emp e
  3    JOIN dept d
  4      ON (e.deptno = d.deptno)
  5    JOIN salgrade g
  6      ON (e.sal BETWEEN g.losal AND g.hisal);


EMPNO ENAME      DEPTNO DNAME                SAL      GRADE
----- ---------- ------ -------------- --------- ----------
 7839 KING           10 ACCOUNTING       5000.00          5
 7782 CLARK          10 ACCOUNTING       2450.00          4
 7934 MILLER         10 ACCOUNTING       1300.00          2
 7902 FORD           20 RESEARCH         3000.00          4
 7788 SCOTT          20 RESEARCH         3000.00          4
 7566 JONES          20 RESEARCH         2975.00          4
 7876 ADAMS          20 RESEARCH         1100.00          1
 7369 SMITH          20 RESEARCH          800.00          1
 7698 BLAKE          30 SALES            2850.00          4
 7844 TURNER         30 SALES            1500.00          3
 7499 ALLEN          30 SALES            1600.00          3
 7654 MARTIN         30 SALES            1250.00          2
 7521 WARD           30 SALES            1250.00          2
 7900 JAMES          30 SALES             950.00          1


14 rows selected

另外，在一些实际场景中，同一张表的不同行之间也可能有某种联系，这时，我们也可以在语句中重复多次引用这张表，实现不同行之间的关联

例如在 EMP 表中，MGR 字段代表着员工的经理的工号，这便是这个员工和另一个员工，也就是行与行之间的关系

很多资料都乐于把这种实际上是用某张表和其自身进行关联的查询方式形象地称为“自联结”

SQL> SELECT e.empno, e.ename, e.mgr, m.ename mname
  2    FROM emp e, emp m
  3   WHERE e.mgr = m.empno;


EMPNO ENAME        MGR MNAME
----- ---------- ----- ----------
 7902 FORD        7566 JONES
 7788 SCOTT       7566 JONES
 7900 JAMES       7698 BLAKE
 7844 TURNER      7698 BLAKE
 7654 MARTIN      7698 BLAKE
 7521 WARD        7698 BLAKE
 7499 ALLEN       7698 BLAKE
 7934 MILLER      7782 CLARK
 7876 ADAMS       7788 SCOTT
 7782 CLARK       7839 KING
 7698 BLAKE       7839 KING
 7566 JONES       7839 KING
 7369 SMITH       7902 FORD


13 rows selected

Oracle SQL 语法规则中，将表联结的写法进一步简化，它允许编码者将所要联结的表全部放置在 FROM 部分，并使用逗号相隔，至于表关联条件和数据筛选条件则均写在 WHERE 部分，数据库会自行将不同用处的条件语句区分以进行不同的操作

这样的写法可以想象成为“先将各表进行笛卡尔联结，生成一个临时的虚拟表，再应用WHERE中所有的条件进行筛选加工，得出最终的结果集”以便理解

如上文所说，数据库能够自动识别 WHERE 部分的关联语句和筛选语句，但是为了提高代码的可读性，习惯上我们常将关联条件写在前面，筛选条件写在后面

SQL> SELECT e.empno, e.ename, e.deptno, d.dname, e.sal, g.grade
  2    FROM emp e, dept d, salgrade g
  3   WHERE e.deptno = d.deptno -- join1
  4     AND e.sal BETWEEN g.losal AND g.hisal -- join2
  5     AND e.deptno = 30 -- filter1
  6     AND g.grade = 3 -- filter2
  7  /


EMPNO ENAME      DEPTNO DNAME                SAL      GRADE
----- ---------- ------ -------------- --------- ----------
 7499 ALLEN          30 SALES            1600.00          3
 7844 TURNER         30 SALES            1500.00          3

外联结

内联结可以实现实体表之间的内在关联，但有时根据既定的关联条件，一张表中的记录不一定在另一张表中总有对应的记录

比如 DEPT 表中，有一个编号为 40 的 OPERATIONS 部门，它在 EMP 表中并没有对应的员工。这种情况下，使用内联结查询两表时，总是看不到 40 部门

外联结被设计用于解决这种情形的查询，它不但返回符合联结和查询条件的数据行，还可以返回不符合条件的一些行

这里需要关注的是，“不符合条件”这一字眼，它并不表示例如 DEPT 中 10 这个部门，之于 EMP 表中 FORD 这个员工（其部门为 20），这两行记录不符合 DEPTNO 字段相等的条件，从而可以关联起来被返回

所谓“不符合条件”可以通俗地理解为“无法符合条件”，例如 DEPT 中 10 号部门可以匹配到 EMP 表中多个部门为 10 的员工，但是 40 号部门却无法匹配到 EMP 表中任何一个员工，故“不但返回符合联结条件的数据行，还可以返回不符合条件的一些行”这一说法，表示不仅 10 号部门及其众员工可以被返回，40 号部门这样没有员工的部门，也将在外连接查询中被一视同仁的返回

在 Oracle 中，允许三种外联结形式：左外联结、右外联结和全外联结

左外联结增加返回关键字 LEFT [OUTER] JOIN 以左表中不符合联结条件但符合筛选条件的行
右外联结增加返回关键字 RIGHT [OUTER] JOIN 以左表中不符合联结条件但符合筛选条件的行
全外联结则增加返回关键字 FULL [OUTER] JOIN 两边表中不符合联结条件但符合筛选条件的行

外联结在返回不符合联结条件的行组合时，总有一张表是取不到数据的，这时这张表的各个字段用 NULL 表示

为了演示下面的例子，我们暂时把 EMP 表中的员工 SCOTT 其 DEPTNO 字段设置成 50，这是一个在 DEPT 表中不存在的部门编号

SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e
  3    LEFT JOIN dept d
  4      ON (e.deptno = d.deptno)
  5   WHERE e.deptno IN (10, 50);


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
MILLER         10     10 ACCOUNTING
KING           10     10 ACCOUNTING
CLARK          10     10 ACCOUNTING
SCOTT          50

我们看到虽然 SCOTT 其部门编号 50 在 DEPT 表中没有对应记录，但是由于 EMP 是 LEFT JOIN 左边的表，且 SCOTT 这行数据符合了 WHERE 中对 EMP 表的筛选条件，所以它可以被返回，这一行中引用的 DEPT 表中的字段，都将得到 NULL

SQL> SELECT d.deptno, d.dname, e.empno, e.ename
  2    FROM emp e
  3   RIGHT JOIN dept d
  4      ON (e.deptno = d.deptno)
  5   WHERE d.deptno IN (10, 40);


DEPTNO DNAME          EMPNO ENAME
------ -------------- ----- ----------
    10 ACCOUNTING      7782 CLARK
    10 ACCOUNTING      7839 KING
    10 ACCOUNTING      7934 MILLER
    40 OPERATIONS

我们看到尽管 40 号部门在 EMP 表中并没有对应的员工，但由于 DEPT 是 RIGHT JOIN 右边的表，而且这个部门符合了 WHERE 中对 DEPT 的筛选要求，所以它也可以被返回，同样地，这一行所引用的 EMP 表中的字段都是 NULL

SQL> SELECT d.deptno, d.dname, e.empno, e.ename
  2    FROM emp e
  3    FULL JOIN dept d
  4      ON (e.deptno = d.deptno);


DEPTNO DNAME          EMPNO ENAME
------ -------------- ----- ----------
    20 RESEARCH        7369 SMITH
    30 SALES           7499 ALLEN
    30 SALES           7521 WARD
    20 RESEARCH        7566 JONES
    30 SALES           7654 MARTIN
    30 SALES           7698 BLAKE
    10 ACCOUNTING      7782 CLARK
                       7788 SCOTT
    10 ACCOUNTING      7839 KING
    30 SALES           7844 TURNER
    20 RESEARCH        7876 ADAMS
    30 SALES           7900 JAMES
    20 RESEARCH        7902 FORD
    10 ACCOUNTING      7934 MILLER
    40 OPERATIONS           


15 rows selected

在这个例子返回的结果中，第 8 行展示了部门不能对应到 DEPT 表的 EMP 表中的 SCOTT 的行，第 15 行则展示了编号不能在 EMP 表匹配员工的 DEPT 表中的 40 号部门，这两行能够同时与内联结匹配成功的其它 13 行数据一起被返回，体现了全外联结 FULL JOIN 的作用

值得注意的是，外联结似乎具有一种“传递性”，比如说我们在例二的基础上再引入 SALGRADE 表，虽然这张表不需要和 EMP 表进行外联结，且本身不和 DEPT 表有联系，但是却不能使用 INNER JOIN 联结到语句中

SQL> SELECT d.deptno, d.dname, e.empno, e.ename, e.sal, g.grade
  2    FROM emp e
  3   RIGHT JOIN dept d
  4      ON (e.deptno = d.deptno)
  5    JOIN salgrade g
  6      ON (e.sal BETWEEN g.losal AND g.hisal)
  7   WHERE d.deptno IN (10, 40);


DEPTNO DNAME          EMPNO ENAME            SAL      GRADE
------ -------------- ----- ---------- --------- ----------
    10 ACCOUNTING      7839 KING         5000.00          5
    10 ACCOUNTING      7782 CLARK        2450.00          4
    10 ACCOUNTING      7934 MILLER       1300.00          2

我们观察到，这次结果中并没有返回 40 号部门，这是由于从语义上讲，EMP RIGHT JOIN DEPT 后，等同于形成了一个虚拟表，接下来与 SALGRADE 联结的是这张虚拟表，而非 EMP 表本身，只不过在这张虚拟表中，来源于 EMP 表的字段仍可带有其表别名

在这个虚拟表中，对于 40 号部门那一行，来源于 EMP 表的 SAL 字段是 NULL，所以用 SAL 字段去和 SALGRADE 表作关联的话，仍须使用外联结。现在是虚拟表存在行的 SAL 列无法符合与 SALGRADE 表的关联条件，所以在接入 SALGRADE 表时，应使用左外联结

SQL> SELECT d.deptno, d.dname, e.empno, e.ename, e.sal, g.grade
  2    FROM emp e
  3   RIGHT JOIN dept d
  4      ON (e.deptno = d.deptno)
  5    LEFT JOIN salgrade g
  6      ON (e.sal BETWEEN g.losal AND g.hisal)
  7   WHERE d.deptno IN (10, 40);


DEPTNO DNAME          EMPNO ENAME            SAL      GRADE
------ -------------- ----- ---------- --------- ----------
    40 OPERATIONS                                
    10 ACCOUNTING      7839 KING         5000.00          5
    10 ACCOUNTING      7782 CLARK        2450.00          4
    10 ACCOUNTING      7934 MILLER       1300.00          2

在 Oracle 中，对于左外联结和右外联结同样有一套简化版本的写法（对于全外联结则暂时没有简化写法）

所谓左右，只是表相较于关键字之方位而言，究其本质，无非是要将一张表不能满足联结条件的行也返回到结果集当中，与此同时，另外一张表则如同是无形中增加了若干“空”行，与前面这些行实现了“联结”

Oracle 的简化写法正是基于这个本质，使用组合符号“(+)”在关联条件中标记出哪张表是需要“增加空行”的

SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e, dept d
  3   WHERE e.deptno = d.deptno(+)
  4     AND e.deptno IN (10, 50);


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
MILLER         10     10 ACCOUNTING
KING           10     10 ACCOUNTING
CLARK          10     10 ACCOUNTING
SCOTT          50        


SQL> SELECT d.deptno, d.dname, e.empno, e.ename
  2    FROM emp e, dept d
  3   WHERE e.deptno(+) = d.deptno
  4     AND d.deptno IN (10, 40);


DEPTNO DNAME          EMPNO ENAME
------ -------------- ----- ----------
    10 ACCOUNTING      7782 CLARK
    10 ACCOUNTING      7839 KING
    10 ACCOUNTING      7934 MILLER
    40 OPERATIONS

另外，简化写法的查询语句也需要关注外联结的“传递性”

SQL> SELECT d.deptno, d.dname, e.empno, e.ename, e.sal, g.grade
  2    FROM emp e, dept d, salgrade g
  3   WHERE e.deptno(+) = d.deptno
  4     AND d.deptno IN (10, 40)
  5     AND e.sal BETWEEN g.losal AND g.hisal;


DEPTNO DNAME          EMPNO ENAME            SAL      GRADE
------ -------------- ----- ---------- --------- ----------
    10 ACCOUNTING      7839 KING         5000.00          5
    10 ACCOUNTING      7782 CLARK        2450.00          4
    10 ACCOUNTING      7934 MILLER       1300.00          2


SQL> SELECT d.deptno, d.dname, e.empno, e.ename, e.sal, g.grade
  2    FROM emp e, dept d, salgrade g
  3   WHERE e.deptno(+) = d.deptno
  4     AND d.deptno IN (10, 40)
  5     AND e.sal BETWEEN g.losal(+) AND g.hisal(+);


DEPTNO DNAME          EMPNO ENAME            SAL      GRADE
------ -------------- ----- ---------- --------- ----------
    40 OPERATIONS                                
    10 ACCOUNTING      7839 KING         5000.00          5
    10 ACCOUNTING      7782 CLARK        2450.00          4
    10 ACCOUNTING      7934 MILLER       1300.00          2

如同这个例子中，EMP 表在和 SALGRADE 表关联时，我们可以理解为：EMP 表已经为 DEPT 表增加过一些空行了，所以 SALGRADE 表也要增加一些空行去和 EMP 表的空行关联

关于简化写法的外联结，还要注意的一点是，在12c版本以前，Oracle 对其有一个限制，那就是“表可以外部连接到至多一个其他的表”，换句话说就是，一张表只能为至多另一张表增加空行

SQL> SELECT * FROM demo_a;


COL
---
A
B


SQL> SELECT * FROM demo_b;


COL
---
B


SQL> SELECT * FROM demo_c;


COL
---
B
C


SQL> --在11g环境中
SQL> SELECT a.col, b.col, c.col
  2    FROM demo_a a, demo_b b, demo_c c
  3   WHERE a.col = b.col(+)
  4     AND b.col(+) = c.col;
SELECT a.col, b.col, c.col
  FROM demo_a a, demo_b b, demo_c c
 WHERE a.col = b.col(+)
             *
   AND b.col(+) = c.col


ORA-01417: 表可以外部连接到至多一个其他的表


SQL> --在12c环境中
SQL> SELECT a.col, b.col, c.col
  2    FROM demo_a a, demo_b b, demo_c c
  3   WHERE a.col = b.col(+)
  4     AND b.col(+) = c.col;


COL COL COL
--- --- ---
B   B   B
B       C
A       C
A       B

注意这个结果表明，DEMO_A 表先和 DEMO_C 表发生交叉联结，联结结果与 DEMO_B 表发生外联结，外联结的联结条件为“DEMO_A、DEMO_B 和 DEMO_C 的 COL 列同时相等”

SQL> SELECT a.col, b.col, c.col
  2    FROM demo_a a
  3   CROSS JOIN demo_c c
  4    LEFT JOIN demo_b b
  5      ON (a.col = b.col AND b.col = c.col);


COL COL COL
--- --- ---
B   B   B
B       C
A       C
A       B

关于外联结还有一点值得强调，区分“联结条件”和“筛选条件”完全是由条件表达式所写的位置决定的：写在 ON 后条件就是联结条件，用作验证联结关系；而写在 WHERE 后的就是筛选条件，用于在联结结束后再对结果集进行过滤加工

而且联结条件是在筛选条件之前先执行的

SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e
  3    LEFT JOIN dept d
  4      ON (e.deptno = d.deptno)
  5   WHERE d.deptno IN (10, 20);


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
SMITH          20     20 RESEARCH
JONES          20     20 RESEARCH
CLARK          10     10 ACCOUNTING
KING           10     10 ACCOUNTING
ADAMS          20     20 RESEARCH
FORD           20     20 RESEARCH
MILLER         10     10 ACCOUNTING


7 rows selected


SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e
  3    LEFT JOIN dept d
  4      ON (e.deptno = d.deptno AND d.deptno IN (10, 20));


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
MILLER         10     10 ACCOUNTING
KING           10     10 ACCOUNTING
CLARK          10     10 ACCOUNTING
FORD           20     20 RESEARCH
ADAMS          20     20 RESEARCH
JONES          20     20 RESEARCH
SMITH          20     20 RESEARCH
SCOTT          50        
JAMES          30        
TURNER         30        
BLAKE          30        
MARTIN         30        
WARD           30        
ALLEN          30        


14 rows selected

如例一中，WHERE 语句中的条件是作用于前面 EMP 表左外联结 DEPT 表后的结果集的；又如例二中，SCOTT 这一行是因为其 DEPTNO 列不符合 E.DEPTNO=D.DEPTNO 这一半联结条件，此后的行是因为其虽然满足了前一半联结条件，但没有满足后一半条件 D.DEPTNO IN (10, 20)，它们都可以被返回，因为这是外联结

至于简化写法的外联结中，联结条件和筛选条件都是写在 WHERE 后面的，那么带有(+)标记的条件被视作联结条件，其它条件被视作筛选条件

SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e, dept d
  3   WHERE e.deptno = d.deptno(+)
  4     AND d.deptno IN (10, 20);


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
SMITH          20     20 RESEARCH
JONES          20     20 RESEARCH
CLARK          10     10 ACCOUNTING
KING           10     10 ACCOUNTING
ADAMS          20     20 RESEARCH
FORD           20     20 RESEARCH
MILLER         10     10 ACCOUNTING


7 rows selected




SQL> SELECT e.ename, e.deptno, d.deptno, d.dname
  2    FROM emp e, dept d
  3   WHERE e.deptno = d.deptno(+)
  4     AND d.deptno(+) IN (10, 20);


ENAME      DEPTNO DEPTNO DNAME
---------- ------ ------ --------------
MILLER         10     10 ACCOUNTING
KING           10     10 ACCOUNTING
CLARK          10     10 ACCOUNTING
FORD           20     20 RESEARCH
ADAMS          20     20 RESEARCH
JONES          20     20 RESEARCH
SMITH          20     20 RESEARCH
SCOTT          50        
JAMES          30        
TURNER         30        
BLAKE          30        
MARTIN         30        
WARD           30        
ALLEN          30        


14 rows selected

总结

最后将一张表联结查询的韦恩示意图呈现给大家

我刘某人就是天下第一长的男人

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle PL/SQL 基础教程（5）复杂查询（二）

评论