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

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

以下是我的测试案例：

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