Oracle 查询结果集行数分析

fteair · 发表于 2020-5-23 13:58:36

曾去某金融软件公司面试，交流中面试官问的一个问题是：“如果有 A、B 两张表，A 表中有 2 条数据，B 表中有 200 条数据，请问 SELECT * FROM A,B 能查出多少条数据？”。

听到这个问题的瞬间我就懵了，因为我自己也做过近两年的面试官，所以我首先会想的就是他问这么没有实际意义（实际开发中几乎 100% 的查询都需要条件）的问题是想考察我什么呢？同时我心里也在想，这种逗号的写法本质上就是内连接，那答案是笛卡尔积吗？我刚想对面试官说：“我没这么写过，但我分析这种写法的结果应该是笛卡尔积，也就是 2×200 等于 400……”，正在组织语言的时候被面试官打断了，他说：“没关系！我就问问！……”

现在我们来仔细研究下这个问题

首先来创建 t6、t7、t8 共 3 张表，创建语句如下：

CREATE TABLE t6 AS SELECT LEVEL f1,6 f2,0 f3 FROM DUAL CONNECT BY LEVEL<=6;CREATE TABLE t7 AS SELECT LEVEL f1,7 f2,0 f3 FROM DUAL CONNECT BY LEVEL<=7;CREATE TABLE t8 AS SELECT LEVEL f1,8 f2,0 f3 FROM DUAL CONNECT BY LEVEL<=8;

然后来看看各个表中的数据分步：

SQL> SELECT t6.* FROM t6; F1 F2 F3---------- ---------- ---------- 1 6 0 2 6 0 3 6 0 4 6 0 5 6 0 6 6 06 rows selectedSQL> SELECT t7.* FROM t7; F1 F2 F3---------- ---------- ---------- 1 7 0 2 7 0 3 7 0 4 7 0 5 7 0 6 7 0 7 7 07 rows selectedSQL> SELECT t8.* FROM t8; F1 F2 F3---------- ---------- ---------- 1 8 0 2 8 0 3 8 0 4 8 0 5 8 0 6 8 0 7 8 0 8 8 08 rows selected

接下来开始我们的实验：

SELECT COUNT(1) res FROM t6 JOIN t7 ON t6.f1=t7.f1; -- res: 6SELECT COUNT(1) res FROM t6 JOIN t7 ON t6.f2=t7.f2; -- res: 0SELECT COUNT(1) res FROM t6 JOIN t7 ON t6.f3=t7.f3; -- res: 42SELECT COUNT(1) res FROM t6 JOIN t7 ON 1=1; -- res: 42SELECT COUNT(1) res FROM t6 JOIN t7 ON 1=2; -- res: 0

在 t6 和 t7 两张表中：f1 字段值中的 1~6 是相等的，所以结果集行数是 6×1 等于 6；f2 字段值则完全不想等，所以结果集行数是 6×0 等于 0；f3 字段值全都相等，所以结果集行数是 6×7 等于 42。

SELECT COUNT(1) res FROM t6 LEFT JOIN t7 ON t6.f1=t7.f1; -- res: 6SELECT COUNT(1) res FROM t6 LEFT JOIN t7 ON t6.f2=t7.f2; -- res: 6SELECT COUNT(1) res FROM t6 LEFT JOIN t7 ON t6.f3=t7.f3; -- res: 42SELECT COUNT(1) res FROM t6 LEFT JOIN t7 ON 1=1; -- res: 42SELECT COUNT(1) res FROM t6 LEFT JOIN t7 ON 1=2; -- res: 6

左连接的时候，会返回左边表中的所有行，如果左边表中的行在右边表中没有匹配行，则结果集中右边表中的列返回空值。在这里，t7.f1 与 t6.f1 有 6 行唯一匹配，所以结果集行数是 6×1 等于 6；t7.f2 与 t6.f2 完全不匹配，所以结果集行数就是 6；t7.f3 中有 7 行能匹配 t6.f3 中的任意行，所以结果集行数是 6×7 等于 42。

接下来回到本文一开始提出的那个问题，先看查询结果：

SELECT COUNT(1) res FROM t6,t7; -- res: 42

事实上这是一个交叉连接（一定有人会晕倒），交叉连接的标准写法是：

SELECT COUNT(1) res FROM t6 CROSS JOIN t7; -- res: 42

也许是工作久了的原因，可能大部分人都还记得内连接和外连接（左连接、右连接、全连接），因为内链接和左连接还经常用。同时，估计大部分人和我一样，已经把三大连接中的交叉连接给忘得一干二净了，原因也很简单，长时间没用了！

交叉连接最典型的特点就是没有 WHERE 子句，交叉连接返回连接表中所有数据行的笛卡尔积，在关系数据库中的笛卡尔积的结果就是交叉连接所涉及的表中行数之积。

其实 CROSS JOIN 后面也是可以跟 WHERE 子句的，不过那样它就相当于内连接了。我觉得可以理解为不写条件就相当于条件恒等，这时的结果集行数就是两个表中数据行数的乘积，也就是所谓的笛卡尔积。

最后附上同样从来都用不到的全连接的验证结果：

SELECT COUNT(1) res FROM t6 FULL JOIN t7 ON t6.f1=t7.f1; -- res: 7SELECT COUNT(1) res FROM t6 FULL JOIN t7 ON t6.f2=t7.f2; -- res: 13SELECT COUNT(1) res FROM t6 FULL JOIN t7 ON t6.f3=t7.f3; -- res: 42SELECT COUNT(1) res FROM t6 FULL OUTER JOIN t7 ON 1=1; -- res: 42SELECT COUNT(1) res FROM t6 FULL OUTER JOIN t7 ON 1=2; -- res: 13

最最后附上全连接的定义：完全外部连接返回左边表和右边表中的所有行。当某行在一个表中没有匹配行时，则另一个表中的列将包含空值。如果表之间有匹配行，则整个结果集的行包含基表的数据。