--合并结果集 --1.union all SELECT * FROM emp WHERE empno=7369 UNION ALL--单纯合并 SELECT * FROM emp WHERE empno=7369; --2.union SELECT * FROM emp WHERE empno=7369 UNION --将重复结果集合并 SELECT * FROM emp WHERE empno=7369; --------------使用命令窗口执行，查看union与or的执行计划 SQL> alter session set "_b_tree_bitmap_plans" = FALSE; SQL> explain plan for select empno,ename from emp where empno =7788 or ename='SCOTT'; SQL> select * from table(dbms_xplan.display); SQL> explain plan for select empno,ename from emp where empno=7788 UNION select empno,ename from emp where ename = 'SCOTT'; SQL> select * from table(dbms_xplan.display); --可见union会使用索引 --union会强制将结果集进行合并，从而使数据出现错误 SQL> select empno,deptno from emp where mgr=7698 order by 1; EMPNO DEPTNO ----- ------ 7499 30 7521 30 7654 30 7844 30 7900 30 SQL> select empno,deptno from emp where job='SALESMAN' order by 1; EMPNO DEPTNO ----- ------ 7499 30 7521 30 7654 30 7844 30 --从上面两条语句看出有4条结果重复，看看union和or的区别 SQL> select deptno from emp where mgr=7698 or job='SALESMAN'; DEPTNO ------ 30 30 30 30 30 SQL> select deptno from emp where mgr=7698 2 union 3 select deptno from emp where job='SALESMAN'; DEPTNO ------ 30 /************************************************************************************************************************************** * 结论： * 1.不仅两个数据集间重复的数据会被去重，而且单个数据集里重复的数据也会被去重； * 2.有重复数据的数据集用union后得到的数据与预期会不一致； * 3.可以这样，select deptno from (select empno,deptno from emp where mgr=7698 union select empno,deptno from emp where job='SALESMAN') * 或者将empno改为rowid也行。 **************************************************************************************************************************************/ --4.[inner] join与= SELECT e.empno,e.ename,d.dname,d.loc FROM emp e INNER JOIN dept d ON (e.deptno=d.deptno) WHERE e.deptno=10; SELECT e.empno,e.ename,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND e.deptno=10;--以上两句结果相同，但join是SQL-92标准，可以清晰反应表与表之间的关联关系，推荐使用join --5.in、exists和inner join CREATE TABLE emp2 AS--创建相关table SELECT ename,job,sal,comm FROM emp WHERE job = 'CLERK'; --6.要求查询与emp2相匹配的emp中的数据，可以使用in、exists或inner join SELECT * FROM emp WHERE (ename,job,sal) IN (SELECT ename,job,sal FROM emp2); SELECT * FROM emp a WHERE EXISTS (SELECT NULL FROM emp2 b WHERE b.ename=a.ename AND b.job=a.job AND b.sal=a.sal); SELECT * FROM emp a INNER JOIN emp2 b ON (b.ename=a.ename AND b.job=a.job AND b.sal=a.sal); /******************************************************************************************************** * 结论： * 1.以上三条语句可以返回相同条数的结果集； * 2.通过查看plan可以看出，exists和in都使用了HASH JOIN SEMI（哈希半连接）而inner join使用了哈希连接； * 3.exists和in的执行效率是一样的，如果不确定，可以通过查看plan来判断，不要死记硬背。 ********************************************************************************************************/ --7.内、左、右、外连接 --建立测试表 --左表 DROP TABLE L; CREATE TABLE L AS SELECT 'L_1' AS str,'1' AS v FROM dual UNION ALL SELECT 'L_2','2' AS v FROM dual UNION ALL SELECT 'L_3','3' AS v FROM dual UNION ALL SELECT 'L_4','4' AS v FROM dual; --右表 DROP TABLE R; CREATE TABLE R AS SELECT 'R_3' AS str,'3' AS v,1 AS STATUS FROM dual UNION ALL SELECT 'R_4','4' AS v,0 AS STATUS FROM dual UNION ALL SELECT 'R_5','5' AS v,0 AS STATUS FROM dual UNION ALL SELECT 'R_6','6' AS v,0 AS STATUS FROM dual; --INNER JOIN SELECT l.str AS l_str,r.str AS r_str FROM l INNER JOIN r ON l.v=r.v ORDER BY 1,2; SELECT l.str AS l_str,r.str AS r_str FROM l,r WHERE l.v=r.v ORDER BY 1,2; --LEFT JOIN SELECT l.str AS l_str,r.str AS r_str FROM l LEFT JOIN r ON l.v=r.v ORDER BY 1,2; SELECT l.str AS l_str,r.str AS r_str FROM l,r WHERE l.v=r.v(+) ORDER BY 1,2; --RIGHT JOIN SELECT l.str AS l_str,r.str AS r_str FROM l RIGHT JOIN r ON l.v=r.v ORDER BY 1,2; SELECT l.str AS l_str,r.str AS r_str FROM l,r WHERE l.v(+)=r.v ORDER BY 1,2; --FULL JOIN SELECT l.str AS l_str,r.str AS r_str FROM l FULL JOIN r ON l.v=r.v ORDER BY 1,2; --8.自连接 SELECT a.empno,a.ename,b.ename mgr,a.deptno FROM emp a,emp b WHERE a.mgr=b.empno(+) ORDER BY a.empno; --9.NOT IN,NOT EXISTS和LEFT JOIN SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO IS NOT NULL); SELECT * FROM DEPT WHERE NOT EXISTS (SELECT NULL FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO); SELECT DEPT.* FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO WHERE EMP.DEPTNO IS NULL; --10.外连接中的条件 --沿用7的测试表，查询左表所有内容，使用V关联右表，但只显示右表中STATUS为1的值，期望结果如下： STR STR --- --- L_1 L_2 L_3 R_3 L_4 --错误写法： SELECT L.STR,R.STR FROM L LEFT JOIN R ON(L.V=R.V) WHERE R.STATUS=1 ORDER BY 1; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 688663707 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 27 | 8 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 27 | 8 (25)| 00:00:01 | |* 2 | HASH JOIN | | 1 | 27 | 7 (15)| 00:00:01 | |* 3 | TABLE ACCESS FULL| R | 1 | 21 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| L | 4 | 24 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("L"."V"="R"."V") 3 - filter("R"."STATUS"=1) Note ----- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - dynamic sampling used for this statement 21 rows selected --正确写法： SELECT L.STR,R.STR FROM L LEFT JOIN R ON (L.V=R.V AND R.STATUS=1) ORDER BY 1; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2310059642 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 108 | 8 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 4 | 108 | 8 (25)| 00:00:01 | |* 2 | HASH JOIN OUTER | | 4 | 108 | 7 (15)| 00:00:01 | | 3 | TABLE ACCESS FULL| L | 4 | 24 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| R | 1 | 21 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("L"."V"="R"."V"(+)) 4 - filter("R"."STATUS"(+)=1) Note ----- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - dynamic sampling used for this statement 21 rows selected /*外层条件不要随便加，会影响结果集*/ --11.检测两个表中的数据及对应数据的条数是否相同 CREATE OR REPLACE VIEW V AS SELECT * FROM EMP WHERE DEPTNO != 10 UNION ALL SELECT * FROM EMP WHERE ENAME='SCOTT'; --要求查处视图V与表EMP中不同的数据（注意：视图中'SCOTT'有两行数据，而EMP表中只有一条数据） --分析：除了EMP表中DEPTNO为10的数据，还有ENAME为SCOTT的数据与V中不一致，因为EMP表中只有一条而V中有两条 SELECT * FROM (SELECT EMPNO,ENAME,COUNT(*) AS CNT FROM V GROUP BY EMPNO,ENAME) V --加一列统计条数用以区分不同 FULL JOIN (SELECT EMPNO,ENAME,COUNT(*) AS CNT FROM EMP GROUP BY EMPNO,ENAME) E ON V.EMPNO=E.EMPNO AND V.CNT=E.CNT WHERE V.EMPNO IS NULL OR E.EMPNO IS NULL; EMPNO ENAME CNT EMPNO ENAME CNT ---------- ---------- ---------- ----- ---------- ---------- 7788 SCOTT 2 7782 CLARK 1 7839 KING 1 7788 SCOTT 1 7934 MILLER 1 --12.聚集与内连接 CREATE TABLE EMP_BONUS (EMPNO INT,RECEIVED DATE,TYPE INT); INSERT INTO EMP_BONUS VALUES (7934,DATE '2005-5-17',1); INSERT INTO EMP_BONUS VALUES (7934,DATE '2005-2-15',2); INSERT INTO EMP_BONUS VALUES (7839,DATE '2005-5-17',3); INSERT INTO EMP_BONUS VALUES (7782,DATE '2005-5-17',1); --要求返回上述员工的工资及奖金，奖金根据TYPE来定，TYPE为1，奖金为10%... SELECT E.DEPTNO,E.EMPNO,E.ENAME,E.SAL,B.TYPE*0.1*E.SAL AS BONUS FROM EMP E,EMP_BONUS B WHERE E.EMPNO(+)=B.EMPNO; DEPTNO EMPNO ENAME SAL BONUS ------ ----- ---------- --------- ---------- 10 7934 MILLER 1300.00 130 10 7934 MILLER 1300.00 260 10 7839 KING 5000.00 1500 10 7782 CLARK 2450.00 245 --若此时想要计算6月份部门为10的总工资 --错误做法： SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E,EMP_BONUS B WHERE E.EMPNO(+)=B.EMPNO GROUP BY E.DEPTNO; DEPTNO TOTAL_SAL TOTAL_BONUS ------ ---------- ----------- 10 10050 2135 --原因，MILLER的工资和奖金都重复计算了，因为他5月份已经降职了，所以有两条不同的TYPE对应他的相关信息！ --正确做法： SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E INNER JOIN (SELECT EB.EMPNO,EB.TYPE,EB.RECEIVED FROM EMP_BONUS EB INNER JOIN (SELECT MAX(C.EMPNO) EMPNO,MAX(C.RECEIVED) RECEIVED FROM EMP_BONUS C GROUP BY C.EMPNO) EBG --先统计出员工最新的职位变更时间 ON EB.EMPNO=EBG.EMPNO AND EB.RECEIVED=EBG.RECEIVED) B ON E.EMPNO=B.EMPNO GROUP BY E.DEPTNO; DEPTNO TOTAL_SAL TOTAL_BONUS ------ ---------- ----------- 10 8750 1875 --13.聚集与外连接 SELECT E.DEPTNO,SUM(E.SAL) TOTAL_SAL,SUM(B.TYPE*0.1*E.SAL) AS TOTAL_BONUS FROM EMP E LEFT JOIN --只改此处为LEFT JOIN即可 (SELECT EB.EMPNO,EB.TYPE,EB.RECEIVED FROM EMP_BONUS EB INNER JOIN (SELECT MAX(C.EMPNO) EMPNO,MAX(C.RECEIVED) RECEIVED FROM EMP_BONUS C GROUP BY C.EMPNO) EBG --先统计出员工最新的职位变更时间 ON EB.EMPNO=EBG.EMPNO AND EB.RECEIVED=EBG.RECEIVED) B ON E.EMPNO=B.EMPNO GROUP BY E.DEPTNO ORDER BY 1; DEPTNO TOTAL_SAL TOTAL_BONUS ------ ---------- ----------- 10 8750 1875 20 10875 30 9400 --14.多表查询时的空值处理 --要求返回比ALLEN提成低的员工 --错误做法： SELECT E.ENAME,E.COMM FROM EMP E WHERE E.COMM<(SELECT COMM FROM EMP WHERE ENAME='ALLEN'); ENAME COMM ---------- --------- TURNER 0.00 --错误原因：有些员工的COMM一项为NULL，但并未返回 --正确做法： SELECT E.ENAME,E.COMM FROM EMP E WHERE COALESCE(E.COMM,0)<(SELECT COMM FROM EMP WHERE ENAME='ALLEN'); ENAME COMM ---------- --------- SMITH JONES BLAKE CLARK SCOTT KING TURNER 0.00 ADAMS JAMES FORD MILLER