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

C¸c Subquery Néi dung M« t¶ c¸c vÊn ®Ò mµ subquery cã thÓ gi¶i quyÕt §Þnh nghÜa c¸c subquery LiÖt kª c¸c kiÓu subquery ViÕt c¸c subquery trªn hµng ®¬n hay nhiÒu hµng Sö dông SUBQUERY “Ai cã møc l­¬ng lín h¬n Jones” “nh©n viªn nµo cã møc l­¬ng lín h¬n Jones?” Query chÝnh ? “møc l­¬ng cña Jones lµ bao nhiªu?” ? Subquery C¸c Subquery Subquery (query trong ) thùc thi mét lÇn tr­íc query chÝnh. KÕt qu¶ cña subquery ®­îc sö dông bëi 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 C¸c h­íng dÉn sö dông SUBQUERY §ãng subquery trong dÊu nguÆc ®¬n. §Æt subquery bªn ph¶i phÐp to¸n so s¸nh. Kh«ng ®­îc thªm mÖnh ®Ò ORDER BY vµo trong subquery Sö dông phÐp to¸n trªn hµng ®¬n cho c¸c subquery trªn hµng ®¬n Sö dông phÐp to¸n trªn nhiÒu hµng cho subquery trªn nhiÒu hµng C¸c kiÓu Subquery Single-row subquery CLERK Single-Row Subqueries Tr¶ vÒ mét hµng Sö dông c¸c phÐp to¸n so s¸nh 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 c¸c Single-Row Subquery 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 víi subquery Oracle server ch¹y subquery tr­íc. Oracle tr¶ vÒ kÕt qu¶ cho mÖnh ®Ò HAVING trong 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); C¸c c©u lÖnh kh«ng h¬p lÖ 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); Single-row operator with multiple-row subquery C©u lÖnh lµm viÖc thÕ nµo? no rows selected Subquery returns no values SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename='SMYTHE'); C¸c multiple-Row Subquery Tr¶ vÒ nhiÒu h¬n mét hµng Sö dông phÐp to¸n cho 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 ANY trong Multiple-Row Subqueries EMPNO ENAME JOB --------- ---------- --------- 7654 MARTIN SALESMAN 7521 WARD SALESMAN SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal 'CLERK'; Sö dông ALL trong Multiple-Row Subquery 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æng KÕt Subquery dïng trong tr­êng hîp t×m kiÕm dùa trªn gi¸ trÞ ch­a biÕt SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);