Học Oracle cơ bản (Bài 6- Sub Query)

Subqueries Tæng Quan VÒ Bµi Häc Sau bµi häc nµy, b¹n cã thÓ: M« t¶ c¸c tr­êng hîp mµ subquery cã thÓ gi¶i quyÕt §Þnh nghÜa mét Subquery LiÖt kª c¸c kiÓu Subquery ViÕt Subquery cho mét hµng vµ nhiÒu hµng Subqueries Subquery bªn trong sÏ ®­îc thùc thi tr­íc Query chÝnh KÕt qu¶ cña Subquery ®­îc sö dông trong Query chÝnh SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); SQL> SELECT ename 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566); Sö Dông Subquery ENAME ---------- KING FORD SCOTT H­íng DÉn Sö Dông Subquery §Æt Subquery trong dÊu ngoÆc ®¬n §Æt Subquery bªn ph¶i cña phÐp to¸n so s¸nh Kh«ng thªm mÖnh ®Ò ORDER BY vµo trong mÖnh ®Ò Subquery Sö dông to¸n h¹ng trªn mét hµng ®¬n cho c¸c Subquery trªn hµng ®¬n Sö dông to¸n h¹ng trªn nhiÒu hµng cho Subquery trªn nhiÒu hµng C¸c KiÓu SUBQUERY Subquery trªn hµng ®¬n CLERK SUBQUERY Trªn Hµng §¬n Tr¶ vÒ chØ mét hµng Sö dông to¸n h¹ng trªn hµng ®¬n Operator = > >= Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to Thùc Thi SUBQUERY Trªn Hµng §¬n ENAME JOB ---------- --------- MILLER CLERK SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND sal > 8 (SELECT sal 9 FROM emp 10 WHERE empno = 7876); Sö Dông Hµm Nhãm Trong SUBQUERY ENAME JOB SAL ---------- --------- --------- SMITH CLERK 800 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp); Sö Dông MÖnh §Ò HAVING Trong SUBQUERY Oracle Server thùc thi c©u lÖnh Subquery tr­íc Oracle Server tr¶ vÒ kÕt qu¶ trong mÖnh ®Ò HAVING trong mÖnh ®Ò Query chÝnh SQL> SELECT deptno, MIN(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING MIN(sal) > 5 (SELECT MIN(sal) 6 FROM emp 7 WHERE deptno = 20); ThÕ Nµo Lµ Mét C©u LÖnh Sai? ERROR: ORA-01427: single-row subquery returns more thanone row no rows selected SQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp 6 GROUP BY deptno); C©u LÖnh SÏ Lµm ViÖc Nh­ ThÕ Nµo? no rows selected SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename='SMYTHE'); Subquery Trªn NhiÒu Hµng Tr¶ vÒ nhiÒu h¬n mét hµng Sö dông to¸n h¹ng so s¸nh nhiÒu hµng Operator IN ANY ALL Meaning Equal to any member in the list Compare value to each value returned by the subquery Compare value to every value returned by the subquery Sö Dông To¸n H¹ng ANY Trong Query Trªn NhiÒu Hµng EMPNO ENAME JOB --------- ---------- --------- 7654 MARTIN SALESMAN 7521 WARD SALESMAN SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal 'CLERK'; Sö Dông To¸n H¹ng ALL Trªn SUBQUERY NhiÒu Hµng EMPNO ENAME JOB --------- ---------- --------- 7839 KING PRESIDENT 7566 JONES MANAGER 7902 FORD ANALYST 7788 SCOTT ANALYST SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal > ALL 4 (SELECT avg(sal) 5 FROM emp 6 GROUP BY deptno); Tãm t¾t Sö dông subquery Subquery tr¶ vÒ mét gi¸ trÞ Subquery tr¶ vÒ nhiÒu gi¸ trÞ To¸n tö: ANY, ALL, IN ... Corelated subqueries Is nested subquery: lÊy hµng: tõ query ngoµi thùc hiÖn query trong, sö dông gi¸ trÞ tõ hµng cña query ngoµi Sö dông gi¸ trÞ tr¶ vÒ tõ query trong ®Ó lÊy hay kh«ng lÊy hµng ngoµi LÆp l¹i. VÝ dô: T×m ng­êi cã l­¬ng lín h¬n l­¬ng trung b×nh trong cïng phßng: SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP E WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO) /