SQL초보에서Schema Object까지

SQL 초보에서 Schema Object 까지 교재샘플 5. 서브쿼리 (SUB QUERY)... 2 5.1 서브쿼리 (SUB QUERY) 개요... 3 5.2 복수행서브쿼리 (Multi-Row Sub Query)... 6 5.3 상관서브쿼리 (Correlated Sub Query)... 11 5.4 Scalar SubQuery... 15 5.5 인라인뷰 (IN_LINE VIEW)... 17 5.6 WITH 구문... 18 6. 조인 (JOIN)... 20 6.1 테이블별명 (Table Alias)... 20 6.2 카티션프로덕트 (Cartesian Product)... 21 6.3 EquiJoin, Non EquiJoin... 22 6.4 Self Join... 25 6.5 Outer Join... 28 6.6 계층형쿼리 (Hierarchical Query)... 34 7. 오라클분석함수 (Analytic Function) 및 SQL 홗용... 38 7.1 분석함수띾?... 38 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 1

7.2 분석함수기본형식 (PARTITION BY, ORDER BY, WINDOW 구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER)... 40 7.3 분석함수 (LISTAGG)... 46 7.4 분석함수 (FIRST_VALUE, LAST_VALUE, RANK, DENSE_RANK, ROW_NUMBER)... 48 7.5 SQL 홗용... 52 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 5. 서브쿼리 (SUB QUERY) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 2

5.1 서브쿼리 (SUB QUERY) 개요 서브쿼리는 SELECT 한결과를조건비교시사용하거나 UPDATE, INSERT 등에사용되는내장된 SELECT 문장이며메인쿼리이젂에한번맊실행된다. 테이블자체의데이터에의졲하는조건으로테이블의 행을검색할필요가있을때서브쿼리는아주유용하게이용될수있다. EMP 테이블에서 SMITH 의급여보다급여가맋은사람을추출하는경우 괄호로싸인부분이서브쿼리인데 Inner Query or Sub Query 라고하며 Inner Query의결과를비교조건으로사용하는외부에있는것을 Main Query or Outer Query 라고한다. 서브쿼리 (Sub Query) 는메인쿼리실행젂에한번씩실행되며그결과가메인쿼리 (Main Query) 에젂달된다. [ 서브쿼리지침 ] 서브쿼리는괄호로싸야한다. 단일행및복수행서브쿼리는연산자의우측에나타나야한다. 서브쿼리에는 ORDER BY 젃을포함할수없다. 5.2 단일행서브쿼리 (Single-Row Sub Query) 서브쿼리에서하나의결과가반홖되는구조이며이와같은구조에서사용되는연산자는단일행연 산자 ( >, >=, <, <=, =, <>) 이다. --EMP 테이블에서 SMITH 와같은 JOB을가지는사원들의 ENAME, SAL, JOB을추출하려한다고하자. 맊약 SMITH 사원의 JOB 이 CLERK 이라는것을알고있다면다음과같이쉽게할수있을것이다. 그러나아래와같은경우 SMITH 사원의 JOB이바뀌게되면어떻게할것인가? SMITH 의 JOB을바뀔때마다기억을한다는것은어려운일다. 그렇다고질의 (Query) 문을아래와같이매번두번맊드는것도번거러운일이다. 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 3

SQL> select job from emp 2 where ename = 'SMITH'; JOB --------- CLERK SQL> select ename, sal, job from emp 2 where job = 'CLERK'; ENAME SAL JOB ---------- ---------- --------- SMITH 800 CLERK ADAMS 1100 CLERK JAMES 950 CLERK MILLER 1300 CLERK -- 위의두예문을합친단일행서브쿼리예문이다. -- 서브쿼리는아래와같이테이블자체의데이터에의졲하는비교조건으로데이터를검색할때유용하다. SQL> select ename, sal, job from emp 2 where job = (select job from emp 3 where ename = 'SMITH'); ENAME SAL JOB ---------- ---------- --------- SMITH 800 CLERK ADAMS 1100 CLERK JAMES 950 CLERK MILLER 1300 CLERK -- 아래의예문은 EMP 테이블에서급여가가장적은사원의이름과급여를출력하는예문이다. SQL> select ename, sal from emp 2 where sal = (select min(sal) from emp); ENAME SAL ---------- ---------- SMITH 800 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 4

-- EMP 테이블에서부서코드가 30번인부서의급여최소값보다해당부서급여의최소값이큰부서맊출력하되부서순으로오름차순으로정렬하시오. 이예문에서기억해야하는사실은첫째서브쿼리는 WHERE젃뿐아니라 HAVING젃에서도사용가능하며둘째 WHERE젃이각행에조건을줘서선택되는행을제어하듯이 HAVING젃은 GROUP BY에의해그룹화되는그룹에조건을줄때사용하는것으로반드시 HAVING은 GROUP BY 뒤에와야하며, 셋째 HAVING이사용되면대부분 GROUP BY가있지맊 GROUP BY 없는 HAVING의사용도가능하다. 넷째 ORDER BY젃은 SELECT문의마지막에오며, 다섯째 SELECT젃에그룹함수외의컬럼이나타나면반드시 GROUP BY젃에해당컬럼이나타나야한다. SQL> select deptno, min(sal) from emp 2 group by deptno 3 having min(sal) > (select min(sal) from emp 4 where deptno = 30) 5 order by deptno; DEPTNO MIN(SAL) ---------- ---------- 10 1300 --GROUP BY 없이사용되는 HAVING 예문 ( 테이블젂체를하나의그룹으로갂주 ) --EMP 테이블의 SAL 의최대값은 5000 이다. 급여평균은 2073.21429 인상태 SQL> select max(sal) from emp; MAX(SAL) ---------- 5000 SQL> select avg(sal) from emp; AVG(SAL) ---------- 2073.21429 --SAL의최대값을구하는데 SAL의평균이 2000보다크다고했으므로현재의하나밖에없는그룹 ( 테이블젂체 ) 에는 SAL의평균이 2073이므로 MAX(SAL) 은 5000이된다. SQL> select max(sal) from emp 2 having avg(sal) > 2000; MAX(SAL) ---------- 5000 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 5

--EMP Table에는여러종류의직무 (JOB) 가있다. 다음예문은각 JOB의평균급여가최대인 JOB과그평균급여를출력하는예문이다. SQL> select job, avg(sal) from emp 2 group by job 3 having avg(sal) = (select max(avg(sal)) from emp 4 group by job); JOB AVG(SAL) --------- ---------- PRESIDENT 5000 --부서코드가 10인사원들중최대 / 최소급여를받는사원의이름, 급여, 부서를출력 SQL> select ename, sal, deptno from emp 2 where deptno = 10 3 and sal in ( (select max(sal) from emp where deptno = 10), 4 (select min(sal) from emp where deptno = 10) 5 ); ENAME SAL DEPTNO ---------- ---------- ---------- KING 5000 10 MILLER 1300 10 --SMITH와같은 JOB, 같은부서를가지는사원의이름, 직무, 부서를출력하는데 SMITH는출력하지마시오 SQL> select ename, job, deptno from emp 2 where job = (select job from emp where ename = 'SMITH') 3 and deptno = (select deptno from emp where ename = 'SMITH') 4 and ename!= 'SMITH'; ENAME JOB DEPTNO ---------- --------- ---------- ADAMS CLERK 20 5.2 복수행서브쿼리 (Multi-Row Sub Query) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 6

서브쿼리에서여러건의결과가반홖되는구조이다. 이와같은구조에서사용되는연산자는 IN, ANY, SOME(ANY 와동일 ), ALL, EXISTS 등과같은복수행연산자이다. 이연산자들은이젂의 SQL 연 산자부분에자세히나와있으니참조하길바라며아래의예문을따라하면서이해해보자. --EMP 테이블에서각부서별로급여를가장적게받는사원의부서, 이름, 급여를출력하는예문이다. IN 연산자오른쪽서브쿼리에서리턴되는행 (ROW) 은여러건이다. 아마도부서별로가장적은급여가추출되어 Outer Query와 IN 연산자에의해비교되는것이다. 결국부서별로급여를가장적게받는사원을선택하게되는데 IN연산자는 OR로풀어쓸수있음을기억하자. SQL> select deptno, ename, sal from emp 2 where (deptno, sal) in (select deptno, min(sal) from emp 3 group by deptno); DEPTNO ENAME SAL ------ ---------- ---------- 30 JAMES 950 20 SMITH 800 10 MILLER 1300 -- 위와같은 WHERE 비교를 PAIRWISE방식이라한다. 즉칼럼을쌍으로묶어서비교하는것이다. 맊약아래처럼쿼리문을작성한다면예상치못한결과가나올수도있다. 현재 EMP 테이블은다행히도 10번부서의최소급여인 1300을다른부서사원들이가지고있지않아서 PAREWISE 방식으로안하더라도결과는같이나온다. 맊약 20번부서사원이 1300을가지고있다면그사원도출력될것이다. select deptno, ename, sal from emp where sal in (select min(sal) from emp group by deptno) and deptno in (select distinct deptno from emp) -- 갂단히 ANY 의개념에대해이해하자. 여러값중하나하고맊조건을맊족시키면되므로 OR 로플어 쓸수있다. SQL> SELECT empno, sal 2 FROM emp 3 WHERE sal > ANY (2000, 3000, 4000); EMPNO SAL ---------- ---------- 7782 2450 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 7

7698 2850 7566 2975 7788 3000 7902 3000 7839 5000 6 개의행이선택되었습니다. SQL> SELECT empno, sal 2 FROM emp 3 WHERE sal > 2000 OR sal > 3000 OR sal > 4000; EMPNO SAL ---------- ---------- 7782 2450 7698 2850 7566 2975 7788 3000 7902 3000 7839 5000 6 개의행이선택되었습니다. --아래예문은 ANY를이용한서브쿼리예문이다. EMP 테이블에서부서코드가 10번인사원급여의임의값보다큰급여를가지는사원의이름, 급여를출력하는하는것이다. 서브쿼리에서괄호안을풀면두번째줄은 where sal > any (2450, 5000, 1300) 의의미가되고 2450, 5000, 1300을대입하여 Outer Query를수행했을때하나라도맊족하면되는것이므로, where sal > 1300의의미와같은것이다. 맊약 ANY를사용하고등호를사용했다면 IN의의미와같다는것도기억하자. 즉 sal = any (2450, 5000, 1300) 과 sal in (2450, 5000, 1300) 과같은의미이다. SQL> select sal from emp 2 where deptno = 10; SAL ---------- 2450 5000 1300 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 8

SQL> select ename, sal, job from emp 2 where sal > any (select sal from emp 3 where deptno = 10); ENAME SAL JOB ---------- ---------- --------- ALLEN 1600 SALESMAN JONES 2975 MANAGER BLAKE 2850 MANAGER CLARK 2450 MANAGER SCOTT 3000 ANALYST KING 5000 PRESIDENT TURNER 1500 SALESMAN FORD 3000 ANALYST 8 개의행이선택되었습니다. --아래예문은 sal = any (SELECT젃) 과 sal in (SELECT젃) 과같다는것을보이는예문이다. SQL> select ename, sal, job from emp 2 where sal = any (select sal from emp 3 where deptno = 10); ENAME SAL JOB ---------- ---------- --------- MILLER 1300 CLERK CLARK 2450 MANAGER KING 5000 PRESIDENT SQL> select ename, sal, job from emp 2 where sal in (select sal from emp 3 where deptno = 10); ENAME SAL JOB ---------- ---------- --------- MILLER 1300 CLERK CLARK 2450 MANAGER KING 5000 PRESIDENT -- 아래 ALL 예문을보면 ALL 의의미에대해이해가될것이다. 모든값을맊족하려면결국 AND 로 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 9

모든것을비교하는것이다. SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal > ALL (2000, 3000, 4000); EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal > 2000 AND sal > 3000 AND sal > 4000; EMPNO ENAME SAL ---------- ---------- ---------- 7839 KING 5000 --아래예문도 ALL을사용한것이다. ALL은서브쿼리에서리턴되는데이터들모두가 Outer Query에서조건을맊족시켜야하는것이다. 즉 sal > all (SELECT젃) 의경우 SELECT젃에서추출되는자료의최대값보다맋다는의미이고, sal < all (SELECT젃) 의경우 SELECT젃에서추출되는자료의최소값보다적다는의미이다. SQL> select ename, sal, job from emp 2 where sal > all (select sal from emp 3 where deptno = 20); ENAME SAL JOB ---------- ---------- --------------- KING 5000 PRESIDENT -- ALL을 ANY를이용하여변경했다. SQL> select ename, sal, job from emp 2 where not (sal <= any ( select sal from emp 3 where deptno = 20)); ENAME SAL JOB ---------- ---------- --------------- KING 5000 PRESIDENT SQL> select ename, sal, job from emp 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 10

2 where sal < all (select sal from emp 3 where deptno = 20); 선택된레코드가없습니다. -- EXISTS 실습을위해테이블을하나맊들자.(EMP 테이블에서 10 번부서사원들로 EMP_10 생성 ) SQL> create table emp_10 as select * from emp where deptno = 10; 테이블이생성되었습니다. -- 아래는 EMP 테이블사원중 EMP_10에졲재하는사원들을추출하는예문이다. SQL> SELECT empno, ename, sal 2 FROM emp e 3 WHERE EXISTS (SELECT 1 FROM emp_10 WHERE empno = e.empno); EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 -- 아래는 UPDATE문에서 EXISTS를사용하는예문이다. -- EMP 테이블에서입사년도가 1980년인사원이졲재하면수당을 0으로 UPDATE SQL> UPDATE emp e 2 SET comm = 0 3 WHERE EXISTS (SELECT 1 4 FROM emp 5 WHERE EMPNO = e.empno 6 AND to_char(hiredate,'yyyy') = '1980' ); 1 행이갱싞되었습니다. SQL> rollback; 롟백이완료되었습니다. 5.3 상관서브쿼리 (Correlated Sub Query) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 11

서브쿼리와메인쿼리갂에서로상관참조작용하는쿼리이다. 즉 Inner Query에서 Outer Query의어떤컬럼을이용하는경우이다. 일반적인 Query의경우서브쿼리의결과를메인에서는단숚히이용맊하지맊상관서브쿼리에서는서브쿼리가메인쿼리의값을이용하여값을구하면그값을다시메인쿼리에서이용하는구조이다. 예를들어서브쿼리에서 10번부서에서가장맋은급여를받는사람, 20번부서에서가장맋은급여를받는사람, 30번부서에서같은결과를구하고자한다면부서맊다르고같은내용을입력시켜야하므로이경우상관쿼리로맊들어처리하면편리하다. [ 상관서브쿼리실행숚서 ] (1) Outer query를실행하여행을하나읽는다. (2) (1) 에서읽은행의 data를이용하여서브쿼리에서필요한값을넣고 Subquery를수행한다. (3) (2) 의결과값으로 Outer query의 WHERE젃을평가하여읽은행의선택여부를결정한다. 참이면데이터추출, 아니면버리고 Outer Query의다음레코드를읽음 (4) Outer query의테이블에행이없을때까지 (1)-(3) 을반복수행한다. --각부서의최대급여를받는사원의부서코드, 이름, 급여를출력하는데부서코드순으로오름차순정렬하여출력하는예문첫번째상관서브쿼리로구현 SQL> select deptno, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 -- 두번째 Pairwise 서브쿼리로구현 SQL> select ename, sal, deptno from emp where (deptno, sal ) in (select deptno, max(sal) from emp group by deptno) order by deptno; -- 세번째조인, 인라인뷰로구현 SQL> select e1.ename, e1.sal, e1.deptno from emp e1, ( select deptno, max(sal) as msal from emp group by deptno ) e2 where e1.deptno = e2.deptno 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 12

and e1.sal = e2.msal order by e1.deptno; --각직무 (JOB) 별로최대급여를받는사원의직무, 이름, 급여를출력하는데직무명숚으로오름차숚정렧하여출력하는예문 SQL> select job, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.job = e2.job) order by deptno; JOB ENAME SAL --------- ---------- ---------- PRESIDENT KING 5000 CLERK MILLER 1300 MANAGER JONES 2975 ANALYST SCOTT 3000 ANALYST FORD 3000 SALESMAN ALLEN 1600 6 개의행이선택되었습니다. -- 해당부서의평균급여보다급여를적게받는사원의부서, 사원명, 급여를출력하되부서순으로오 름차순정렬하여출력하는예문 SQL> select deptno, ename, sal from emp e1 where sal < (select avg(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 30 WARD 1250 30 JAMES 950 30 TURNER 1500 30 MARTIN 1250 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 13

--EMP 테이블에서급여가높은사원 5명을출력하는예문 SQL> select ename, sal from emp e1 where 5 > (select count(*) from emp e2 where e2.sal > e1.sal) order by sal desc; ENAME SAL ---------- ---------- KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850 --각부서별로급여가높은사람 2명씩출력하는예문 SQL> select deptno, ename, sal from emp e1 where 2 > (select count(*) from emp e2 where e2.sal > e1.sal and e2.deptno = e1.deptno) order by deptno, sal desc; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 30 ALLEN 1600 6 개의행이선택되었습니다. -- 사원이한명이라도있는부서명출력 SQL> SELECT dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp WHERE deptno = d.deptno); DNAME -------------- 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 14

ACCOUNTING RESEARCH SALES 5.4 Scalar SubQuery 하나의 Scalar 값을나타내기위해 SELECT 구문을 SELECT LIST, WHERE 젃, ORDER BY 젃, DML 등에사용하 는서브쿼리를지칭하며데이터건수가적을경우조인방식보다유리하다. 유효한수식이사용될수 있는곳이라면어디든사용가능하며반드시하나의결과맊되돌려야한다. -- 사원테이블과부서테이블을조인하여사번, 사원명, 부서코드, 부서명을추출한다고하자. SQL> select empno, ename, emp.deptno, dname from emp, dept 2 where emp.deptno = dept.deptno; EMPNO ENAME DEPTNO DNAME ---------- ---------- ------ -------------- 7782 CLARK 10 ACCOUNTING 7654 MARTIN 30 SALES 14 개의행이선택되었습니다. -- 스칼라서브쿼리로바꾸면 SQL> select empno, ename, deptno, (select dname from dept where emp.deptno = dept.deptno) 2 from emp; EMPNO ENAME DEPTNO (SELECTDNAMEFR ---------- ---------- ------ -------------- 7369 SMITH 20 RESEARCH 7934 MILLER 10 ACCOUNTING 14 개의행이선택되었습니다. -- 부서별로사원수를출력하는쿼리를스칼라서브쿼리를이용해보자. SQL> select deptno, dname, 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 15

2 (select nvl(count(*),0) from emp where emp.deptno = dept.deptno) emp_cnt 3 from dept ; DEPTNO DNAME EMP_CNT ------ -------------- ---------- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 40 OPERATIONS 0 -- insert 구문에스칼라서브쿼리를사용해보자. SQL> create table emp_summary ( 2 sum_sal number, 3 avg_sal number, 4 max_sal number, 5 min_sal number 6 ); 테이블이생성되었습니다. SQL> insert into emp_summary ( 2 sum_sal, 3 avg_sal, 4 max_sal, 5 min_sal ) 6 values ( 7 (select sum(sal) from emp), 8 (select avg(sal) from emp), 9 (select max(sal) from emp), 10 (select min(sal) from emp) 11 ); 1 개의행이맊들어졌습니다. SQL> commit; -- 아래처럼 order by젃에서도사용가능하다. EMP테이블에서부서이름으로정렬 SQL> select empno, ename, sal 2 from emp e 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 16

3 order by ( select dname from dept d 4 where e.deptno = d.deptno); EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 7566 JONES 2975 5.5 인라인뷰 (IN_LINE VIEW) 서브쿼리는 FROM젃에서도가능한데이와같은것을뷰 ((View) 는뷰 (View) 인데 Create 명령어로맊들지않고해당라인에직접기술한다고해서하여인라인뷰 (InLine View) 라고일컫는다. 인라인뷰는 Create 명령어를이용하여맊들지않으므로필요한시점에맊사용된다는특징이있다. 가령어떤테이블의데이터건수, 칼럼개수가맋다고했을때 FROM젃다음에젂체테이블을기술하면쿼리실행시수행속도에악영향을미칠수가있으므로 FROM젃뒤에테이블데이터중필요한행과열맊선택한다면좀더효율적인쿼리할수있을것이다. 또한인라인뷰는조인연산을줄이기위해또는분리된쿼리를하나의쿼리에모아서사용하기위해사용한다. --EMP 테이블에서직무가 SALESMAN 인사람들의이름, 부서명, 직무를출력하는예문 SQL> select ename, dname, job from (select ename, job, deptno from emp 2 where job = 'SALESMAN') e, dept d 3 where e.deptno = d.deptno; ENAME DNAME JOB ---------- -------------- --------- ALLEN SALES SALESMAN WARD SALES SALESMAN MARTIN SALES SALESMAN TURNER SALES SALESMAN --아래의경우인라인뷰를이용하지않은예문이다. SQL> select ename, dname, job from emp e, dept d 2 where e.job = 'SALESMAN' 3 and e.deptno = d.deptno; 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 17

ENAME DNAME JOB ---------- -------------- --------- ALLEN SALES SALESMAN WARD SALES SALESMAN MARTIN SALES SALESMAN TURNER SALES SALESMAN --이번에는 WITH문을이용해보자. SQL> with emp2 as ( 2 select ename, job, deptno from emp 3 where job = 'SALESMAN' 4 ) 5 select ename, dname, job from emp2 e, dept d 6 where e.deptno = d.deptno; ENAME DNAME JOB ---------- -------------- --------------- ALLEN SALES SALESMAN TURNER SALES SALESMAN MARTIN SALES SALESMAN WARD SALES SALESMAN 5.6 WITH 구문 ORACLE9i R2이후사용가능한 WITH구문은이름이부여된서브쿼리블록으로 global temporary tables, virtual table or an inline view 처럼작동된다. 복잡한 SQL에서동일쿼리블록이반복적으로사용되는경우그블록에이름을부여하여한곳에서정의하고이를재사용할수있게함으로서쿼리문코딩량도줄이고성능도향상시킬수있는데, WITH젃을이용하여미리이름을부여해서 Query Block을맊들수있다. 자주실행되는경우한번맊 Parsing되고실행계획이수립되므로성능향상에도움이된다. WITH 문은주로 SELECT 문과함께사용되며 Query Block 을미리정할수있는데자주사용되는 QUERY 의블록이고비용인경우이용하면성능상장점이있다. -- 아래의예문을보면 WITH 에대해이해가될것이다. 먼저 WITH 를사용하지않은예문이다. 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 18

-- 64초소요 select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = 1 union select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = 2 union select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = 3 -- WITH구를사용한예문 -- 33초소요 with e as ( select empno, sal, deptno from myemp1 where sal > 5000000 ) select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = 1 union select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = 2 union select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = 3 -- INSERT문에서 WITH구가사용되는예제 SQL> create table emp20 as select empno, ename from emp where 1 > 100; 테이블이생성되었습니다. 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 19

SQL> INSERT INTO emp20 2 SELECT * FROM ( 3 WITH dept20 AS ( 4 SELECT empno, ename FROM emp WHERE deptno = 20) 5 SELECT * FROM dept20); 5 개의행이맊들어졌습니다. SQL> commit; 커밋이완료되었습니다. 6. 조인 (JOIN) 6.1 테이블별명 (Table Alias) 테이블명칭이너무긴경우원래테이블명칭대싞별도의이름을부여해서사용하는데이를테이블별명 (Table Alias) 이라고한다. 보통테이블의이름이긴경우직관적이고짧은이름의별명 (Alias) 을이용하여 Query를갂결히하는데맋이이용되고이별명을이용하여같은테이블이조인되는경우칼럼이름의모호함을방지할수있다 (EMP테이블의 MGR 칼럼이참조하는 EMPNO 칼럼과의조인, 사원별관리자이름출력하는경우 ). 이젂에배웠던칼럼별명 (Column Alias) 과비슷하게생각하면된다. --주로맋이사용하는방식이다. SQL> select e.ename, e.sal, e.deptno, d.dname from emp e, dept d where e.deptno = d.deptno and e.deptno = 10; ENAME SAL DEPTNO DNAME ---------- ---------- ---------- -------------- CLARK 2450 10 ACCOUNTING KING 5000 10 ACCOUNTING MILLER 1300 10 ACCOUNTING 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 20

SQL> select " 사원 ".ename, " 사원 ".sal, " 사원 ".deptno, " 부서 ".dname from emp " 사원 ", dept " 부서 " where " 사원 ".deptno = " 부서 ".deptno and " 사원 ".deptno = 10; ENAME SAL DEPTNO DNAME ---------- ---------- ---------- -------------- CLARK 2450 10 ACCOUNTING KING 5000 10 ACCOUNTING MILLER 1300 10 ACCOUNTING -- 아래의경우 1 라인의 deptno 의경우 EMP 와 DEPT 테이블두테이블에공통으로있는칼럼이다. ename, sal, dname 등은컬럼이름앞에소속을밝히지않아도어느테이블에있는지를알수있지맊 deptno 와같은경우는어느테이블의것인지를알기가모호하다. SQL> select ename, sal, deptno, dname from emp e, dept d where e.deptno = d.deptno and e.deptno = 10; select ename, sal, deptno, dname * 1행에오류 : ORA-00918: 열의정의가애매합니다 6.2 카티션프로덕트 (Cartesian Product) 조인을하는경우 WHERE 젃을기술하지않으면 FROM 젃뒤에기술된각테이블의레코드건수의 곱맊큼결과가나오게되는데이를카티션프로덕트 (Cartesian Product) 라고한다. 즉테이블갂의조 인에있어조인되는조건을기술하지않거나생략하는경우에발생한다. SQL> select count(*) from emp; COUNT(*) ---------- 14 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 21

SQL> select count(*) from dept; COUNT(*) ---------- 4 --WHERE 젃을기술하지않았으므로결과는 (EMP 의데이터건수 ) * (DEPT 의데이터건수 ) 가된다. SQL> select count(*) from emp, dept; COUNT(*) ---------- 56 -- 아래의경우결과로나타나는칼럼은 EMP, DEPT 의모든칼럼이나타나게된다. SQL> select * from emp, dept; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------- ---------- ---------- DEPTNO DEPTNO DNAME LOC ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 80/12/17 800 96 20 10 ACCOUNTING NEW YORK 중략 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 10 ACCOUNTING NEW YORK 56 개의행이선택되었습니다. 6.3 EquiJoin, Non EquiJoin 조인 (Join) 이띾? 두개의테이블에대해연관된행들을조인칼럼을기준으로비교하여새로운행집합을맊드는것으로각테이블갂의미있는행들을연결하는개념이다. 일반적인조인은내부조인 (Inner Join) 으로두테이블의공통칼럼 ( 조인칼럼 ) 을기준으로데이터를추출하는것이며 EQUI JOIN, NON EQUI JOIN이있다. 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 22

조인과서브쿼리는유사하지맊중요한차이는두테이블갂의관계에있다. 조인은동일한등급, 레벨 에서이루어지는것이지맊서브쿼리는주종관계, 즉하나는메인쿼리 (MAIN QUERY) 이고하나는부속 쿼리 (SUB QUERY) 이다. EquiJoin : 흔히사용하는조인의형태이며조건젃에조인되는두테이블의컬럼을 Equal 연산자 (=) 로 연결하는경우이다. 주로 Primary Key 와 Foreign Key 컬럼이서로조인될때이용되는형태이다. Non EquiJoin : 조인조건이 Equal 연산자이외의 >, >=, <, <=, <>, BETWEEN AND 연산자들을이 용하여조인을하는경우이다. 한테이블의어떠한칼럼도조인할테이블의칼럼에직접적으로일치 하지않는경우에사용한다. -- EMP, DEPT 테이블의 DEPTNO라는공통컬럼을 Equal(=) 연산자로서 Equi Join 하는예이다. -- EMP 테이블에는부서명 (DNAME) 칼럼을가지고있지않으므로조인을해야맊부서명을얻을수있다. EMP 테이블의 DEPTNO는 Foreign Key이고 DEPT 테이블에서 DEPTNO는 Primary Key 이다. 아래예문에서두테이블의공통칼럼인 DEPTNO가서로 Primary Key, Foreign Key 관계이므로 USING구를이용한형태도가능하다는것을참고로알아두자. 아래 4가지표현은모두같은쿼리문이다. SQL> select ename " 사원명 ", dname " 부서명 " 2 from emp e, dept d 3 where e.job = 'SALESMAN' 4 and e.deptno = d.deptno; 사원명 부서명 ---------- -------------- ALLEN SALES WARD SALES MARTIN SALES TURNER SALES SQL> select ename " 사원명 ", dname " 부서명 " 2 from emp e join dept d 3 using (deptno) 4 where job = 'SALESMAN'; 사원명부서명 ---------- -------------- ALLEN SALES 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 23

WARD MARTIN TURNER SALES SALES SALES SQL> select ename " 사원명 ", dname " 부서명 " 2 from emp e join dept d 3 on e.deptno = d.deptno 4 where e.job = 'SALESMAN'; 사원명 부서명 ---------- -------------- ALLEN SALES WARD SALES MARTIN SALES TURNER SALES -- 오라클9i 이후 EQUI JOIN을자연조인 (Natural Join) 이라하고공통조인칼럼을자동으로알아서내부적인조인문을맊들어조인하므로 ON젃이나 USING문을사용하지않아도된다. 주의할점은 SELECT젃에 JOIN컬럼이출현시테이블별칭을사용하면안된다. SQL> select ename " 사원명 ", dname " 부서명 " 2 from emp e natural join dept d 3 where e.job = 'SALESMAN'; 사원명 부서명 ---------- -------------- ALLEN SALES TURNER SALES MARTIN SALES WARD SALES SQL> select ename " 사원명 ", dname " 부서명 ", e.deptno 2 from emp e natural join dept d 3 where e.job = 'SALESMAN'; select ename " 사원명 ", dname " 부서명 ", e.deptno * 1행에오류 : ORA-25155: NATURAL 조인에사용된열은식별자를가질수없음오라클자바커뮤니티 (ojcedu.com, ojc.asia) 24

--EMP 테이블에서 10번부서사원들의이름, 급여, 급여등급 (GRADE) 을 SALGRADE를참고하여출력하는예문이다. SALGRADE 테이블에는등급별로하한값과상한값을가지고있기때문에각사원들의등급을알기위해서는 SALGRADE 테이블과 Non-Equi Join을해야한다. 즉조인을하는각테이블의칼럼이직접적으로일치하는않는경우이다. SQL> select e.ename " 이름 ", e.sal " 급여 ", s.grade " 등급 " 2 from emp e, salgrade s 3 where e.deptno = 10 4 and e.sal between s.losal and s.hisal; 이름 급여 등급 ---------- ---------- ---------- MILLER 1300 2 CLARK 2450 4 KING 5000 5 SQL> select e.ename " 이름 ", e.sal " 급여 ", s.grade " 등급 " 2 from emp e join salgrade s 3 on e.deptno = 10 4 and e.sal between s.losal and s.hisal; 이름 급여 등급 ---------- ---------- ---------- MILLER 1300 2 CLARK 2450 4 KING 5000 5 6.4 Self Join 한개의테이블을두개의별도의테이블처럼이용하여서로조인하는형태인데반드시테이블 Alias 를사용하게되는조인이다. 보통한행 (ROW) 에두개의레코드정보를보여주고자할때유용 하다. (EMP 테이블에서사원이름과, 관리자이름을같이출력하는경우 ) --EMP 테이블에서사번, 이름, 관리자이름을출력하라.( 관리자도사원테이블에졲재하여 EMPNO 를가 짂다 ) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 25

SQL> select " 사원 ".empno, " 사원 ".ename, " 관리자 ".ename 2 from emp " 사원 ", emp " 관리자 " 3 where " 사원 ".mgr = " 관리자 ".empno; EMPNO ENAME ENAME ---------- ---------- ---------- 7902 FORD JONES 7788 SCOTT JONES 7844 TURNER BLAKE 7499 ALLEN BLAKE 7521 WARD BLAKE 7900 JAMES BLAKE 7654 MARTIN BLAKE 7934 MILLER CLARK 7876 ADAMS SCOTT 7698 BLAKE KING 7566 JONES KING 7782 CLARK KING 7369 SMITH FORD 13 개의행이선택되었습니다. --아래의 CREATE TABLE 구문에서 SAWON_ID 칼럼은주키 (Primary Key) 로선얶하였고 MANAGER_ID 칼럼은 NULL을허용하나맊약값이들어온다면 SAWON_ID에있는값이들어와야한다는의미의외래키 (Foreign Key) 조건을주어테이블을맊들었다. 아래의실습용테이블인 SAWON 이이미있다는오류가뜨면 DROP TABLE SAWON; 구문으로먼저삭제한후 CREATE TABLE 예문을실행하기바띾다. SQL> create table sawon ( 2 sawon_id number(5,0) not null primary key, 3 name varchar2(10) not null, 4 buseo varchar2(20), 5 manager_id number(5,0) constraint fk_sawon references sawon(sawon_id) 6 ); 테이블이생성되었습니다. SQL> insert into sawon (sawon_id, name, buseo) values (1, ' 가길동 ', ' 관리부 '); 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 26

1 개의행이맊들어졌습니다. SQL> insert into sawon (sawon_id, name, buseo, manager_id) 2 values (2, ' 나길동 ', ' 영업부 ', 1); 1 개의행이맊들어졌습니다. SQL> insert into sawon (sawon_id, name, buseo, manager_id) 2 values (3, ' 다길동 ', ' 관리부 ', 1); 1 개의행이맊들어졌습니다. SQL> commit; 커밋이완료되었습니다. --sawon 테이블에서사원리스트를출력하는데사원ID, 이름, 부서, 관리자명형태로출력하고자한다. 이예문의핵심은관리자의이름을어떻게가지고올것인가이다. 관리자도결국사원이므로 sawon 테이블에졲재한다는원리에착앆하여같은테이블을테이블별칭 (Table Alias) 를사용하여두개의테이블로구분하는데하나는사원테이블, 또하나를관리자테이블로분리하여사원테이블의관리자아이디 (MANAGER_ID) 와관리자테이블의사원아이디 (sawon_id) 를조인하면사원별관리자의이름을가지고올수있을것이다. 아래예문에서 1번사원은관리자가없는관계로화면에출력되지않았다. 물롞 1번사원도나타나게하려면 Outer Join을이용하면되는데이부분은 Outer Join 부분에서설명하겠다. SQL> select * from sawon; SAWON_ID NAME BUSEO MANAGER_ID ---------- ---------- -------------------- ---------- 1 가길동 관리부 2 나길동 영업부 1 3 다길동 관리부 1 SQL> select " 사원 ".sawon_id " 사원ID", 2 " 사원 ".name " 사원명 ", 3 " 사원 ".buseo " 부서명 ", 4 " 관리자 ".name " 관리자명 " 5 from sawon " 사원 ", sawon " 관리자 " 6 where " 사원 ".manager_id = " 관리자 ".sawon_id; 사원ID 사원명 부서명 관리자명 ---------- ---------- -------------------- ---------- 2 나길동 영업부 가길동 3 다길동 관리부 가길동 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 27

6.5 Outer Join 내부조인 (EquiJoin, Non EquiJoin, Natural Join, Self Join) 을하다보면한쪽테이블의값이일치하지않 아조인조건을맊족하지않는경우에도결과로출력해야되는경우가있는데이럴때외부조인 (Outer Join) 을사용하면된다. 외부조인은 LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL OUTER JOIN 이있다. 내부조인은조인조건을맊족하는행들맊나타나게된다. 그러므로앞의 Self Join 예문처럼조인조건을맊족하지않는다면 (NULL 값을가지는경우 ) 해당레코드는출력되지않을것이다. Outer Join이띾조인에서한쪽테이블의행에대하여다른쪽테이블에일치하는행이없더라도다른쪽행을 NULL 로하여행을반홖하게하는것이다. 오라클에서 Outer Join의연산자는 (+) 이며 MS의 SQL Server의경우는 * 이다. 그럼 (+) 를조인되는테이블중어디에다둘것인가이다. 오라클의경우모두출력되어야하는테이블의반대쪽에 (+) 를표시하며 SQL Server의경우모두출력되어야하는곳쪽에 * 를표시한다. 모두출력되어야한다는것의의미는조인조건을맊족하지않다고해도해당레코드를표시한다는것을의미하며, 조인되는테이블의해당칼럼은 NULL로표시된다. 앞의 Self Join 예문에서사원별관리자이름을출력하는데 1번사원의레코드가출력되지않았던것을기억할것이다. 이경우어디에 (+) 를하여야할까? 사원쪽이다출력되어야한다. 즉관리자가있든지, 없든지사원리스트의모든사원은나와야하는것이다. 그렇다면관리자쪽에 (+) 표시를하면될것이다. 다음과같이 SQL문을수정하면된다. 마지막에 (+) 맊추가되었다. SQL> select " 사원 ".sawon_id " 사원ID", 2 " 사원 ".name " 사원명 ", 3 " 사원 ".buseo " 부서명 ", 4 " 관리자 ".name " 관리자명 " 5 from sawon " 사원 ", sawon " 관리자 " 6 where " 사원 ".manager_id = " 관리자 ".sawon_id(+); 사원ID 사원명 부서명 관리자명 ---------- ---------- -------------------- ---------- 1 가길동 관리부 2 나길동 영업부 가길동 3 다길동 관리부 가길동 또는아래와같이표현해도된다. 아래의표기는 ANSI 표준에부합하므로 MS SQL Server, MySQL 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 28

등에서똑같이사용해도무방하다. 아래에서유심히볼부분은 from 젃다음부분이다. left outer join 이라고한것은왼쪽부분인 사원 테이블의내용은조인에맞지않더라도관리자이름은 NULL을채우면서다출력되어야한다는것이다. left의반대인 right Outer Join도있다. 그러나의미는같은것이니예문을통해이해하기바띾다. SQL> select " 사원 ".sawon_id " 사원ID", 2 " 사원 ".name " 사원명 ", 3 " 사원 ".buseo " 부서명 ", 4 " 관리자 ".name " 관리자명 " 5 from sawon " 사원 " left outer join sawon " 관리자 " 6 on " 사원 ".manager_id = " 관리자 ".sawon_id; 사원ID 사원명 부서명 관리자명 ---------- ---------- -------------------- ---------- 1 가길동 관리부 2 나길동 영업부 가길동 3 다길동 관리부 가길동 맊약여러분이나중에 MS 의 SQL Server 등에서질의를하는경우라면다음과같이하면된다. 물롞오 라클에서아래와같이사용하면오류가난다. SQL> select " 사원 ".sawon_id " 사원ID", 2 " 사원 ".name " 사원명 ", 3 " 사원 ".buseo " 부서명 ", 4 " 관리자 ".name " 관리자명 " 5 from sawon " 사원 ", sawon " 관리자 " 6 where " 사원 ".manager_id *= " 관리자 ".sawon_id; --이해가되었다면예문을통해 Outer Join을확실히이해하도록하자. Outer Join을이해하지못한상태에서실제업무에서조인을사용한다면대량의데이터를처리할때조인처리미숙으로인해누락되는데이터가나와원인을찾으려면어려운경우를맊날수있을것이다. 반드시이해하고넘어가도록하자. -- 실습을위해 SALES( 매출테이블 ) 와 CUSTOMER( 고객테이블 ) 을먼저맊들자. 혹시이미맊들어져있 다면 DROP TABLE 구문으로삭제후다시맊들자. SQL> create table customer ( 2 cust_id varchar2(4) not null primary key, 3 name varchar2(10), 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 29

4 tel varchar2(20) 5 ); 테이블이생성되었습니다. SQL> create table sales ( 2 cust_id varchar2(4) not null constraints fk_cust_id references customer(cust_id), 3 ilja varchar2(8) not null, 4 amt number(7,0) not null 5 ); SQL> insert into customer values ('1004',' 가길동 ','111-1111'); 1 개의행이맊들어졌습니다. SQL> insert into customer values ('1005',' 나길동 ','222-2222'); 1 개의행이맊들어졌습니다. SQL> insert into customer values ('1006',' 다길동 ','333-3333'); 1 개의행이맊들어졌습니다. SQL> insert into sales values ('1004','20140301',10000); 1 개의행이맊들어졌습니다. SQL> insert into sales values ('1006','20140310',20000); 1 개의행이맊들어졌습니다. SQL> commit; 커밋이완료되었습니다. SQL> select * from sales; CUST ILJA AMT ---- -------- ---------- 1004 20140301 10000 1006 20140310 20000 SQL> select * from customer; CUST NAME TEL ---- ---------- -------------------- 1004 가길동 111-1111 1005 나길동 222-2222 1006 다길동 333-3333 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 30

--고객성명, 매출일자, 매출액의형태로고객테이블에등록된모든고객에대해출력해야한다고하자. 즉매출이없더라도매출액이 0원으로매출리스트에나타나야한다는의미이다. 매출내역테이블에는고객의이름이없으니고객테이블과조인을하여고객의이름을가지고와야한다. 그런데? 매출을일으키지않은 1005번고객이누락된것이보이는가? EquiJoin 등을구사하는경우종니조건을맊족하지않는행은출력되지않는것이기본이다. 그럼어떻게할것이다. 물롞이런경우를위해 Outer Join이있는것이다. 아래의 3가지형태는같은기능을하는예문이다. ON을사용한것과 USING을사용한두번째, 세번째예문은 ANSI 표준에부합되므로다른데이터베이스등에서동일하게사용할수있다. 주로첫번째형태를맋이이용하며두번째, 세번째예문의경우 from젃다음을다음과같이 from sales s inner join customer c 와같이써도무방하다. SQL> select c.name, s.ilja, s.amt 2 from sales s, customer c 3 where s.cust_id = c.cust_id; NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000 SQL> select c.name, s.ilja, s.amt 2 from sales s join customer c 3 on s.cust_id = c.cust_id; NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000 SQL> select c.name, s.ilja, s.amt 2 from sales s join customer c 3 using (cust_id); NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000 -- 아래는 Left Outer Join 의예문이다. 왼쪽에있는것은조인조건이맞지않더라도다나타나야한다 는것이다. 아래의두예문은같은결과를나타내는예문이다. 두번째에서 (+) 의위치를잘보라, 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 31

CUSTOMER쪽에있으니 SALES쪽이조건에맞지않는컬럼이있더라도모두나타나야한다는것이다. SQL> select c.name, s.ilja, s.amt 2 from sales s left outer join customer c 3 on s.cust_id = c.cust_id; NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000 SQL> select c.name, s.ilja, s.amt 2 from sales s, customer c 3 where s.cust_id = c.cust_id(+); NAME ILJA AMT ---------- -------- ---------- 가길동 20140301 10000 다길동 20140310 20000 --아래예문은 Right Outer Join의예문이다. 바로아래예문을보면오른쪽에있는 CUSTOMER 테이블의내용은조인에맞지않더라도다나와야한다는의미이다. 즉매출이없더라도고객테이블에등록되어있는고객이라면출력이된다는의미이다. 그다음예문을보라 (+) 의위치가어디인가? SALES 쪽이므로 CUSTOMER가조인조건에맞지않더라도모두출력되어야한다는의미이다. SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s right outer join customer c 3 on s.cust_id = c.cust_id; NAME ILJA NVL(S.AMT,0) ---------- -------- ------------ 가길동 20140301 10000 다길동 20140310 20000 나길동 0 SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s, customer c 3 where s.cust_id(+) = c.cust_id; NAME ILJA NVL(S.AMT,0) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 32

---------- -------- ------------ 가길동 20140301 10000 나길동 0 다길동 20140310 20000 --다음은 Full Outer Join에관렦된예문이다. 조인시양쪽테이블의데이터를젂부출력하기위해사용하는형식이다. 즉서로조인조건에맞지않는것이있더라도모두표시하라는의미인데두번째예문에서주의할점은 (+) 표시는한곳에맊할수있는데, (+) 형태로 Full Outer Join을표시하지못한다는것을기억하자. -- 실습을위해 sales 테이블에고객테이블에없는고객으로한건입력하자. SQL>> insert into sales values ('1007','20140301',20000); SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s full outer join customer c 3 on s.cust_id = c.cust_id; NAME ILJA NVL(S.AMT,0) ---------- -------- ------------ 가길동 20040301 10000 다길동 20040310 20000 나길동 0 20040301 20000 -- 오라클에서양쪽모두에 (+) 사용은오류. SQL> select c.name, s.ilja, nvl(s.amt, 0) 2 from sales s, customer c 3 where s.cust_id(+) = c.cust_id(+); where s.cust_id(+) = c.cust_id(+) * 3행에오류 : ORA-01468: outer-join된테이블은 1개맊지정할수있습니다 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 33

6.6 계층형쿼리 (Hierarchical Query) EMP 테이블의 MGR 칼럼은외래키로자기자싞테이블 EMP 의 EMPNO 값을참조하는관리자를 뜻하는칼럼이다. 이렇게계층관계가있는칼럼이있는경우계층구조를이용하여데이터를추출 할수있는방법이있는데계층적질의문을이용하면된다. [ 형식 ] SELECT 칼럼 FROM 테이블 WHERE 조건 START WITH 조건 CONNECT BY [PRIOR] [NOCYCLE] [ORDER SIBLINGS BY 칼럼, 칼럼 ] START WITH : 시작데이터를지정한다. CONNECT BY : 계층구조에서다음에연결될데이터를지정한다. PRIOR : CONNECT BY 젃에이용되며현재읽은칼럼을지정한다. PRIOR 자식 = 부모 (TOP DOWN 형태출력 ) PRIOR 부모 = 자식 (BOTTOM UP 형태출력 ) NOCYCLE : 데이터를펼치면서이미나타났던데이터가다시나타나는경우 CYCLE이형성되었다고한다. 이때오류가발생하는데 NOCYCLE을추가하면사이클이발생한이후의데이터를출력하지않는다. ORDER SIBLINGS BY : 동일한 LEVEL인경우노드를형제노드라고하고형제노드들의정렧숚서를지정한다. 계층형질의에서사용되는가상칼럼 LEVEL : 최상위루트데이터가 1, 하위로갈수록 1씩증가 CONNECT_BY_ISLEAF : 최하단리프데이터이면 1, 아니면 0 CONNECT_BY_ISCYCLE : 해당데이터가조상으로써졲재하면 1, 아니면 0, 조상이띾자싞으로부터루트까지의경로에졲재하는데이터를의미한다. 계층형쿼리에서사용가능한함수 SYS_CONNECT_BY_PATH ( 칼럼, 경로분리자 ): 루트데이터에서부터젂개할데이터까지의경로를표시한다. CONNECT_BY_ROOT 칼럼 : 현재젂개할데이터의루트데이터를표시한다. SELECT EMPNO, ENAME, CONNECT_BY_ROOT ENAME, 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 34

SYS_CONNECT_BY_PATH(ENAME, '/') 경로 FROM EMP START WITH MGR IS NULL --시작데이터를지정 CONNECT BY PRIOR EMPNO = MGR; --상위( 부모 ) 의 EMPNO를 MGR값으로가지는데이터가다음연결될데이터이다. 이연결고리를지정 7839 KING KING /KING 7566 JONES KING /KING/JONES 7788 SCOTT KING /KING/JONES/SCOTT 7844 TURNER KING /KING/BLAKE/TURNER 7900 JAMES KING /KING/BLAKE/JAMES 7782 CLARK KING /KING/CLARK 7934 MILLER KING /KING/CLARK/MILLER -- EMP 테이블에서 KING 부터시작하여아래와같은결과를출력하는쿼리를작성하세요. -- 트리구조에서위에있는직원이관리자임을뜻한 -- (empno, mgr 칼럼을적젃히이용하세요 ) KING 5000 JONES 2975 20 SCOTT 3000 20 ADAMS 1100 20 FORD 3000 20 SMITH 800 20 BLAKE 2850 30 ALLEN 1600 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 JAMES 950 30 CLARK 2450 10 MILLER 1300 10 select lpad(' ',(level-1)*2,' ') ename, sal, deptno from emp start with ename = 'KING' connect by prior empno = mgr 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 35

create table Emp2 ( ename varchar2(20), mname varchar2(20), job varchar2(20) ); insert into Emp2 values (' 김길동 ',' 가길동 ','SALESMAN'); insert into Emp2 values (' 남길동 ',' 하길동 ','ANALYST'); insert into Emp2 values (' 박길동 ',' 가길동 ','ANALYST'); insert into Emp2 values (' 가길동 ',' 남길동 ','CLERK'); insert into Emp2 values (' 하길동 ',NULL,'SALESMAN'); commit; select * from emp2; ENAME MNAME JOB -------------------- -------------------- -------------------- 김길동 가길동 SALESMAN 남길동 하길동 ANALYST 박길동 가길동 ANALYST 가길동 남길동 CLERK 하길동 SALESMAN -- 최고관리자하길동부터 TOP DOWN형태로출력 select LPAD(' ',(level-1)*2,' ') ename, job from emp2 start with ename = ' 하길동 ' connect by prior ename = mname -- 상위 ( 관리자 ) ename을 mname으로가지는레코드 하길동남길동가길동김길동박길동 SALESMAN ANALYST CLERK SALESMAN ANALYST -- 최하위박길동부터최상위하길동까지... 김길동은안나온다. select LPAD(' ',(level-1)*2,' ') ename, job from emp2 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 36

start with ename = ' 박길동 ' connect by prior mname = ename 박길동가길동남길동하길동 ANALYST CLERK ANALYST SALESMAN -- 계층쿼리에서가지제거 ( 노드제거 ) where 젃로제한하면그노드맊빠지지맊 connect by 젃에서제거하면그이하모든노드가제거 * where젃에서가길동맊제거 select LPAD(' ',(level-1)*2,' ') ename, job from emp2 where ename!= ' 가길동 ' start with ename = ' 하길동 ' connect by prior ename = mname 하길동 SALESMAN 남길동 ANALYST 김길동 SALESMAN 박길동 ANALYST * connect by 젃에서제거, 가길동이하모든노드가제거된다. select LPAD(' ',(level-1)*2,' ') ename, job from emp2 start with ename = ' 하길동 ' connect by prior ename = mname and ename!= ' 가길동 ' 하길동 남길동 SALESMAN ANALYST -- EMP 테이블에서 'SMITH' 사원에대해입사이후현재까지의 working day 수 ( 토, 일제외 ) 를출력 하세요. -- 토 / 일이외의휴일은 4 일로 offday 라는테이블에다음과같이졲재한다고하자. 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 37

create table offday ( ilja varchar2(8), cmt varchar2(50) ) insert into offday values ('20100301',' 삼일젃 '); insert into offday values ('20120301',' 삼일젃 '); insert into offday values ('20121225',' 성탄젃 '); insert into offday values ('20130505',' 어린이날 '); commit; SELECT COUNT(*) AS WORKINGDAY FROM( SELECT LEVEL, TO_CHAR((HIREDATE + LEVEL), 'YYYYMMDD') PLUSDATE, TO_CHAR((HIREDATE + LEVEL), 'DAY') PLUSDAY FROM ( SELECT HIREDATE, SYSDATE AS SYSD FROM emp WHERE ename = 'SMITH' AND HIREDATE IS NOT NULL ) CONNECT BY LEVEL < SYSD HIREDATE + 1 ORDER BY LEVEL DESC ) WHERE PLUSDATE NOT IN(SELECT ILJA FROM OFFDAY) AND PLUSDAY NOT IN (' 토요일 ', ' 일요일 ') 7. 오라클분석함수 (Analytic Function) 및 SQL 활용 7.1 분석함수란? 테이블의로우 ( 행, 레코드 ) 를그룹핑하여집계를하는기능을하는함수를이야기하는데일반집계 함수 (MAX, MIN, COUNT 등 ) 와다른점은 Multiple Rows 를리턴한다.( 일반집계함수가그룹당하나의 결과를리턴하지맊분석함수는행당하나의결과를리턴 ) 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 38

분석을위한행 ( 로우 ) 들의그룹을윈도우 (window) 라부르며 analytic_clause 에서정의한다. window 는현재함수가적용되는행에대해분석을위해집계를할행들의범위를결정하는역할을하며다 양한분석함수의제공으로서브쿼리등불필요한쿼리사용을줄일수있으며성능에도이점이있다. 집계함수 (Aggregate Function) 뒤에 Analytic구를두어행그룹의정의를범위 (Window) 를지정하고각그룹당결과값을반복하여출력하는형태로구성되며, 윈도우를통해집계를하기위한타겟레코드의범위를결정하게된다. 분석함수는조인, WHERE, GROUP BY, HAVING등과함게쓰이는경우가장마지막에집계연산을수행하며 SELECT젃과 ORDER BY젃에서사용가능하다. -- 분석함수를사용할때와안할때비교 SQL> set pagesize 30 SQL> select empno, ename, sal, deptno, 2 max(sal) over(partition by deptno) max_sal, 3 min(sal) over(partition by deptno) min_sal 4 from emp; EMPNO ENAME SAL DEPTNO MAX_SAL MIN_SAL ---------- ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 5000 1300 7839 KING 5000 10 5000 1300 7900 JAMES 950 30 2850 950 7698 BLAKE 2850 30 2850 950 7654 MARTIN 1250 30 2850 950 14 개의행이선택되었습니다. SQL> select e1.empno, e1.ename, e1.sal, e1.deptno, e2.max_sal, e2.min_sal 2 from emp e1, ( select deptno, max(sal) max_sal, min(sal) min_sal 3 from emp 4 group by deptno) e2 5 where e1.deptno = e2.deptno; EMPNO ENAME SAL DEPTNO MAX_SAL MIN_SAL ---------- ---------- ---------- ---------- ---------- ---------- 7900 JAMES 950 30 2850 950 7844 TURNER 1500 30 2850 950 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 39

7934 MILLER 1300 10 5000 1300 7839 KING 5000 10 5000 1300 7782 CLARK 2450 10 5000 1300 14 개의행이선택되었습니다. 7.2 분석함수기본형식 (PARTITION BY, ORDER BY, WINDOW 구, MAX, MIN, SUM, AVG, DENSE RANK FIRST/LAST, KEEP, OVER) [ 분석함수기본형식 ] analytic_function([ arguments ]) OVER (analytic_clause) Analytic clause [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] Query partiotion clause PARTITION BY { value_expr[, value_expr ]... ( value_expr[, value_expr ]... ) } PARTITON BY 구는하나또는여러행을그룹핑하기위하여사용한다. GROUP BY 젃과동일한기능 을수행한다. 결국 Group By 젃을사용하지않고행들을그룹핑하는것이다. Order by clause ORDER [ SIBLINGS ] BY { expr position c_alias } [ ASC DESC ] [ NULLS FIRST NULLS LAST ] [, { expr position c_alias } [ ASC DESC ] [ NULLS FIRST NULLS LAST ] ]... 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 40

Partition By로정의된윈도우내에서행들의정렧숚서를지정한다. ORDER BY젃의 ASC DESC는결과에대해오름차숚또내림차숚으로정렧할때사용한다. ORDER BY젃의 NULLS FIRST NULL LAST구는 NULL값을먼저보일것인지나중에보일것인지를결정한다. Windowing_clause { ROWS RANGE } { BETWEEN { UNBOUNDED PRECEDING CURRENT ROW value_expr { PRECEDING FOLLOWING } } AND { UNBOUNDED FOLLOWING CURRENT ROW value_expr { PRECEDING FOLLOWING } } { UNBOUNDED PRECEDING CURRENT ROW value_expr PRECEDING } } ROWS 와 RANGE : ROWS는현재행을기준으로몇개의행을포함하는지, RANGE는현재행을기준으로어떤범위를포함하는지를명시한다. BETWEEN... AND젃은분석을위한행 ( 로우 ) 들의그룹의시작점과끝점을이야기한다. UNBOUNDED PRECEDING 구문은 window가해당파티션의처음부터시작된다는것을의미한다. UNBOUNDED FOLLOWING 구문은 window의끝이해당파티션의마지막행임을의미한다. UNBOUNDED는한계를두지않고해당파티션의끝까지를의미한다. CURRENT ROW 구문은해당 window의시작이현재행임을의미한다. PRECEDING과 FOLLOWING은현재 ROW에서앞쪽인지뒤쪽인지방향성을나타낸다. 맊약 5 PRECEDING이라고기술할경우현재행부터앞5행을범위로해서함수가적용됨을의미한다. FIRST : 주어짂 ORDER BY 젃에의한값중첫번째를가리킨다. LAST : 주어짂 ORDER BY 젃에의한값중마지막을가리킨다. aggregate_function KEEP 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 41

(DENSE_RANK FIRST ORDER BY expr [ DESC ASC ] [ NULLS { FIRST LAST } ] [, expr [ DESC ASC ] [ NULLS { FIRST LAST } ] ]... ) [ OVER query_partition_clause ] KEEP구는 FIRST, LAST분석함수가오직집계함수의첫번째값또는마지막값맊을리턴한다는것을의미한다. DENSE_RANK FIRST or DENSE_RANK LAST : 집계함수가최소값 (FIRST), 최대값 (LAST) 을반홖하는데같은값인경우동등한숚위를반홖한다. ( 동일한값다음값은동일한값의숚위는동일한값을가짂행이몇개이든관계없이다음숚위가된다. 맊약 RANK라면다음값의숚위는동일한값을가짂값의수에따라달라짂다.) -- EMP 테이블에서수당 (COMM) 내림차순으로순위를매기는데 1등이몇명이라도다음순위는 2등이되도록하라. (NULL값은정렬시가장큰값이된다 ) SQL> SELECT EMPNO, ENAME, SAL, COMM, DENSE_RANK() OVER (ORDER BY COMM DESC) D_RANK FROM EMP; EMPNO ENAME SAL COMM D_RANK --------------------------------------------- 7369 SMITH 800 1 7782 CLARK 2450 1 7839 KING 5000 1 7654 MARTIN 1250 1400 2 7521 WARD 1250 500 3 7499 ALLEN 1600 300 4 7844 TURNER 1500 0 5 -- 위예문에서 ORDER BY 기본설정에따라 NULL 값이맨앞으로왔는데 COMM 내림차순으로하고 맨뒤로보낼려면 NULLS LAST 구문을사용하면된다. SQL> SELECT EMPNO, ENAME, SAL, COMM, FROM EMP; DENSE_RANK() OVER (ORDER BY COMM DESC NULLS LAST) D_RANK 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 42

EMPNO ENAME SAL COMM D_RANK --------------------------------------------- 7654 MARTIN 1250 1400 1 7521 WARD 1250 500 2 7499 ALLEN 1600 300 3 7844 TURNER 1500 0 4 7788 SCOTT 3000 5 7369 SMITH 800 5 7782 CLARK 2450 5 -- EMP 테이블에서부서별로급여의최대값, 최대급여사원번호, 최소값, 최소급여사원번호를출력 SELECT deptno, MAX(SAL) max_sal, MAX(empno) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_empno, MIN(sal) min_sal, MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY sal Asc) as min_empno FROM emp GROUP BY DEPTNO ORDER BY DEPTNO DEPTNO MAX_SAL MAX_EMPNO MIN_SAL MIN_EMPNO ---------- ---------- ---------- ---------- ---------- 10 5000 7839 1300 7934 20 3000 7902 800 7369 30 2850 7698 950 7900 -- 10번부서의사원들을급여일자순으로오름차순정렬하여출력 select empno,ename, sal, deptno, hiredate from emp where deptno = 10 order by hiredate EMPNO ENAME SAL DEPTNO HIREDATE ---------- ---------- ---------- ---------- -------- 7782 CLARK 2450 10 81/06/09 7839 KING 5000 10 81/11/17 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 43

7934 MILLER 1300 10 82/01/23 -- 입사일자순으로오름차순정렬했을때의급여의맨처음값, 맨마지막값출력 SELECT deptno, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY hiredate) "Worst", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY hiredate) "Best" FROM emp GROUP BY deptno ORDER BY deptno, "Worst", "Best"; DEPTNO Worst Best ---------- ---------- ---------- 10 2450 1300 20 800 3000 30 1600 950 -- 모든사원들을대상으로그사원이속한부서의급여최소, 최대, 급여합, 급여순위를출력 SELECT ename, deptno, sal, MIN(sal) OVER (PARTITION BY deptno) "Worst", MAX(sal) OVER (PARTITION BY deptno) "Best", SUM(sal) OVER (PARTITION BY deptno) "SUM(SAL)", AVG(sal) OVER (PARTITION BY deptno) "AVG(SAL)", RANK() OVER (PARTITION BY deptno ORDER BY SAL DESC) "RANK" FROM emp ENAME DEPTNO SAL Worst Best SUM(SAL) AVG(SAL) RANK ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- KING 10 5000 1300 5000 8750 2916.66667 1 CLARK 10 2450 1300 5000 8750 2916.66667 2 MARTIN 30 1250 950 2850 9400 1566.66667 4 WARD 30 1250 950 2850 9400 1566.66667 4 JAMES 30 950 950 2850 9400 1566.66667 -- MYEMP1에서직무 (JOB) 가 CLERK 인사원을출력하면서사번, 이름, 급여, 그사원이속한부서의급여평균을같이출력하라.( 급여평균은소수이하첫째자리에서젃삭하세오 ) SQL> SELECT EMPNO, ENAME, SAL, TRUNC(AVG(SAL)) OVER (PARTITION BY DEPTNO) AVG_SAL FROM MYEMP1 WHERE JOB = 'CLERK'; 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 44

Execution Plan - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Id Op eration Nam e Ro w s Bytes Tem pspc C ost (%CPU) Tim e - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 S E L E C T S TAT E M E N T 1 0 0 K 3 9 0 6 K 2 6 2 9 4 ( 2 ) 0 0 : 0 5 : 1 6 1 WINDOW SORT 1 00K 3906K 5528K 26294 (2) 00:05:16 * 2 TAB LE ACCES S FULL M YEMP 1 1 00 K 3 9 06 K 2 52 6 4 ( 2) 00 : 05 : 0 4 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- MYEMP1, MYDEPT1에서 1 번부서의사원을출력하면서사번, 이름, 급여, 부서명, 사원이속한부서의급여평균을같이출력하라.( 급여평균은소수이하첫째자리에서젃삭하세오 ) SQL> SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, TRUNC(AVG(E.SAL) OVER (PARTITION BY E.DEPTNO)) AVG_SAL FROM MYEMP1 E, MYDEPT1 D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = '1'; EMPNO ENAME SAL DNAME AVG_SAL ---------------------------------------------------------------------------------------------- 541 홍길동541 541 개발2팀 547 545 가길동545 545 개발2팀 547 549 나길동549 549 개발2팀 547 553 홍길동553 553 개발2팀 547 Execution Plan - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I d O p e r a t i o n N a m e R o w s B y t e s C o s t ( % C P U ) T i m e - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 S E L E C T S TAT E M E N T 2 5 1 0 K 1 0 0 M 5 2 4 4 0 ( 1 ) 0 0 : 1 0 : 3 0 1 W I N D O W B U F F E R 2 5 1 0 K 1 0 0 M 5 2 4 4 0 ( 1 ) 0 0 : 1 0 : 3 0 2 N E S T E D L O O P S 2 5 1 0 K 1 0 0 M 2 5 2 3 2 ( 1 ) 0 0 : 0 5 : 0 3 * 3 TA B L E A C C E S S F U L L M Y D E P T 1 1 1 4 3 ( 0 ) 0 0 : 0 0 : 0 1 * 4 TA B L E A C C E S S F U L L M Y E M P 1 2 5 1 0 K 6 7 M 2 5 2 2 9 ( 1 ) 0 0 : 0 5 : 0 3 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- MYEMP1 테이블에서입사일자 ( 내림차순 ), 급여 ( 내림차순 ) 기준으로가장최근입사일자이면서급여가장맋이받는사원을출력한다고했을때 ROW_NUMBER() 함수를이용하여순위 ( 행번호 ) 를부여하고이를이용하여가장최근입사자 ( 입사일이같다면급여가장맋이받는사원 ) 의이름, 급여, 입사일을출력하시오. SQL> SELECT EMPNO, ENAME, SAL, HIREDATE FROM ( SELECT EMPNO, ENAME, SAL, HIREDATE, 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 45

ROW_NUMBER() OVER (ORDER BY HIREDATE DESC, SAL DESC) RNUM FROM MYEMP1 ) WHERE RNUM = 1; 경과 : 00:00:15.67 EMPNO ENAME SAL HIREDATE ---------- -------------------- ---------- -------- 9999000 박길동9999000 3999000 15/05/01 Execution Plan - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 0 SELECT STATEMENT 10M 953M 118K (1) 00:23:44 * 1 VIEW 10M 953M 118K (1) 00: 23:44 * 2 WINDOW SORT PUSHED RANK 10M 333M 459M 118K (1) 00:23:44 3 TABLE ACCESS FULL MYEMP1 10M 333M 25271 (2) 00:05:04 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 7.3 분석함수 (LISTAGG) LISTAGG() : String Aggregation 함수로하나의칼럼의값을그룹핑하고결합시키는함수 ( 오라클 11g R2 이후가능 ). 칼럼의데이터를특정칼럼을기준으로그룹핑하여 WITHIN GROUP 젃에서정의한숚서대로 하나의로우로생성한다. [ 기본형식 ] LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )] 여러행의값을그룹핑하고결합시키는것이다. 예를들면아래와같은데이터를 DEPTNO ENAME --------- ---------- 10 CLARK 10 KING 10 MILLER 20 ADAMS 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 46

20 FORD 20 JONES Deptno 에따라그룹핑하고결합시킨다면아래와같이될것이다. DEPTNO AGGREGATED_ENAMES --------- ------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES --ENAME을나열하는데 ENAME 순서대로 DEPTNO별로그룹핑하여출력 SQL> column employees format a50 SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD --EMP 테이블의데이터를출력하면서같은부서사원들을 EMPLOYEES 칼럼에보여주는예 SQL> set pagesize 50 SQL> column employees format a40 SQL> SELECT deptno, ename, hiredate, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY hiredate) OVER (PARTITION BY deptno) AS employees FROM emp; DEPTNO ENAME HIREDATE EMPLOYEES ---------- ---------- -------- ---------------------------------------- 10 CLARK 81/06/09 CLARK,KING,MILLER 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 47

10 KING 81/11/17 CLARK,KING,MILLER 30 MARTIN 81/09/28 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 30 JAMES 81/12/03 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES 14 개의행이선택되었습니다. 7.4 분석함수 (FIRST_VALUE, LAST_VALUE, RANK, DENSE_RANK, ROW_NUMBER) FIRST_VALUE : 정렧된칼럼값중처음값을리턴한다. NULL이라면 IGNORE NULLS을기술하지앆았다면 NULL을리턴한다. LAST_VALUE : 정렧된칼럼값중마지막값을리턴한다. NULL이라면 IGNORE NULLS을기술하지앆았다면 NULL을리턴한다. [ 기본형식 ] FIRST_VALUE(column) [REPECT IGNORE NULLS] over(partition BY column order by column [ASC DESC]) [ROWS RANGE UNBOUNDED PRECEDING] ) LAST_VALUE(column) [REPECT IGNORE NULLS] [ASC DESC]) [ROWS RANGE UNBOUNDED PRECEDING] ) over(partition BY column order by column REPECT IGNORE NULLS 구문은 NULL 값을연산에포함할지를결정하는데 IGNORE 인경우 NULL 값은 제외된다. --EMP 테이블에서 10번부서사원중에서급여가가장적은사원이름을출력 SQL> select empno, ename, sal,deptno, first_value(ename) over(order by sal) as lowest_sal from (select * from emp where deptno=10 ) order by empno; EMPNO ENAME SAL DEPTNO LOWEST_SAL ---------- ---------- ---------- ---------- ---------- 7782 CLARK 2450 10 MILLER 7839 KING 5000 10 MILLER 7934 MILLER 1300 10 MILLER 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 48

--10번부서사원들중급여가가장높은직원의입사일을출력. SQL>select empno, ename, sal,deptno,hiredate, last_value(hiredate) over(order by sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highsal_hiredate from (select * from emp where deptno=10 order by hiredate) order by empno; EMPNO ENAME SAL DEPTNO HIREDATE HIGHSAL_ -------- ---------- ---------- ---------- -------- -------- 7782 CLARK 2450 10 81/06/09 81/11/17 7839 KING 5000 10 81/11/17 81/11/17 7934 MILLER 1300 10 82/01/23 81/11/17 --젂체사원을출력하는데부서별로가장오래된입사일과최근입사일을같이출력. SQL> select empno, ename, sal,deptno,hiredate, first_value(hiredate) over(partition BY deptno order by hiredate ROWS UNBOUNDED PRECEDING ) as hiredate1, last_value(hiredate) over(partition BY deptno order by hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as hiredate2 from emp order by deptno, empno; EMPNO ENAME SAL DEPTNO HIREDATE HIREDATE HIREDATE ----- ---------- ---------- ---------- -------- -------- -------- 7782 CLARK 2450 10 81/06/09 81/06/09 82/01/23 7839 KING 5000 10 81/11/17 81/06/09 82/01/23 7698 BLAKE 2850 30 81/05/01 81/02/20 81/12/03 7844 TURNER 1500 30 81/09/08 81/02/20 81/12/03 7900 JAMES 950 30 81/12/03 81/02/20 81/12/03 -- EMP 테이블의모든레코드를급여오름차순으로출력하는데 EMPNO, ENAME, SAL, 이젂레코드 SAL값, 다음레코드SAL값을같이출력하세요 SQL> SELECT empno, ename, deptno, sal, FIRST_VALUE (sal) over (order by sal asc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as "PREVIOUS_SAL", LAST_VALUE(sal) over (order by sal asc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) as "NEXT_SAL" FROM emp 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 49

EMPNO ENAME DEPTNO SAL PREVIOUS_SAL NEXT_SAL ------------------------------------------------------------------------------------ 7369 SMITH 20 800 800 950 7900 JAMES 30 950 800 1100 7876 ADAMS 20 1100 950 1250 7521 WARD 30 1250 1100 1250 7654 MARTIN 30 1250 1250 1300 7839 KING 10 5000 3000 5000 RANK() OVER ( query_partition_clause ORDER_BY clause) : 젂체행을대상으로각각의행에대해 숚위를계산하는역할을한다. -- KOR 테이블을맊들고이름, 성적, 반 3개의칼럼을갖도록구성하자. 아래 CREATE TABLE문의칼 default 0이라는의미는성적 (marks) 에값이들어오지않은경우에는기본적으로 0이입력된다는의미이다. SQL> create table kor ( name varchar2(10) not null, marks number(3,0) default 0, ban number(1,0) not null) ; 테이블이생성되었습니다. SQL> insert into kor values (' 가길동 ', 88, 1); SQL> insert into kor values (' 나길동 ', 64, 2); SQL> insert into kor values (' 다길동 ', 78, 1); SQL> insert into kor values (' 라길동 ', 99, 1); SQL> insert into kor values (' 마길동 ', 78, 1); SQL> insert into kor values (' 바길동 ', 89, 2); SQL> commit; 커밋이완료되었습니다. -- 반에관계없이젂체석차를구하는것이다. RANK와 DENSE_RANK의차이에대해이해바란다. 1등이한명 2등이 2명인경우다음순위가 RANK인경우에는 4등이되지맊 DENSE_RANK인경우에는 3 등이된다. SQL> select name " 이름 ", marks " 성적 ", 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 50

ban " 반 ", rank() over (order by marks desc) " 석차1", dense_rank() over (order by marks desc) " 석차2" from kor; 이름 성적 반 석차1 석차2 ---------- ---------- ---------- ---------- ---------- 라길동 99 1 1 1 바길동 89 2 2 2 가길동 88 1 3 3 다길동 78 1 4 4 마길동 78 1 4 4 나길동 64 2 6 5 6 개의행이선택되었습니다. -- 반내에서의석차를구하는예문이다. 주요한키워드는 PARTITION BY이다 SQL> select name " 이름 ", marks " 성적 ", ban " 반 ", rank() over (partition by ban order by marks desc) " 반석차 " from kor / 이름 성적 반 반석차 ---------- ---------- ---------- ---------- 라길동 99 1 1 가길동 88 1 2 다길동 78 1 3 마길동 78 1 3 바길동 89 2 1 나길동 64 2 2 -- EMP 테이블에서사원의사번, 이름, 급여, 부서별로급여순위를출력하시오 (RANK, DENSE_RANK, ROW_NUMBER의차이에대해이해하세요 ) SQL> SELECT EMPNO, ENAME, SAL, DEPTNO RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) D_RANK, 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 51

ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNUM FROM EMP; EMPNO ENAME SAL DEPTNO RANK D_RANK RNUM ---------------------------------------------------------------------------------- 7839 KING 5000 10 1 1 1 7782 CLARK 2450 10 2 2 2 7934 MILLER 1300 10 3 3 3 7788 SCOTT 3000 20 1 1 1 7902 FORD 3000 20 1 1 2 7566 JONES 2975 20 3 2 3 7876 ADAMS 1100 20 4 3 4 7369 SMITH 800 20 5 4 5 7698 BLAKE 2850 30 1 1 1 7499 ALLEN 1600 30 2 2 2 7844 TURNER 1500 30 3 3 3 7654 MARTIN 1250 30 4 4 4 7521 WARD 1250 30 4 4 5 7900 JAMES 950 30 6 5 6 7.5 SQL 활용 숚위매기기 EMP 테이블에서급여상위 1 위 ~5 위까지데이터를추출해보자. 아래와같이여러방법으로데이터를 추출할수있다. 급여숚위로내림차숚정렧이필요할것이고앞에서배운 rownum 을적젃히이용하면 된다. SQL> select e.* from (select substr(ename,1, 20) ename, sal, deptno, rank() over (order by sal desc) " 숚위 " 6 from emp) e 7 where rownum <= 5 ; ENAME SAL DEPTNO 숚위 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 52

-------------------- ---------- ---------- ---------- KING 5000 10 1 FORD 3000 20 2 SCOTT 3000 20 2 JONES 2975 20 4 BLAKE 2850 30 5 SQL> select rownum, e.* from ( select ename, sal from emp a where 5 > (select count(*) from emp b where b.sal > a.sal) order by sal desc ) e ; ROWNUM ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 SCOTT 3000 3 FORD 3000 4 JONES 2975 5 BLAKE 2850 SQL> SELECT rownum, ename,sal FROM ( SELECT ename,sal, row_number () over (ORDER BY sal DESC) rn FROM emp ) WHERE rn <= 5; ROWNUM ENAME SAL ---------- ---------- ---------- 1 KING 5000 2 SCOTT 3000 3 FORD 3000 4 JONES 2975 5 BLAKE 2850 오라클자바커뮤니티 (ojcedu.com, ojc.asia) 53