论表与表之间的关系--半连接改写
昨天听了落落的课,讲到了表与表之间的关系。
以下是我的测试案例:
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