Học Oracle cơ bản (Bài 07-subquery many cols)

LÖnh Subqueries víi nhiÒu cét Môc ®Ých Sau bµi häc, b¹n cã thÓ: Subquery víi nhiÒu cét Subquery trong mÖnh ®Ò FROM khi gi¸ trÞ null tr¶ vÒ. ViÕt lÖnh subquery trong mÖnh ®Ò FROM. Subquery nhiÒu cét Main query MANAGER 10 Subquery SALESMAN 30MANAGER 10CLERK 20 Sö dông Subqueries nhiÒu cét HiÖn ng­êi cã l­¬ng thÊp nhÊt trong mçi phßng. SELECT ENAME, SAL, DEPTNO FROM EMP WHERE (SAL, DEPTNO) IN (SELECT MIN(SAL), DEPTNO FROM EMP GROUP BY DEPTNO); Gi¸ trÞ Null trong Subquery SQL> SELECT employee.ename 2 FROM emp employee 3 WHERE employee.empno NOT IN 4 (SELECT manager.mgr 5 FROM emp manager); no rows selected. Sö dông Subquery trong mÖnh ®Ò FROM ENAME SAL DEPTNO SALAVG ---------- --------- --------- ---------- KING 5000 10 2916.6667 JONES 2975 20 2175 SCOTT 3000 20 2175 ... 6 rows selected. SQL> SELECT a.ename, a.sal, a.deptno, b.salavg 2 FROM emp a, (SELECT deptno, avg(sal) salavg 3 FROM emp 4 GROUP BY deptno) b 5 WHERE a.deptno = b.deptno 6 AND a.sal > b.salavg; Tãm t¾t Subquery nhiÒu cét tr¶ vÒ h¬n mét cét So s¸nh cét trong c¸c so s¸nh cét cã thÓ tõng cÆt hoÆc kh«ng cïng cÆp Subquery nhiÒu cét còng cã thÓ ®­îc sö dông trong mÖnh ®Ò FROM cña lÖnh SELECT.