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

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

以下是我的测试案例：

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_ID110120130140150160170180190110011101120113011401150116011701180119012001210122012301240125012601270 SELECT COUNT(*),DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID; COUNT(*)DEPARTMENT_ID61006301390220170211045503480140560110

所以可以得知表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

转载于:https://www.cnblogs.com/wanbin/p/9514716.html