SQL 识别并消除笛卡儿积|极客教程

SQL 识别并消除笛卡儿积，你想找出部门编号为 10 的所有员工的名字及其部门所在的城市。下面的查询返回的数据是错误的。

SQL 识别并消除笛卡儿积问题描述

你想找出部门编号为 10 的所有员工的名字及其部门所在的城市。下面的查询返回的数据是错误的。

select e.ename, d.loc
  from emp e, dept d
 where e.deptno = 10
　
ENAME      LOC
---------- -------------
CLARK      NEW YORK
CLARK      DALLAS
CLARK      CHICAGO
CLARK      BOSTON
KING       NEW YORK
KING       DALLAS
KING       CHICAGO
KING       BOSTON
MILLER     NEW YORK
MILLER     DALLAS
MILLER     CHICAGO
MILLER     BOSTON

正确的结果集如下所示。

ENAME      LOC
---------- -------------
CLARK      NEW YORK
KING       NEW YORK
MILLER     NEW YORK

SQL 识别并消除笛卡儿积解决方案

在 FROM 子句里对两个表执行连接查询，以得到正确的结果集。

1 select e.ename, d.loc
2   from emp e, dept d
3  where e.deptno = 10
4    and d.deptno = e.deptno

SQL 识别并消除笛卡儿积扩展知识

先看一下 DEPT 表的数据。

select * from dept
　
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

我们看到，编号为 10 的部门位于纽约，因此如果查询结果不是纽约，那就出错了。上述那个错误的查询语句返回的结果行数是 FROM 子句里两个表的行数的乘积。对于该查询而言，依据 EMP 表的部门编号等于 10 这一过滤条件，将产生 3 行结果。但是，由于没有对 DEPT 表做条件过滤，因此 DEPT 表中的全部 4 行数据都将被返回。3 乘以 4 等于 12，因此上述错误的查询语句会返回 12 行数据。为了消除笛卡儿积，我们通常会用到 n-1 法则，其中 n 代表 FROM 子句里表的个数，n-1 则代表消除笛卡儿积所必需的连接查询的最少次数。依据表里有什么样的键以及基于哪些列来实现表之间的连接操作，有时候必要的连接查询次数可能会超过 n-1 次，但是当我们编写查询语句的时候，n-1 法则仍然是一个很好的指导原则。