论表与表之间的关系--半连接改写

昨天听了落落的课，讲到了表与表之间的关系。

以下是我的测试案例：

SQL改写范例–SQL1(1:N关系)

SELECT *
  FROM DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN (SELECT E.DEPARTMENT_ID FROM EMPLOYEES E);

查询表DEPARTMENTS（department_id）与表EMPLOYEES（department_id）之间的关系

select count(*),department_id from departments group by department_id;

COUNT(*)	DEPARTMENT_ID
1	10
1	20
1	30
1	40
1	50
1	60
1	70
1	80
1	90
1	100
1	110
1	120
1	130
1	140
1	150
1	160
1	170
1	180
1	190
1	200
1	210
1	220
1	230
1	240
1	250
1	260
1	270

SELECT COUNT(*),DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

COUNT(*)	DEPARTMENT_ID
6	100
6	30
1
3	90
2	20
1	70
2	110
45	50
34	80
1	40
5	60
1	10

所以可以得知表DEPARTMENTS（department_id）与表EMPLOYEES（department_id）之间关系为1 : N

所以以上SQL可以等价改写成以下形式：

SELECT D.*
  FROM DEPARTMENTS D,(SELECT DEPARTMENT_ID FROM EMPLOYEES E GROUP BY DEPARTMENT_ID) C
  WHERE D.DEPARTMENT_ID=C.DEPARTMENT_ID

SQL改写范例–SQL2(N:1关系)

SELECT *
  FROM EMPLOYEES E
 WHERE E.DEPARTMENT_ID IN (SELECT D.DEPARTMENT_ID FROM DEPARTMENTS D);

由案例1可以得知：
表EMPLOYEES（department_id）与表DEPARTMENTS（department_id）之间的关系为N:1

所以以上SQL可以等价改写成以下形式：

SELECT E.*
  FROM EMPLOYEES E, DEPARTMENTS D
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

SQL改写范例–SQL3(N:N关系)

创建以下表，并插入数据：

CREATE TABLE EMP_TEST AS SELECT * FROM EMPLOYEES ；
INSERT INTO EMP_TEST SELECT * FROM EMP_TEST;
...
重复插入至3000多条数据后
commit；

CREATE TABLE DEPT_TEST AS SELECT * FROM DEPARTMENTS;
INSERT INTO DEPT_TEST SELECT * FROM DEPT_TEST;
...
重复插入至1700多条数据后
commit；

现在对以下SQL改写：

SELECT COUNT(*)
  FROM EMP_TEST  E
 WHERE E.DEPARTMENT_ID IN (SELECT D.DEPARTMENT_ID FROM DEPT_TEST  D);

count(*)
-------
3392

由以上建表时语句可以得知，

表EMP_TEST （department_id）与表DEPT_TEST （department_id）之间的关系为N:N

错误改写：

 SELECT COUNT(*)
  FROM EMP_TEST E, DEPT_TEST D
 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

count(*)
-------
434176

正确改写：

 SELECT COUNT(*)
   FROM EMP_TEST E,
        (SELECT DEPARTMENT_ID FROM DEPT_TEST D GROUP BY D.DEPARTMENT_ID) C
  WHERE E.DEPARTMENT_ID = C.DEPARTMENT_ID;

count(*)
-------
3392