GROUPING SETS 등의새로운리포팅기능 - WINDOW FUNCTION 같은새로운개념의분석기능들 가. 일반집합연산자 현재사용하는 SQL 의많은기능이관계형데이터베이스의이론을수립한 E.F.Codd 박사의논문에언급이되어있다. 논문에언급된 8 가지관계형대수는다시각각 4

Transcription

1 01. 표준조인 1. STANDARD SQL 개요 1970 년 : Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문발표 1974 년 : IBM SQL 개발 1979 년 : Oracle 상용 DBMS 발표 1980 년 : Sybase SQL Server 발표 ( 이후 Sybase ASE 로개명 ) 1983 년 : IBM DB2 발표 1986 년 : ANSI/ISO SQL 표준최초제정 (SQL-86, SQL1) 1992 년 : ANSI/ISO SQL 표준개정 (SQL-92, SQL2) 1993 년 : MS SQL Server 발표 (Windows OS, Sybase Code 활용 ) 1999 년 : ANSI/ISO SQL 표준개정 (SQL-99, SQL3) 2003 년 : ANSI/ISO SQL 표준개정 (SQL- 2003) 2008 년 : ANSI/ISO SQL 표준개정 (SQL-2008) 국내뿐만아니라전세계적으로많이사용되고있는관계형데이터베이스의경우오브젝트개념을포함한여러새로운기능들이꾸준히개발되고있으며, 현재기업형 DBMS 는순수관계형데이터베이스가아닌객체지원기능이포함된객체관계형 (Object Relational) 데이터베이스를대부분사용하고있다. 현재우리가사용하는많은시스템의두뇌역할을하는관계형데이터베이스를유일하게접속할수있는언어가바로 SQL 이다. 사용자와개발자입장에서는 SQL 의진화및변화가가장큰관심내용인데, 초창기 SQL 의기본기능을정리했던최초의 SQL-86 표준과관계형 DBMS 의폭발적인전성기를주도했던 ANSI/ISO SQL2 세대를지나면서많은기술적인발전이있었다. 그러나, ANSI/ISO SQL2 의경우표준 SQL 에대한명세가부족한부분이있었고, DBMS 벤더별로문법이나사용되는용어의차이가너무커져서상호호환성이나 SQL 학습효율이많이부족한문제가발생하였다. 이에향후 SQL 에서필요한기능을정리하고호환가능한여러기준을제정한것이 1999 년에정해진 ANSI/ISO SQL3 이다. 이후가장먼저 ANSI/ISO SQL3 의기능을시현한것이 Oracle 의 8i/9i 버전이라고할수있다. 참고로 2003 년에 ANSI/ISO SQL 기준이소폭추가개정되었고현재사용되는데이터베이스는대부분 SQL-2003 표준을기준으로하고있다. 다른벤더의 DBMS 도 2006 년이후발표된버전에서 ANSI/ISO SQL-99 와 SQL-2003 의핵심적인기능은만족스러운수준으로구현된것으로평가받고있다. 마지막으로 2008 년에진행된추가개정내용은아직사용자레벨에큰영향을미치지않고있다. 아직도벤더별로일부기능의개발이진행중인경우도있고벤더별특이한기술용어는여전히호환이안되고있지만, ANSI/ISO SQL 표준을통해 STANDARD JOIN 을포함한많은기능이상호벤치마킹하고발전하면서 DBMS 간에평준화를이루어가고있다고볼수있다. 예를들면, IBM DB2 나 SYBASE ASE DBMS 는과거버전부터 CASE 기능이나 FULL OUTER JOIN 기능을지원하였지만, Oracle DBMS 는양쪽 (FULL) OUTER JOIN 의경우 (+) 표시를이용한두개의 SQL 문장을 UNION 오퍼레이션으로처리하거나, CASE 기능을구현하기위해 DECODE 함수를복잡하게구현해야하는불편함이있었다. 이런불편사항은 Oracle 에서표준 SQL 에포함된 CASE 기능과 FULL OUTER JOIN 기능을추가함으로써문제가해결되었다.( 참고로, Oracle DECODE 함수가 CASE 기능보다장점도있으므로 Oracle 사용자는요구사항에따라 DECODE 나 CASE 함수를선택할수있다.) 결과적으로사용자입장에서는 ANSI/ISO SQL 의새로운기능들을사용함으로써보다쉽게데이터를추출하거나 SQL 튜닝의효과를함께얻을수있게되었다. 대표적인 ANSI/ISO 표준 SQL 의기능은다음내용을포함한다. - STANDARD JOIN 기능추가 (CROSS, OUTER JOIN 등새로운 FROM 절 JOIN 기능들 ) - SCALAR SUBQUERY, TOP-N QUERY 등의새로운 SUBQUERY 기능들 - ROLLUP, CUBE,

2 GROUPING SETS 등의새로운리포팅기능 - WINDOW FUNCTION 같은새로운개념의분석기능들 가. 일반집합연산자 현재사용하는 SQL 의많은기능이관계형데이터베이스의이론을수립한 E.F.Codd 박사의논문에언급이되어있다. 논문에언급된 8 가지관계형대수는다시각각 4 개의일반집합연산자와순수관계연산자로나눌수있으며, 관계형데이터베이스엔진및 SQL 의기반이론이되었다. 일반집합연산자를현재의 SQL 과비교하면, 1. UNION 연산은 UNION 기능으로, 2. INTERSECTION 연산은 INTERSECT 기능으로, 3. DIFFERENCE 연산은 EXCEPT(Oracle 은 MINUS) 기능으로, 4. PRODUCT 연산은 CROSS JOIN 기능으로구현되었다. 첫번째, UNION 연산은수학적합집합을제공하기위해, 공통교집합의중복을없애기위한사전작업으로시스템에부하를주는정렬작업이발생한다. 이후 UNION ALL 기능이추가되었는데, 특별한요구사항이없다면공통집합을중복해서그대로보여주기때문에정렬작업이일어나지않는장점을가진다. 만일 UNION 과 UNION ALL 의출력결과가같다면, 응답속도향상이나자원효율화측면에서데이터정렬작업이발생하지않는 UNION ALL 을사용하는것을권고한다. 두번째, INTERSECTION 은수학의교집합으로써두집합의공통집합을추출한다. 세번째, DIFFERENCE 는수학의차집합으로써첫번째집합에서두번째집합과의공통집합을제외한부분이다. 대다수벤더는 EXCEPT 를, Oracle 은 MINUS 용어를사용한다. (SQL 표준에는 EXCEPT 로표시되어있으며, 벤더에서 SQL 표준기능을구현할때다른용어를사용하는것은현실적으로허용되고있다.) 네번째, PRODUCT 의경우는 CROSS(ANIS/ISO 표준 ) PRODUCT 라고불리는곱집합으로, JOIN 조건이없는경우생길수있는모든데이터의조합을말한다. 양쪽집합의 M*N 건의데이터조합이발생하며, CARTESIAN( 수학자이름 ) PRODUCT 라고도표현한다. 나. 순수관계연산자

3 순수관계연산자는관계형데이터베이스를구현하기위해새롭게만들어진연산자이다. 순수관계연산자를현재의 SQL 문장과비교하면다음과같다. 5. SELECT 연산은 WHERE 절로구현되었다. 6. PROJECT 연산은 SELECT 절로구현되었다. 7. (NATURAL) JOIN 연산은다양한 JOIN 기능으로구현되었다. 8. DIVIDE 연산은현재사용되지않는다. 다섯번째, SELECT 연산은 SQL 문장에서는 WHERE 절의조건절기능으로구현이되었다. (SELECT 연산과 SELECT 절의의미가다름을유의하자.) 여섯번째, PROJECT 연산은 SQL 문장에서는 SELECT 절의칼럼선택기능으로구현이되었다. 일곱번째, JOIN 연산은 WHERE 절의 INNER JOIN 조건과함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절등으로가장다양하게발전하였다. 여덟번째, DIVIDE 연산은나눗셈과비슷한개념으로왼쪽의집합을 XZ 로나누었을때, 즉 XZ 를모두가지고있는 A 가답이되는기능으로현재사용되지않는다. 관계형데이터베이스의경우요구사항분석, 개념적데이터모델링, 논리적데이터모델링, 물리적데이터모델링단계를거치게되는데, 이단계에서엔터티확정및정규화과정, 그리고 M:M ( 다대다 ) 관계를분해하는절차를거치게된다. 특히정규화과정의경우데이터정합성과데이터저장공간의절약을위해엔터티를최대한분리하는작업으로, 일반적으로 3 차정규형이나보이스코드정규형까지진행하게된다. 이런정규화를거치면하나의주제에관련있는엔터티가여러개로나누어지게되고, 이엔터티들이주로테이블이되는데이렇게흩어진데이터를연결해서원하는데이터를가져오는작업이바로 JOIN 이라고할수있다. 관계형데이터베이스에있어서 JOIN 은 SQL 의가장중요한기능이므로충분히이해할필요가있다.

4 2. FROM 절 JOIN 형태 ANSI/ISO SQL 에서표시하는 FROM 절의 JOIN 형태는다음과같다. - INNER JOIN - NATURAL JOIN - USING 조건절 - ON 조건절 - CROSS JOIN - OUTER JOIN ANSI/ISO SQL 에서규정한 JOIN 문법은 WHERE 절을사용하던기존 JOIN 방식과차이가있다. 사용자는기존 WHERE 절의검색조건과테이블간의 JOIN 조건을구분없이사용하던방식을그대로사용할수있으면서, 추가된선택기능으로테이블간의 JOIN 조건을 FROM 절에서명시적으로정의할수있게되었다. INNER JOIN 은 WHERE 절에서부터사용하던 JOIN 의 DEFAULT 옵션으로 JOIN 조건에서동일한값이있는행만반환한다. DEFAULT 옵션이므로생략이가능하지만, CROSS JOIN, OUTER JOIN 과는같이사용할수없다. NATURAL JOIN 은 INNER JOIN 의하위개념으로 NATURAL JOIN 은두테이블간의동일한이름을갖는모든칼럼들에대해 EQUI(=) JOIN 을수행한다. NATURAL INNER JOIN 이라고도표시할수있으며, 결과는 NATURAL JOIN 과같다. 새로운 SQL JOIN 문장중에서가장중요하게기억해야하는문장은 ON 조건절을사용하는경우이다. 과거 WHERE 절에서 JOIN 조건과데이터검증조건이같이사용되어용도가불분명한경우가발생할수있었는데, WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로분리하여표시함으로써사용자가이해하기쉽도록한다. ON 조건절의경우 NATURAL JOIN 처럼 JOIN 조건이숨어있지않고, 명시적으로 JOIN 조건을구분할수있고, NATURAL JOIN 이나 USING 조건절처럼칼럼명이똑같아야된다는제약없이칼럼명이상호다르더라도 JOIN 조건으로사용할수있으므로앞으로가장많이사용될것으로예상된다. 다만, FROM 절에테이블이많이사용될경우다소복잡하게보여가독성이떨어지는단점이있다. 그런측면에서 SQL Server 의경우 ON 조건절만지원하고 NATURAL JOIN 과 USING 조건절을지원하지않고있는것으로보인다. 본가이드는 ANSI/ISO SQL 기준에 NATURAL JOIN 과 USING 조건절이표시되어있으므로이부분도설명을하도록한다. 3. INNER JOIN INNER JOIN 은 OUTER( 외부 ) JOIN 과대비하여내부 JOIN 이라고하며 JOIN 조건에서동일한값이있는행만반환한다. INNER JOIN 표시는그동안 WHERE 절에서사용하던 JOIN 조건을 FROM 절에서정의하겠다는표시이므로 USING 조건절이나 ON 조건절을필수적으로사용해야한다. [ 예제 ] 사원번호와사원이름, 소속부서코드와소속부서이름을찾아본다. [ 예제 ] WHERE 절 JOIN 조건 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; 위 SQL 과아래 SQL 은같은결과를얻을수있다. FROM 절 JOIN 조건 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; INNER 는 JOIN 의디폴트옵션으로아래 SQL 문과같이생략가능하다. SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; [ 실행결과 ] DEPTNO EMPNO ENAME DNAME SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 개의행이선택되었다. 위에서사용한 ON 조건절에대해서는뒤에서추가설명하도록한다. 4. NATURAL JOIN

5 NATURAL JOIN 은두테이블간의동일한이름을갖는모든칼럼들에대해 EQUI(=) JOIN 을수행한다. NATURAL JOIN 이명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을정의할수없다. 그리고, SQL Server 에서는지원하지않는기능이다. [ 예제 ] 사원번호와사원이름, 소속부서코드와소속부서이름을찾아본다. [ 예제 ] SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT; [ 실행결과 ] DEPTNO EMPNO ENAME DNAME SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 개의행이선택되었다. 위 SQL 은별도의 JOIN 칼럼을지정하지않았지만, 두개의테이블에서 DEPTNO 라는공통된칼럼을자동으로인식하여 JOIN 을처리한것이다. JOIN 에사용된칼럼들은같은데이터유형이어야하며, ALIAS 나테이블명과같은접두사를붙일수없다. [ 예제 ] SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT; ERROR: NATURAL JOIN 에사용된열은식별자를가질수없음 NATURAL JOIN 은 JOIN 이되는테이블의데이터성격 ( 도메인 ) 과칼럼명등이동일해야하는제약조건이있다. 간혹모델링상의부주의로인해동일한칼럼명이더라도다른용도의데이터를저장하는경우도있으므로주의해서사용해야한다. [ 예제 ] 아래 '*' 와일드카드처럼별도의칼럼순서를지정하지않으면 NATURAL JOIN 의기준이되는칼럼들이다른칼럼보다먼저출력된다. (ex: DEPTNO 가첫번째칼럼이된다.) 이때 NATURAL JOIN 은 JOIN 에사용된같은이름의칼럼을하나로처리한다. [ 예제 ] SELECT * FROM EMP NATURAL JOIN DEPT; [ 실행결과 ] DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC SMITH CLERK RESEARCH DALLAS ALLEN SALESMAN SALES CHICAGO WARD SALESMAN SALES CHICAGO JONES MANAGER RESEARCH DALLAS MARTIN SALESMAN SALES CHICAGO BLAKE MANAGER SALES CHICAGO CLARK MANAGER ACCOUNTING NEW YORK SCOTT ANALYST RESEARCH DALLAS KING PRESIDENT ACCOUNTING NEW YORK TURNER SALESMAN SALES CHICAGO ADAMS CLERK RESEARCH DALLAS JAMES CLERK SALES CHICAGO FORD ANALYST RESEARCH DALLAS MILLER CLERK ACCOUNTING NEW YORK 14 개의행이선택되었다. [ 예제 ] 반면, INNER JOIN 의경우첫번째테이블, 두번째테이블의칼럼순서대로데이터가출력된다. 이때 NATURAL JOIN 은 JOIN 에사용된같은이름의칼럼을하나로처리하지만, INNER JOIN 은별개의칼럼으로표시한다. [ 예제 ] SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; [ 실행결과 ] EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC SMITH CLERK

6 RESEARCH DALLAS 7499 ALLEN SALESMAN SALES CHICAGO 7521 WARD SALESMAN SALES CHICAGO 7566 JONES MANAGER RESEARCH DALLAS 7654 MARTIN SALESMAN SALES CHICAGO 7698 BLAKE MANAGER SALES CHICAGO 7782 CLARK MANAGER ACCOUNTING NEW YORK 7788 SCOTT ANALYST RESEARCH DALLAS 7839 KING PRESIDENT ACCOUNTING NEW YORK 7844 TURNER SALESMAN SALES CHICAGO 7876 ADAMS CLERK RESEARCH DALLAS 7900 JAMES CLERK SALES CHICAGO 7902 FORD ANALYST RESEARCH DALLAS 7934 MILLER CLERK ACCOUNTING NEW YORK 14 개의행이선택되었다. [ 예제 ] NATURAL JOIN 과 INNER JOIN 의차이를자세히설명하기위해 DEPT_TEMP 테이블을임시로만든다. [ 예제 ] Oracle CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT; [ 예제 ] SQL Server SELECT * INTO DEPT_TEMP FROM DEPT; [ 예제 ] UPDATE DEPT_TEMP SET DNAME = 'R&D' WHERE DNAME = 'RESEARCH'; UPDATE DEPT_TEMP SET DNAME = 'MARKETING' WHERE DNAME = 'SALES'; SELECT * FROM DEPT_TEMP; [ 실행결과 ] DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 R&D DALLAS 30 MARKETING CHICAGO 40 OPERATIONS BOSTON 4 개의행이선택되었다. 부서번호 20 과 30 의 DNAME 이 'R&D' 와 'MARKETING' 으로변경된것을확인할수있다. [ 예제 ] 세개의칼럼명이모두같은 DEPT 와 DEPT_TEMP 테이블을 NATURAL [INNER] JOIN 으로수행한다. [ 예제 ] SELECT * FROM DEPT NATURAL INNER JOIN DEPT_TEMP; INNER 는 DEFAULT 옵션으로아래와같이생략가능하므로위 SQL 과아래 SQL 은같은결과를얻을수있다. SELECT * FROM DEPT NATURAL JOIN DEPT_TEMP; [ 실행결과 ] DEPTNO DNAME LOC ACCOUNTING NEW YORK 40 OPERATIONS BOSTON 2 개의행이선택되었다. 위 SQL 의경우 DNAME 의내용이바뀐부서번호 20, 30 의데이터는실행결과에서제외된것을알수있다. [ 예제 ] 다음에는같은조건이지만출력칼럼에서차이가나는일반적인 INNER JOIN 을수행한다. [ 예제 ] SELECT * FROM DEPT JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO AND DEPT.DNAME = DEPT_TEMP.DNAME AND DEPT.LOC = DEPT_TEMP.LOC; 위 SQL 과아래 SQL 은같은결과를얻을수있다. SELECT * FROM DEPT, DEPT_TEMP WHERE DEPT.DEPTNO = DEPT_TEMP.DEPTNO AND DEPT.DNAME = DEPT_TEMP.DNAME AND DEPT.LOC = DEPT_TEMP.LOC; [ 실행결과 ] DEPTNO DNAME LOC DEPTNO DNAME LOC ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON 40 OPERATIONS BOSTON 2 개의행이선택되었다. 위 SQL 의경우 DNAME 의내용이바뀐부서번호 20, 30 의경우는결과에서제외된것을알수있다. 차이가나는부분은 NATURAL JOIN 은 JOIN 에사용된같은이름의칼럼을하나로처리하지만, INNER JOIN 의경우는 2 개의칼럼으로표시된다.

7 5. USING 조건절 NATURAL JOIN 에서는모든일치되는칼럼들에대해 JOIN 이이루어지지만, FROM 절의 USING 조건절을이용하면같은이름을가진칼럼들중에서원하는칼럼에대해서만선택적으로 EQUI JOIN 을할수가있다. 다만, 이기능은 SQL Server 에서는지원하지않는다. [ 예제 ] 세개의칼럼명이모두같은 DEPT 와 DEPT_TEMP 테이블을 DEPTNO 칼럼을이용한 [INNER] JOIN 의 USING 조건절로수행한다. [ 예제 ] SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); [ 실행결과 ] DEPTNO DNAME LOC DNAME LOC ACCOUNTING NEW YORK ACCOUNTING NEW YORK 20 RESEARCH DALLAS R&D DALLAS 30 SALES CHICAGO MARKETING CHICAGO 40 OPERATIONS BOSTON OPERATIONS BOSTON 4 개의행이선택되었다. 위 SQL 의 '*' 와일드카드처럼별도의칼럼순서를지정하지않으면 USING 조건절의기준이되는칼럼이다른칼럼보다먼저출력된다. (ex: DEPTNO 가첫번째칼럼이된다.) 이때 USING JOIN 은 JOIN 에사용된같은이름의칼럼을하나로처리한다. [ 예제 ] USING 조건절을이용한 EQUI JOIN 에서도 NATURAL JOIN 과마찬가지로 JOIN 칼럼에대해서는 ALIAS 나테이블이름과같은접두사를붙일수없다. (DEPT.DEPTNO DEPTNO) [ 예제 ] 잘못된사례 : SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); ERROR: USING 절의열부분은식별자를가질수없음바른사례 : SELECT DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); [ 실행결과 ] DEPTNO DNAME LOC DNAME LOC ACCOUNTING NEW YORK ACCOUNTING NEW YORK 20 RESEARCH DALLAS R&D DALLAS 30 SALES CHICAGO MARKETING CHICAGO 40 OPERATIONS BOSTON OPERATIONS BOSTON 4 개의행이선택되었다. [ 예제 ] 이번에는 DEPT 와 DEPT_TEMP 테이블의일부데이터내용이변경되었던 DNAME 칼럼을조인조건으로 [INNER] JOIN 의 USING 조건절을수행한다. [ 예제 ] SELECT * FROM DEPT JOIN DEPT_TEMP USING (DNAME); [ 실행결과 ] DNAME DEPTNO LOC DEPTNO LOC ACCOUNTING 10 NEW YORK 10 NEW YORK OPERATIONS 40 BOSTON 40 BOSTON 2 개의행이선택되었다. 위 SQL 의경우 DNAME 의내용이바뀐부서번호 20, 30 의경우는결과에서제외된것을알수있다. 그리고 USING 에사용된 DNAME 이첫번째칼럼으로출력된것과함께, JOIN 조건에참여하지않은 DEPTNO 와 LOC 가 2 개의칼럼으로표시된것을알수있다. [ 예제 ] 이번에는세개의칼럼명이모두같은 DEPT 와 DEPT_TEMP 테이블을 LOC 와 DEPTNO 2 개칼럼을이용한 [INNER] JOIN 의 USING 조건절로수행한다. [ 예제 ] SELECT * FROM DEPT JOIN DEPT_TEMP USING (LOC, DEPTNO); [ 실행결과 ] LOC DEPTNO DNAME DNAME NEW YORK 10 ACCOUNTING ACCOUNTING DALLAS 20 RESEARCH R&D CHICAGO 30 SALES MARKETING BOSTON 40 OPERATIONS OPERATIONS 4 개의행이선택되었다.

8 USING 에사용된 LOC, DEPTNO 가첫번째, 두번째칼럼으로출력되고, JOIN 조건에참여하지않은 DNAME 칼럼은 2 개의칼럼으로표시된것을알수있다. [ 예제 ] 이번에는 DEPTNO, DNAME 2 개의칼럼을이용한 [INNER] JOIN 의 USING 조건절로수행한다. [ 예제 ] SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME); [ 실행결과 ] DEPTNO DNAME LOC LOC ACCOUNTING NEW YORK NEW YORK 40 OPERATIONS BOSTON BOSTON 2 개의행이선택되었다. 위 SQL 의경우 DNAME 의내용이바뀐부서번호 20, 30 의경우는결과에서제외된것을알수있다. 그리고 USING 에사용된 DEPTNO, DNAME 이첫번째, 두번째칼럼으로출력된것과함께, JOIN 조건에참여하지않은 LOC 가 2 개의칼럼으로표시된것을알수있다 6. ON 조건절 JOIN 서술부 (ON 조건절 ) 와비 JOIN 서술부 (WHERE 조건절 ) 를분리하여이해가쉬우며, 칼럼명이다르더라도 JOIN 조건을사용할수있는장점이있다. [ 예제 ] 사원테이블과부서테이블에서사원번호와사원이름, 소속부서코드, 소속부서이름을출력한다. [ 예제 ] SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO); [ 실행결과 ] EMPNO ENAME DEPTNO DNAME SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 14 개의행이선택되었다. NATURAL JOIN 의 JOIN 조건은기본적으로같은이름을가진모든칼럼들에대한동등조건이지만, 임의의 JOIN 조건을지정하거나, 이름이다른칼럼명을 JOIN 조건으로사용하거나, JOIN 칼럼을명시하기위해서는 ON 조건절을사용한다. ON 조건절에사용된괄호는옵션사항이다. USING 조건절을이용한 JOIN 에서는 JOIN 칼럼에대해서 ALIAS 나테이블명과같은접두사를사용하면 SYNTAX 에러가발생하지만, 반대로 ON 조건절을사용한 JOIN 의경우는 ALIAS 나테이블명과같은접두사를사용하여 SELECT 에사용되는칼럼을논리적으로명확하게지정해주어야한다. (DEPTNO E.DEPTNO) ON 조건절은 WHERE 절의 JOIN 조건과같은기능을하면서도, 명시적으로 JOIN 의조건을구분할수있으므로가장많이사용될것으로예상된다. 다만, FROM 절에테이블이많이사용될경우다소복잡하게보여가독성이떨어지는단점이있다. 가. WHERE 절과의혼용 [ 예제 ] ON 조건절과 WHERE 검색조건은충돌없이사용할수있다. 부서코드 30 인부서의소속사원이름및소속부서코드, 부서코드, 부서이름을찾아본다. [ 예제 ] SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.DEPTNO = 30; [ 실행결과 ] ENAME DEPTNO DEPTNO DNAME ALLEN SALES WARD SALES MARTIN SALES BLAKE SALES TURNER SALES JAMES SALES 6 개의행이선택되었다. 나. ON 조건절 + 데이터검증조건추가

9 ON 조건절에 JOIN 조건외에도데이터검색조건을추가할수는있으나, 검색조건목적인경우는 WHERE 절을사용할것을권고한다. ( 다만, 아우터조인에서조인의대상을제한하기위한목적으로사용되는추가조건의경우는 ON 절에표기되어야한다.) [ 예제 ] 매니저사원번호가 7698 번인사원들의이름및소속부서코드, 부서이름을찾아본다. [ 예제 ] SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698); 위 SQL 과아래 SQL 은같은결과를얻을수있다. SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698; [ 실행결과 ] ENAME MGR DEPTNO DNAME ALLEN SALES WARD SALES MARTIN SALES TURNER SALES JAMES SALES 5 개의행이선택되었다. 다. ON 조건절예제 [ 예제 ] 팀과스타디움테이블을스타디움 ID 로 JOIN 하여팀이름, 스타디움 ID, 스타디움이름을찾아본다. [ 예제 ] SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM JOIN STADIUM ON TEAM.STADIUM_ID = STADIUM.STADIUM_ID ORDER BY STADIUM_ID;; 위 SQL 은 STADIUM_ID 라는공통된칼럼이있기때문에아래처럼 USING 조건절로구현할수도있다. SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME FROM TEAM JOIN STADIUM USING (STADIUM_ID) ORDER BY STADIUM_ID; 위 SQL 은고전적인방식인 WHERE 절의 INNER JOIN 으로구현할수도있다. SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM, STADIUM WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID ORDER BY STADIUM_ID [ 실행결과 ] TEAM_NAME STADIUM_ID STADIUM_NAME 광주상무 A02 광주월드컵경기장강원 FC A03 강릉종합경기장제주유나이티드 FC A04 제주월드컵경기장대구 FC A05 대구월드컵경기장유나이티드 B01 인천월드컵경기장일화천마 B02 성남종합운동장삼성블루윙즈 B04 수원월드컵경기장 FC 서울 B05 서울월드컵경기장아이파크 C02 부산아시아드경기장울산현대 C04 울산문수경기장경남 FC C05 창원종합운동장스틸러스 C06 포항스틸야드드래곤즈 D01 광양전용경기장시티즌 D02 대전월드컵경기장 15 개의행이선택되었다. [ 예제 ] 팀과스타디움테이블을팀 ID 로 JOIN 하여팀이름, 팀 ID, 스타디움이름을찾아본다. STADIUM 에는팀 ID 가 HOMETEAM_ID 라는칼럼으로표시되어있다. [ 예제 ] SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME FROM TEAM JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID ORDER BY TEAM_ID; 위 SQL 은고전적인방식인 WHERE 절의 INNER JOIN 으로구현할수도있다. SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME FROM TEAM, STADIUM WHERE TEAM.TEAM_ID = STADIUM.HOMETEAM_ID ORDER BY TEAM_ID; 위 SQL 은 TEAM_ID 와 HOMETEAM_ID 라는다른이름의칼럼을사용하기때문에 USING 조건절을사용할수는없다. [ 실행결과 ] TEAM_NAME TEAM_ID STADIUM_NAME 울산현대 K01 울산문수경기장삼성블루윙즈 K02 수원월드컵경기장스틸러스 K03 포항스틸야드유나이티드 K04 인천월드컵경기장현대모터스 K05 전주월드컵경기장아이파크 K06 부산아시아드경기장드래곤즈 K07 광양전용경기장일화천마 K08 성남종합운동장 FC 서울 K09 서울월드컵경기장시티즌 K10 대전월드컵경기장경남 FC K11 창원종합운동장광주상무 K12 광주월드컵경기장강원 FC K13 강릉종합경기장제주유나이티드 FC K14 제주월드컵경기장 15 개의행이선택되었다. 라. 다중테이블 JOIN [ 예제 ] 사원과 DEPT 테이블의소속부서명, DEPT_TEMP 테이블의바뀐부서명정보를출력한다. [ 예제 ] SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME FROM EMP E JOIN DEPT D ON

10 (E.DEPTNO = D.DEPTNO) JOIN DEPT_TEMP T ON (E.DEPTNO = T.DEPTNO); 위 SQL 은고전적인방식인 WHERE 절의 INNER JOIN 으로구현할수도있다. SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME FROM EMP E, DEPT D, DEPT_TEMP T WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = T.DEPTNO; [ 실행결과 ] EMPNO DEPTNO DNAME NEW_DNAME RESEARCH R&D SALES MARKETING SALES MARKETING RESEARCH R&D SALES MARKETING SALES MARKETING ACCOUNTING ACCOUNTING RESEARCH R&D ACCOUNTING ACCOUNTING SALES MARKETING RESEARCH R&D SALES MARKETING RESEARCH R&D ACCOUNTING ACCOUNTING 14 개의행이선택되었다. [ 예제 ] GK 포지션의선수별연고지명, 팀명, 구장명을출력한다. [ 예제 ] SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지명, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명 FROM PLAYER P JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID WHERE P.POSITION = 'GK' ORDER BY 선수명 ; 위 SQL 은고전적인방식인 WHERE 절의 INNER JOIN 으로구현할수도있다. SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지명, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명 FROM PLAYER P, TEAM T, STADIUM S WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID AND P.POSITION = 'GK' ORDER BY 선수명 ; [ 실행결과 ] 선수명포지션연고지명팀명구장명 강성일 GK 대전시티즌대전월드컵경기장권정혁 GK 울산울산현대울산문수경기장권찬수 GK 성남일화천마성남종합운동장김대희 GK 포항스틸러스포항스틸야드김승준 GK 대전시티즌대전월드컵경기장김용발 GK 전북현대모터스전주월드컵경기장김운재 GK 수원삼성블루윙즈수원월드컵경기장김정래 GK 전남드래곤즈광양전용경기장김준호 GK 포항스틸러스포항스틸야드김창민 GK 전북현대모터스전주월드컵경기장김충호 GK 인천유나이티드인천월드컵경기장남현우 GK 인천유나이티드인천월드컵경기장박유석 GK 부산아이파크부산아시아드경기장 43 개의행이선택되었다. [ 예제 ] 홈팀이 3 점이상차이로승리한경기의경기장이름, 경기일정, 홈팀이름과원정팀이름정보를출력한다. [ 예제 ] SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, HT.TEAM_NAME, AT.TEAM_NAME, HOME_SCORE, AWAY_SCORE FROM SCHEDULE SC JOIN STADIUM ST ON SC.STADIUM_ID = ST.STADIUM_ID JOIN TEAM HT ON SC.HOMETEAM_ID = HT.TEAM_ID JOIN TEAM AT ON SC.AWAYTEAM_ID = AT.TEAM_ID WHERE HOME_SCORE > = AWAY_SCORE +3; 위 SQL 은고전적인방식인 WHERE 절의 INNER JOIN 으로구현할수도있다. SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, HT.TEAM_NAME, AT.TEAM_NAME, HOME_SCORE, AWAY_SCORE FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT WHERE HOME_SCORE> = AWAY_SCORE +3 AND SC.STADIUM_ID = ST.STADIUM_ID AND SC.HOMETEAM_ID = HT.TEAM_ID AND SC.AWAYTEAM_ID = AT.TEAM_ID; FROM 절에 4 개의테이블이 JOIN 에참여하였으며, HOME TEAM 과 AWAY TEAM 의팀이름을구하기위해 TEAM 테이블을 HT 와 AT 두개의 ALIAS 로구분하였다. [ 실행결과 ] STADIUM_NAME STADIUM_ID SCHE_DATE TEAM_NAME TEAM_NAME HOME_SCORE AWAY_SCORE 서울월드컵경기장 B FC 서울삼성블루윙즈 3 0 부산아시아드경기장 C 아이파크시티즌 3 0 울산문수경기장 C 울산현대스틸러스 3 0 성남종합운동장 B 일화천마유나이티드 6 0 창원종합운동장 C 경남 FC 아이파크 개의행이선택되었다. 7. CROSS JOIN CROSS JOIN 은 E.F.CODD 박사가언급한일반집합연산자의 PRODUCT 의개념으로테이블간 JOIN 조건이없는경우생길수있는모든데이터의조합을말한다. 두개의테이블에대한

11 CARTESIAN PRODUCT 또는 CROSS PRODUCT 와같은표현으로, 결과는양쪽집합의 M*N 건의데이터조합이발생한다. ( 아래 56 건의데이터는 EMP 14 건 * DEPT 4 건의데이터조합건수이다.) [ 예제 ] 사원번호와사원이름, 소속부서코드와소속부서이름을찾아본다. [ 예제 ] SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT ORDER BY ENAME; [ 실행결과 ] ENAME DNAME ADAMS SALES ADAMS RESEARCH ADAMS OPERATIONS ADAMS ACCOUNTING ALLEN OPERATIONS ALLEN RESEARCH ALLEN ACCOUNTING ALLEN SALES BLAKE SALES BLAKE OPERATIONS BLAKE RESEARCH BLAKE ACCOUNTING CLARK SALES CLARK RESEARCH CLARK OPERATIONS CLARK ACCOUNTING 56 개의행이선택되었다. [ 예제 ] NATURAL JOIN 의경우 WHERE 절에서 JOIN 조건을추가할수없지만, CROSS JOIN 의경우 WHERE 절에 JOIN 조건을추가할수있다. 그러나, 이경우는 CROSS JOIN 이아니라 INNER JOIN 과같은결과를얻기때문에 CROSS JOIN 을사용하는의미가없어지므로권고하지않는다. [ 예제 ] SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; 위 SQL 과아래 SQL 은같은결과를얻을수있다. SELECT ENAME, DNAME FROM EMP INNER JOIN DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; [ 실행결과 ] ENAME DNAME SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 개의행이선택되었다. 정상적인데이터모델이라면 CROSS PRODUCT 가필요한경우는많지않지만, 간혹튜닝이나리포트를작성하기위해고의적으로사용하는경우가있을수있다. 그리고데이터웨어하우스의개별 DIMENSION( 차원 ) 을 FACT( 사실 ) 칼럼과 JOIN 하기전에모든 DIMENSION 의 CROSS PRODUCT 를먼저구할때유용하게사용할수있다. 8. OUTER JOIN INNER( 내부 ) JOIN 과대비하여 OUTER( 외부 ) JOIN 이라고불리며, JOIN 조건에서동일한값이없는행도반환할때사용할수있다.

12 [ 그림 Ⅱ-2-3] 은 TAB1 테이블이 TAB2 테이블을 JOIN 하되, TAB2 의 JOIN 데이터가있는경우는 TAB2 의데이터를함께출력하고, TAB2 의 JOIN 데이터가없는경우에도 TAB1 의모든데이터를표시하고싶은경우이다. TAB1 의모든값에대해 TAB2 의데이터가반드시존재한다는보장이없는경우 OUTER JOIN 을사용하여해결이가능하다. 과거 OUTER JOIN 을위해 Oracle 은 JOIN 칼럼뒤에 (+) 를표시하였고, Sybase 는비교연산자의앞이나뒤에 (+) 를표시했었는데, JOIN 조건과 WHERE 절검색조건이불명확한단점, IN 이나 OR 연산자사용시에러발생, (+) 표시가누락된칼럼존재시 OUTER JOIN 오류발생, FULL OUTER JOIN 미지원등불편함이많았다. STANDARD JOIN 을사용함으로써 OUTER JOIN 의많은문제점을해결할수있고, 대부분의관계형 DBMS 간에호환성을확보할수있으므로명시적인 OUTER JOIN 을사용할것을적극적으로권장한다. 추가로 OUTER JOIN 역시 JOIN 조건을 FROM 절에서정의하겠다는표시이므로 USING 조건절이나 ON 조건절을필수적으로사용해야한다. 그리고, LEFT/RIGHT OUTER JOIN 의경우에는기준이되는테이블이조인수행시무조건드라이빙테이블이된다. 옵티마이저는이원칙에위배되는다른실행계획을고려하지않는다. 가. LEFT OUTER JOIN 조인수행시먼저표기된좌측테이블에해당하는데이터를먼저읽은후, 나중표기된우측테이블에서 JOIN 대상데이터를읽어온다. 즉, Table A 와 B 가있을때 (Table 'A' 가기준이됨 ), A 와 B 를비교해서 B 의 JOIN 칼럼에서같은값이있을때그해당데이터를가져오고, B 의 JOIN 칼럼에서같은값이없는경우에는 B 테이블에서가져오는칼럼들은 NULL 값으로채운다. 그리고 LEFT JOIN 으로 OUTER 키워드를생략해서사용할수있다. [ 예제 ] STADIUM 에등록된운동장중에는홈팀이없는경기장도있다. STADIUM 과 TEAM 을 JOIN 하되홈팀이없는경기장의정보도같이출력하도록한다. [ 예제 ] SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME FROM STADIUM LEFT OUTER JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID ORDER BY HOMETEAM_ID; OUTER 는생략가능한키워드이므로아래 SQL 은같은결과를얻을수있다. SELECT

13 STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME FROM STADIUM LEFT JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID ORDER BY HOMETEAM_ID; [ 실행결과 ] STADIUM_NAME STADIUM_ID SEAT_COUNT HOMETEAM_ID TEAM_NAME 울산문수경기장 C K01 울산현대수원월드컵경기장 B K02 삼성블루윙즈포항스틸야드 C K03 스틸러스인천월드컵경기장 B K04 유나이티드전주월드컵경기장 D K05 현대모터스부산아시아드경기장 C K06 아이파크광양전용경기장 D K07 드래곤즈성남종합운동장 B K08 일화천마서울월드컵경기장 B K09 FC 서울대전월드컵경기장 D K10 시티즌창원종합운동장 C K11 경남 FC 광주월드컵경기장 A K12 광주상무강릉종합경기장 A K13 강원 FC 제주월드컵경기장 A K14 제주유나이티드 FC 대구월드컵경기장 A K15 대구 FC 안양경기장 F 마산경기장 F 일산경기장 F 부산시민경기장 F 대구시민경기장 F 개의행이선택되었다. INNER JOIN 이라면홈팀이배정된 15 개의경기장만출력되었겠지만, LEFT OUTER JOIN 을사용하였기때문에홈팀이없는대구시민경기장, 부산시민경기장, 일산경기장, 마산경기장, 안양경기장의정보까지추가로출력되었다. 나. RIGHT OUTER JOIN 조인수행시 LEFT JOIN 과반대로우측테이블이기준이되어결과를생성한다. 즉, TABLE A 와 B 가있을때 (TABLE 'B' 가기준이됨 ), A 와 B 를비교해서 A 의 JOIN 칼럼에서같은값이있을때그해당데이터를가져오고, A 의 JOIN 칼럼에서같은값이없는경우에는 A 테이블에서가져오는칼럼들은 NULL 값으로채운다. 그리고 RIGHT JOIN 으로 OUTER 키워드를생략해서사용할수있다. [ 예제 ] DEPT 에등록된부서중에는사원이없는부서도있다. DEPT 와 EMP 를조인하되사원이없는부서정보도같이출력하도록한다. [ 예제 ] SELECT E.ENAME, D.DEPTNO, D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO; OUTER 는생략가능한키워드이므로아래 SQL 은같은결과를얻을수있다. SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO; [ 실행결과 ] ENAME DEPTNO DNAME LOC CLARK 10 ACCOUNTING NEW YORK KING 10 ACCOUNTING NEW YORK MILLER 10 ACCOUNTING NEW YORK JONES 20 RESEARCH DALLAS FORD 20 RESEARCH DALLAS ADAMS 20 RESEARCH DALLAS SMITH 20 RESEARCH DALLAS SCOTT 20 RESEARCH DALLAS WARD 30 SALES CHICAGO TURNER 30 SALES CHICAGO ALLEN 30 SALES CHICAGO JAMES 30 SALES CHICAGO BLAKE 30 SALES CHICAGO MARTIN 30 SALES CHICAGO 40 OPERATIONS BOSTON 15 개의행이선택되었다. INNER JOIN 이라면사원정보와함께사원이배정된 3 개의부서정보와 14 명의사원정보만출력되었겠지만, RIGHT OUTER JOIN 을사용하였기때문에사원이배정되지않은부서번호 40 의 OPERATIONS 부서의 LOC 정보까지출력되었다. 다. FULL OUTER JOIN 조인수행시좌측, 우측테이블의모든데이터를읽어 JOIN 하여결과를생성한다. 즉, TABLE A 와 B 가있을때 (TABLE 'A', 'B' 모두기준이됨 ), RIGHT OUTER JOIN 과 LEFT OUTER JOIN 의결과를합집합으로처리한결과와동일하다. 단, UNION ALL 이아닌 UNION 기능과같으므로중복되는데이터는삭제한다. (UNION ALL 과 UNION 에대해서는다음절에서설명하도록한다.) 그리고 FULL JOIN 으로 OUTER 키워드를생략해서사용할수있다. [ 예제 ] DEPT 테이블과 DEPT_TEMP 테이블의 FULL OUTER JOIN 사례를만들기위해 DEPT_TEMP 의 DEPTNO 를수정한다. 결과적으로 DEPT_TEMP 테이블의새로운 DEPTNO 데이터는 DETP 테이블의 DEPTNO 와 2 건은동일하고 2 건은새로운 DEPTNO 가생성된다.

14 [ 예제 ] UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20; SELECT * FROM DEPT_TEMP; [ 실행결과 ] DEPTNO DNAME LOC ACCOUNTING NEW YORK 40 R&D DALLAS 50 MARKETING CHICAGO 60 OPERATIONS BOSTON 4 개의행이선택되었다. [ 예제 ] DEPTNO 기준으로 DEPT 와 DEPT_TEMP 데이터를 FULL OUTER JOIN 으로출력한다. 예제에사용된 UNION( 중복데이터는제거됨 ) 은다음절에서설명하도록한다. [ 예제 ] SELECT * FROM DEPT FULL OUTER JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO; OUTER 는생략가능한키워드이므로아래 SQL 은같은결과를얻을수있다. SELECT * FROM DEPT FULL JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO; 위 SQL 과아래 SQL 은같은결과를얻을수있다. SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC FROM DEPT L LEFT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO UNION SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC FROM DEPT L RIGHT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO; [ 실행결과 ] DEPTNO DNAME LOC DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 ACCOUNTING NEW YORK 40 OPERATIONS BOSTON 40 R&D DALLAS 50 MARKETING CHICAGO 60 OPERATIONS BOSTON 6 개의행이선택되었다. INNER JOIN 이라면부서번호가동일한 30, 40 부서의 2 개정보만출력되었겠지만, FULL OUTER JOIN 을사용하였기때문에 DEPT 테이블에만있는부서번호 10, 20 의부서와 DEPT_TEMP 테이블에만있는부서번호 50, 60 의부서정보까지같이출력되었다. 9. INNER vs OUTER vs CROSS JOIN 비교 첫번째, INNER JOIN 의결과는다음과같다. 양쪽테이블에모두존재하는키값이 B-B, C-C 인 2 건이출력된다. 두번째, LEFT OUTER JOIN 의결과는다음과같다. TAB1 을기준으로키값조

15 합이 B-B, C-C, D-NULL, E-NULL 인 4 건이출력된다. 세번째, RIGHT OUTER JOIN 의결과는다음과같다. TAB2 를기준으로키값조합이 NULL-A, B-B, C-C 인 3 건이출력된다. 네번째, FULL OUTER JOIN 의결과는다음과같다. 양쪽테이블을기준으로키값조합이 NULL-A, B-B, C-C, D-NULL, E-NULL 인 5 건이출력된다. 다섯번째, CROSS JOIN 의결과는다음과같다. JOIN 가능한모든경우의수를표시하지만단, OUTER JOIN 은제외한다. 양쪽테이블 TAB1 과 TAB2 의데이터를곱한개수인 4 * 3 = 12 건이추출됨키값조합이 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D- B, D-C, E-A, E-B, E-C 인 12 건이출력된다.

16 02. 집합연산자 두개이상의테이블에서조인을사용하지않고연관된데이터를조회하는방법중에또다른방법이있는데그방법이바로집합연산자 (Set Operator) 를사용하는방법이다. 기존의조인에서는 FROM 절에검색하고자하는테이블을나열하고, WHERE 절에조인조건을기술하여원하는데이터를조회할수있었다. 하지만집합연산자는여러개의질의의결과를연결하여하나로결합하는방식을사용한다. 즉, 집합연산자는 2 개이상의질의결과를하나의결과로만들어준다. 일반적으로집합연산자를사용하는상황은서로다른테이블에서유사한형태의결과를반환하는것을하나의결과로합치고자할때와동일테이블에서서로다른질의를수행하여결과를합치고자할때사용할수있다. 이외에도튜닝관점에서실행계획을분리하고자하는목적으로도사용할수있다. 집합연산자를사용하기위해서는다음제약조건을만족해야한다. SELECT 절의칼럼수가동일하고 SELECT 절의동일위치에존재하는칼럼의데이터타입이상호호환가능 ( 반드시동일한데이터타입일필요는없음 ) 해야한다. 그렇지않으면데이터베이스가오류를반환한다. 집합연산자는개별 SQL 문의결과집합에대해합집합 (UNION/UNION ALL), 교집합 (INTERSECT), 차집합 (EXCEPT) 으로집합간의관계를가지고작업을한다.

17 집합연산자를가지고연산한결과는 [ 그림 Ⅱ-2-5] 와같다. [ 그림 Ⅱ-2-5] 의왼쪽에존재하는 R1, R2 는각각의 SQL 문을실행해서생성된개별결과집합을의미한다. [ 그림 Ⅱ-2-5] 에서보면알수있듯이 UNION ALL 을제외한다른집합연산자에서는 SQL 문의결과집합에서먼저중복된건을배제하는작업을수행한후에집합연산을적용한다 ( 논리적인관점의처리임 ). UNION 연산에서 R1 = {1, 2, 3, 5}, R2 = {1, 2, 3, 4} 가되고, 이것의합집합 (R1 R2) 의결과는 {1, 2, 3, 4, 5} 이다. UNION ALL 연산은중복에대한배제없이 2 개의결과집합을단순히합친것과동일한결과이다. UNION ALL 의결과는 {1, 1, 1, 2, 2, 3, 3, 5, 1, 1, 2, 2, 2, 3, 4} 이다. INTERSECT 연산에서 R1 = {1, 2, 3, 5}, R2 = {1, 2, 3, 4} 가되어, 이것의교집합 (R1 R2) 의결과는 {1, 2, 3} 이다. EXCEPT 연산에서는 R1 = {1, 2, 3, 5}, R2 = {1, 2, 3, 4} 가되고, 이것의차집합 (R1 R2) 의결과는 {5} 이다. EXCEPT 연산에서는순서가중요하다. 만약순서가바뀌어서 R2 R1 의차집합이었다면결과는 {4} 가된다. 집합연산자를사용하여만들어지는 SQL 문의형태는다음과같다. SELECT 칼럼명 1, 칼럼명 2,... FROM 테이블명 1 [WHERE 조건식 ] [[GROUP BY 칼럼 (Column) 이나표현식 [HAVING 그룹조건식 ] ] 집합연산자 SELECT 칼럼명 1, 칼럼명 2,... FROM 테이블명 2 [WHERE 조건식 ] [[GROUP BY 칼럼 (Column) 이나표현식 [HAVING 그룹조건식 ] ] [ORDER BY 1, 2 [ASC 또는 DESC ] ; SELECT PLAYER_NAME 선수명, BACK_NO 백넘버 FROM PLAYER WHERE TEAM_ID = 'K02' UNION SELECT PLAYER_NAME 선수명, BACK_NO 백넘버 FROM PLAYER WHERE TEAM_ID = 'K07' ORDER BY 1; 집합연산자는사용상의제약조건을만족한다면어떤형태의 SELECT 문이라도이용할수있다. 집합연산자는여러개의 SELECT 문을연결하는것에지나지않는다. ORDER BY 는집합연산을적용한최종결과에대한정렬처리이므로가장마지막줄에한번만기술한다. 아래질문에대해집합연산자를사용하여처리하는방법을알아보자.

18 [ 집합연산자를연습하기위한질문 ] 1) K- 리그소속선수들중에서소속이삼성블루윙즈팀인선수들과전남드레곤즈팀인선수들에대한내용을모두보고싶다. 2) K- 리그소속선수들중에서소속이삼성블루윙즈팀인선수들과포지션이골키퍼 (GK) 인선수들을모두보고싶다. 3) K- 리그소속선수들에대한정보중에서포지션별평균키와팀별평균키를알고싶다. 4) K- 리그소속선수를중에서소속이삼성블루윙즈팀이면서포지션이미드필더 (MF) 가아닌선수들의정보를보고싶다. 5) K- 리그소속선수들중에서소속이삼성블루윙즈팀이면서포지션이골키퍼 (GK) 인선수들의정보를보고싶다. SQL 문을작성하기에전에 [ 집합연산자를연습하기위한질문 ] 을다음과같이집합연산자를사용형태로해석할수있다. [ 질문을집합연산의개념으로해석한결과 ] 1) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중소속이전남드레곤즈팀인선수들의집합의합집합 2) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이골키퍼 (GK) 인선수들의집합의합집합 3) K- 리그소속선수중포지션별평균키에대한집합과 K- 리그소속선수중팀별평균키에대한집합의합집합 4) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이미드필더 (MF)) 인선수들의집합의차집합 5) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이골키퍼 (GK) 인선수들의집합의교집합 위의결과를집합연산자를사용하여 SQL 문을작성하고그결과를확인해보도록하자. 먼저첫번째질문에대한 SQL 문을작성하고실행해보자. [ 질문 1] 1) K- 리그소속선수들중에서소속이삼성블루윙즈팀인선수들과전남드레곤즈팀인선수들에대한내용을모두보고싶다. 1) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중소속이전남드레곤즈팀인선수들의집합의합집합 [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' UNION SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K07' [ 실행결과 ] 팀코드선수명포지션백넘버키 K02 가비 MF K02 강대희 MF K02 고종수 MF K02 고창현 MF K02 김강진 DF K07 강철 DF K07 김반 MF K07 김영수 MF K07 김정래 GK K07 김창원 DF 개의행이선택되었다. 첫번째질문에대한 SQL 문에서삼성블루윙즈팀인선수들과전남드레곤즈팀의선수들의합집합이라는것은 WHERE 절에 IN 또는 OR 연산자로도변환이가능하다. 다만 IN 또는 OR 연산자를사용할경우에는결과의표시순서가달라질수있다. 집합이라는관점에서는결과가표시되는순서가틀렸다고두집합이서로다르다고말할수없다. 만약, 결과의동일한표시순서를원한다면 ORDER BY 절을사용해서명시적으로정렬순서를정의하는것이바람직하다. 첫번째질문에대한 SQL 문을 IN 또는 OR 연산자를사용한 SQL 문으로변경해보고결과의표시순서가다름을확인해보자. [ 예제 ] ( 비교 ) SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'; SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID IN ('K02', 'K07'); [ 실행결과 ] 팀선수명포지션백넘버키 K07 김회택 TM K07 서현옥 TC K07 정상호 TC K07 최철우 TC K07 정영광 GK K02 정호 TM K02 왕선재 TC K02 코샤 TC K02 윤성효 TC K02 정광수 GK 개의행이선택되다. 두번째질문에대해 SQL 문을작성하고결과를확인해보자.

19 [ 질문 2] 2) K- 리그소속선수들중에서소속이삼성블루윙즈팀인선수들과포지션이골키퍼 (GK) 인선수들을모두보고싶다. 2) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이골키퍼 (GK) 인선수들의집합의합집합 [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' UNION SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE POSITION = 'GK'; [ 실행결과 ] 팀코드선수명포지션백넘버키 K01 권정혁 GK K01 서동명 GK K01 양지원 GK K01 이무림 GK K01 최창주 GK K02 가비 MF K02 강대희 MF K02 고종수 MF K02 고창현 MF K02 김강진 DF 개의행이선택되었다. 두번째질문에대한실행결과는첫번째실행결과와비교해보면집합의대상만차이가날뿐다른점은없다. 마찬가지로두번째질문에대해 OR 연산자를사용한 SQL 문으로변경하면다음과같다. 여기서는서로다른칼럼에조건을사용했기때문에 IN 연산자를사용할수없다. [ 예제 ] ( 비교 ) SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' OR POSITION = 'GK'; 만약, 두번째질문에대한 SQL 문에서 UNION 이라는집합연산자대신에 UNION ALL 이라는집합연산자를사용하면어떻게될지한번수행해보자. [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' UNION ALL SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE POSITION = 'GK'; [ 실행결과 ] 팀코드선수명포지션백넘버키 K02 정호 TM K02 왕선재 TC K02 코샤 TC K02 윤성효 TC K02 정광수 GK K04 남현우 GK K04 김충호 GK K04 이현 GK K04 한동진 GK K10 강성일 GK 개의행이선택되었다. 수행결과에서알수있듯이결과건수가 UNION 은 88 건이었으나 UNION ALL 은 92 건으로결과건수가늘어났다. 두 SQL 문의결과가서로다르다. 결과가다른이유는 UNION 은결과에서중복이존재할경우중복을제외시키지만 UNION ALL 은각각의질의결과를단순히결합시켜줄뿐중복된결과를제외시키지않기때문이다. 이와같이결과집합에중복이존재하면 UNION 과 UNION ALL 의결과는달라진다. UNION ALL 에서중복된결과들을확인해보고자할때는 ORDER BY 절을사용하면용이하다. 아래 SQL 문을통해중복된결과를확인해보자. [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' UNION ALL SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE POSITION = 'GK' ORDER BY 1, 2, 3, 4, 5; [ 실행결과 ] 팀코드선수명포지션백넘버키 K02 김운재 GK K02 김운재 GK K02 정광수 GK K02 정광수 GK K02 조범철 GK K02 조범철 GK K02 최호진 GK K02 최호진 GK 개의행이선택되었다. 결과에서삼성블루윙즈팀 (K02) 에서포지션이골키퍼 (GK) 인사람이중복표시되째질문에대한 SQL 문을작성하고결과를확인해보자. [ 질문 3] 3) K- 리그소속선수들에대한정보중에서포지션별평균키와팀별평균키를알고싶다. 3) K- 리그소속선수중포지션별평균키에대한집합과 K- 리그소속선수중팀별평균키에대한집합의합집합 [ 예제 ] SELECT 'P' 구분코드, POSITION 포지션, AVG(HEIGHT) 평균키 FROM PLAYER GROUP BY POSITION UNION SELECT 'T' 구분코드, TEAM_ID 팀명, AVG(HEIGHT) 평균키 FROM PLAYER GROUP BY TEAM_ID ORDER BY 1;

20 [ 실행결과 ] 구분코드포지션평균키 P DF P FW P GK P MF P TC T K T K T K T K T K 개의행이선택되었다. 세번째질문에서는평균키에대한값들의합집합을구하는것이다. 합집합을구하기위해 SQL 문에서그룹함수를사용했다. 그룹함수도집합연산자에서사용이가능하다는것을알수있다. 또한실제로테이블에는존재하지않지만결과행을구분하기위해 SELECT 절에칼럼 (' 구분코드 ') 을추가할수있다는것을알수있다. 이와같이목적을위해 SELECT 절에임의의칼럼을추가하는것은다른모든 SQL 문에서적용가능하다. 집합연산자의결과를표시할때 HEADING 부분은첫번째 SQL 문에서사용된 HEADING 이적용된다는것을알수있다. SQL 문에서첫번째 SELECT 절에서는 ' 포지션 ' HEADING 을사용하였고두번째 SELECT 절에서는 ' 팀명 ' HEADING 을사용하였다. 그러나결과에는 ' 포지션 ' HEADING 으로표시되었다. 네번째질문인삼성블루윙즈팀인집합과포지션이미드필더 (MF) 인선수들의차집합에대한 SQL 문을작성하고결과를확인해보자. [ 질문 4] 4) K- 리그소속선수를중에서소속이삼성블루윙즈팀이면서포지션이미드필더 (MF) 가선수들의정보를보고싶다. 4) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이미드필더 (MF)) 인선수들의집합의차집합 [ 예제 ] Oracle SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' MINUS SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE POSITION = 'MF' ORDER BY 1, 2, 3, 4, 5; SQL Server 에서는 MINUS 대신 EXCEPT 를사용할수있다. [ 실행결과 ] 팀코드선수명포지션백넘버키 K02 김강진 DF K02 김관희 FW K02 김만근 FW K02 김병국 DF K02 김병근 DF K02 왕선재 TC K02 윤성효 TC K02 윤화평 FW K02 이성용 DF K02 정광수 GK 개의행이선택되었다. 차집합은앞의집합의결과에서뒤의집합의결과를빼는것이다. 이번 SQL 문은삼성블루윙즈팀의선수들중에서포지션이미드필더 (MF) 인선수들의정보를빼는것이다. 해당 SQL 문은다른형태의 SQL 문으로변경가능하다. EXCEPT 연산자의앞에오는 SQL 문의조건은만족하고뒤에오는 SQL 문의조건은만족하지않는 SQL 문과동일한결과를얻을수있다. 그러므로 EXCEPT 연산자를사용하지않고논리연산자를이용하여동일한결과의 SQL 문을작성할수있다. SQL 문을실행하여결과가동일한지직접확인해보자. [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' AND POSITION <> 'MF' ORDER BY 1, 2, 3, 4, 5; MINUS 연산자는 NOT EXISTS 또는 NOT IN 서브쿼리를이용한 SQL 문으로도변경가능하다. (NOT EXISTS 와 NOT IN 에대한설명은제 5 절서브쿼리에서참고 ) [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER X WHERE X.TEAM_ID = 'K02' AND NOT EXISTS (SELECT 1 FROM PLAYER Y WHERE Y.PLAYER_ID = X.PLAYER_ID AND POSITION = 'MF') ORDER BY 1, 2, 3, 4, 5; SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' AND PLAYER_ID NOT IN (SELECT PLAYER_ID FROM PLAYE WHERE POSITION = 'MF') ORDER BY 1, 2, 3, 4, 5; 이제마지막으로삼성블루윙즈팀이면서포지션이골키퍼인선수들인교집합을얻기위한 SQL 문을작성해보자.

21 [ 질문 5] 5) K- 리그소속선수들중에서소속이삼성블루윙즈팀이면서포지션이골키퍼 (GK) 인선수들의정보를보고싶다. 5) K- 리그소속선수중소속이삼성블루윙즈팀인선수들의집합과 K- 리그소속선수중포지션이골키퍼 (GK) 인선수들의집합의교집합 [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' INTERSECT SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE POSITION = 'GK' ORDER BY 1, 2, 3, 4, 5; [ 실행결과 ] 팀코드선수명포지션백넘버키 K02 김운재 GK K02 정광수 GK K02 조범철 GK K02 최호진 GK 개의행이선택되었다. 교집합의결과는소속이삼성블루윙즈팀인선수의집합이면서포지션이골키퍼인집합인두개의조건을만족하는집합이다. 이것은 INTERSECT 연산자의앞에오는 SQL 문의조건은만족하면서뒤의 SQL 문의조건을만족하는것과동일한결과를얻을수있다. 다음과같이 INTERSECT 연산자를사용하지않고도논리연산자만으로결과가동일한 SQL 문을작성할수있다. SQL 문을실행하여결과가동일한지직접확인해보자. [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' AND POSITION = 'GK' ORDER BY 1, 2, 3, 4, 5; INTERSECT 연산자는 EXISTS 또는 IN 서브쿼리를이용한 SQL 문으로변경가능하다. (EXISTS 와 IN 에대한설명은제 5 절서브쿼리에서참고 ) [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER X WHERE X.TEAM_ID = 'K02' AND EXISTS (SELECT 1 FROM PLAYER Y WHERE Y.PLAYER_ID = X.PLAYER_ID AND Y.POSITION = 'GK') ORDER BY 1, 2, 3, 4, 5; [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' AND PLAYER_ID IN (SELECT PLAYER_ID FROM PLAYER WHERE POSITION = 'GK') ORDER BY 1, 2, 3, 4, 5;

22 03. 계층형질의와셀프조인 1. 계층형질의 테이블에계층형데이터가존재하는경우데이터를조회하기위해서계층형질의 (Hierarchical Query) 를사용한다. 계층형데이터란동일테이블에계층적으로상위와하위데이터가포함된데이터를말한다. 예를들어, 사원테이블에서는사원들사이에상위사원 ( 관리자 ) 과하위사원관계가존재하고조직테이블에서는조직들사이에상위조직과하위조직관계가존재한다. 엔터티를순환관계데이터모델로설계할경우계층형데이터가발생한다. 순환관계데이터모델의예로는조직, 사원, 메뉴등이있다. [ 그림 Ⅱ-2-6] 은사원에대한순환관계데이터모델을표현한것이다. (2) 계층형구조에서 A 의하위사원은 B, C 이고 B 밑에는하위사원이없고 C 의하위사원은 D, E 가있다. 계층형구조를데이터로표현한것이 (3) 샘플데이터이다. 계층형데이터조회는 DBMS 벤더와버전에따라다른방법으로지원한다. 여기서는 Oracle 과 SQL Server 기준으로설명한다. 가. Oracle 계층형질의 Oracle 은계층형질의를지원하기위해서 [ 그림 Ⅱ-2-7] 과같은계층형질의구문을제공한다. - START WITH 절은계층구조전개의시작위치를지정하는구문이다. 즉, 루트데이터를지정한다.( 액세스 ) - CONNECT BY 절은다음에전개될자식데이터를지정하는구문이다. 자식데이터는 CONNECT BY 절에주어진조건을만족해야한다.( 조인 ) - PRIOR : CONNECT BY 절에사용되며, 현재읽은칼럼을지정한다. PRIOR 자식 = 부모형태를사용하면계층구조에서자식데이터에서부

23 모데이터 ( 자식 부모 ) 방향으로전개하는순방향전개를한다. 그리고 PRIOR 부모 = 자식형태를사용하면반대로부모데이터에서자식데이터 ( 부모 자식 ) 방향으로전개하는역방향전개를한다. - NOCYCLE : 데이터를전개하면서이미나타났던동일한데이터가전개중에다시나타난다면이것을가리켜사이클 (Cycle) 이형성되었다라고말한다. 사이클이발생한데이터는런타임오류가발생한다. 그렇지만 NOCYCLE 를추가하면사이클이발생한이후의데이터는전개하지않는다. - ORDER SIBLINGS BY : 형제노드 ( 동일 LEVEL) 사이에서정렬을수행한다. - WHERE : 모든전개를수행한후에지정된조건을만족하는데이터만추출한다.( 필터링 ) Oracle 은계층형질의를사용할때다음과같은가상칼럼 (Pseudo Column) 을제공한다. 다음은 [ 그림 Ⅱ-2-6] 의 (3) 샘플데이터를계층형질의구문을이용해서조회한것이다. 여기서는결과데이터를들여쓰기하기위해서 LPAD 함수를사용하였다. [ 예제 ] SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) 사원사원, 관리자, CONNECT_BY_ISLEAF ISLEAF FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자 ; [ 실행결과 ] LEVEL 사원관리자 ISLEAF A 0 2 B A 1 2 C A 0 3 D C 1 3 E C 1 A 는루트데이터이기때문에레벨이 1 이다. A 의하위데이터인 B, C 는레벨이 2 이다. 그리고 C 의하위데이터인 D, E 는레벨이 3 이다. 리프데이터는 B, D, E 이다. 관리자 사원방향을전개이기때문에순방향전개이다. [ 그림 Ⅱ-2-8] 은계층형질의에대한논리적인실행모습이다.

24 다음예제는사원 'D' 로부터자신의상위관리자를찾는역방향전개의예이다. [ 예제 ] SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) 사원사원, 관리자, CONNECT_BY_ISLEAF ISLEAF FROM 사원 START WITH 사원 = 'D' CONNECT BY PRIOR 관리자 = 사원 ; [ 실행결과 ] LEVEL 사원관리자 ISLEAF D C 0 2 C A 0 3 A 1 본예제는역방향전개이기때문에하위데이터에서상위데이터로전개된다. 결과를보면내용을제외하고표시형태는순방향전개와동일하다. D 는루트데이터이기때문에레벨이 1 이다. D 의상위데이터인 C 는레벨이 2 이다. 그리고 C 의상위데이터인 A 는레벨이 3 이다. 리프데이터는 A 이다. 루트및레벨은전개되는방향에따라반대가됨을알수있다. [ 그림 Ⅱ-2-9] 는역방향전개에대한계층형질의에대한논리적인실행모습이다. Orcle 은계층형질의를사용할때사용자편의성을제공하기위해서 [ 표 Ⅱ-2-3] 과같은함수를제공한다

25 SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT 를사용한예는다음과같다. [ 예제 ] SELECT CONNECT_BY_ROOT 사원루트사원, SYS_CONNECT_BY_PATH( 사원, '/') 경로, 사원, 관리자 FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자 [ 실행결과 ] 루트사원경로사원관리자 A /A A A /A/B B A A /A/C C A A /A/C/D D C A /A/C/E E C START WITH 를통해추출된루트데이터가 1 건이기때문에루트사원은모두 A 이다. 경로는루트로부터현재데이터까지의경로를표시한다. 예를들어, D 의경로는 A C D 이다. 나. SQL Server 계층형질의 SQL Server 2000 버전까지는계층형질의를작성할수있는문법을지원하지않았다. 조직도처럼계층적구조를가진데이터는저장프로시저를재귀호출하거나 While 루프문에서임시테이블을사용하는등 ( 순수한쿼리가아닌 ) 프로그램방식으로전개해야만했다. 그러나 SQL Server 2005 버전부터는하나의질의로원하는결과를얻을수있게되었다. 먼저, Northwind 데이터베이스에접속하여 Employees 테이블의데이터를조회해보자. USE NORTHWIND GO SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES GO ************************************************************************** EmployeeID LastName FirstName ReportsTo Davolio Nancy 2 2 Fulle Andrew NULL 3 Leverling Janet 2 4 Peacock Margaret 2 5 Buchanan Steven 2 6 Suyama Michael 5 7 King Robert 5 8 Callahan Laura 2 9 Dodsworth Anne 5 (9 개행적용됨 ) 총 9 개로우가있는데, ReportsTo 칼럼이상위사원에해당하며 EmployeeID 칼럼과재귀적관계를맺고있다. EmployeeID 가 2 인 Fuller 사원을살펴보면, ReportsTo 칼럼값이 NULL 이므로계층구조의최상위에있음을알수있다. CTE(Common Table Expression) 를재귀호출함으로써 Employees 데이터의최상위부터시작해하위방향으로계층구조를전개하도록작성한쿼리와결과는다음과같다. WITH EMPLOYEES_ANCHOR AS ( SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL FROM EMPLOYEES WHERE REPORTSTO IS NULL /* 재귀호출의시작점 */ UNION ALL SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1 FROM EMPLOYEES_ANCHOR A, EMPLOYEES R WHERE A.EMPLOYEEID = R.REPORTSTO ) SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES_ANCHOR GO ************************************************************************** Level EmployeeID LastName FirstName ReportsTo Fuller Andrew NULL 1 1 Davolio Nancy Leverling Janet Peacock Margaret Buchanan Steven Callahan Laura Suyama Michael King Robert Dodsworth Anne 5 (9 개행적용됨 ) WITH 절의 CTE 쿼리를보면, UNION ALL 연산자로쿼리두개를결합했다. 둘중위에있는쿼리를 앵커멤버 (Anchor Member) 라고하고, 아래에있는쿼리를 재귀멤버 (Recursive Member) 라고한다. 아래는재귀적쿼리의처리과정이다.

26 1. CTE 식을앵커멤버와재귀멤버로분할한다. 2. 앵커멤버를실행하여첫번째호출또는기본결과집합 (T0) 을만든다. 3. Ti 는입력으로사용하고 Ti+1 은출력으로사용하여재귀멤버를실행한다. 4. 빈집합이반환될때까지 3 단계를반복한다. 5. 결과집합을반환한다. 이것은 T0 에서 Tn 까지의 UNION ALL 이다. 정리하자면다음과같다. 먼저, 앵커멤버가시작점이자 Outer 집합이되어 Inner 집합인재귀멤버와조인을시작한다. 이어서, 앞서조인한결과가다시 Outer 집합이되어재귀멤버와조인을반복하다가조인결과가비어있으면즉, 더조인할수없으면지금까지만들어진결과집합을모두합하여리턴한다. [ 그림 Ⅱ-2-10] 에있는조직도를쿼리로출력했을때, 대부분사용자는아래와같은결과를기대할것이다.( 보기편하도록각로우앞쪽에자신의레벨만큼빈칸을삽입했다.) EmployeeID ManagerID NULL 아래에 t_emp 데이터의최상위부터시작해하위방향으로계층구조를전개하도록작성한쿼리와그결과이다. WITH T_EMP_ANCHOR AS ( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL FROM T_EMP WHERE MANAGERID IS NULL /* 재귀호출의시작점 */ UNION ALL SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1 FROM T_EMP_ANCHOR A, T_EMP R WHERE A.EMPLOYEEID = R.MANAGERID ) SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID FROM T_EMP_ANCHOR GO ************************************************************************** Level EmployeeID ManagerID NULL (14 개행적용됨 보다시피, 계층구조를단순히하위방향으로전개했을뿐 [ 그림 Ⅱ-2-10] 에있는조직도와는많이다른모습이다. 앞서보았듯이, CTE 재귀호출로만들어낸계층구조는실제와다른모습으로출력된다. 따라서조직도와같은모습으로출력하려면 order by 절을추가해원하는순서대로결과를정렬해야한다. 실제조직도와같은모습의결과를출력하도록, CTE 에 Sort 라는정렬용칼럼을추가하고쿼리마지막에 order by 조건을추가해보자.( 단, 앵커멤버와재귀멤버양쪽에서 convert 함수등으로데이터형식을일치시켜야한다.) WITH T_EMP_ANCHOR AS ( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL,

27 CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT FROM T_EMP WHERE MANAGERID IS NULL /* 재귀호출의시작점 */ UNION ALL SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1, CONVERT(VARCHAR(1000), A.SORT + '/' + R.EMPLOYEEID) AS SORT FROM T_EMP_ANCHOR A, T_EMP R WHERE A.EMPLOYEEID = R.MANAGERID ) SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID, SORT FROM T_EMP_ANCHOR ORDER BY SORT GO CTE 안에서 Sort 칼럼에사번 (=EmployeeID) 을재귀적으로더해나가면정렬기준으로삼을수있는값이만들어진다. 아래는 Sort 칼럼으로정렬하여출력한결과인데, [ 그림 Ⅱ-2-10] 에있는조직도의모습과일치한다. Level EmployeeID ManagerID Sort NULL / /1100/ /1100/ /1100/1120/ /1100/1120/ / /1200/ /1200/1210/ /1200/1210/ /1200/ /1200/1220/ /1200/1220/ /1300 (14 개행적용됨 ) 가상의 Sort 칼럼을추가해정렬하는게아쉽기는하지만, SQL Server 에서계층구조를실제모습대로출력하려면현재 (2005, 2008 버전기준 ) 로서는감수해야할수밖에없다. 2. 셀프조인 셀프조인 (Self Join) 이란동일테이블사이의조인을말한다. 따라서 FROM 절에동일테이블이두번이상나타난다. 동일테이블사이의조인을수행하면테이블과칼럼이름이모두동일하기때문에식별을위해반드시테이블별칭 (Alias) 를사용해야한다. 그리고칼럼에도모두테이블별칭을사용해서어느테이블의칼럼인지식별해줘야한다. 이외사항은조인과동일하다. 셀프조인에대한기본적인사용법은다음과같다 SELECT ALIAS 명 1. 칼럼명, ALIAS 명 2. 칼럼명,... FROM 테이블 1 ALIAS 명 1, 테이블 2 ALIAS 명 2 WHERE ALIAS 명 1. 칼럼명 2 = ALIAS 명 2. 칼럼명 1; SELECT WORKER.ID 사원번호, WORKER.NAME 사원명, MANAGER.NAME 관리자명 FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.ID; 계층형질의에서살펴보았던사원이라는테이블속에는사원과관리자가모두하나의사원이라는개념으로동일시하여같이입력되어있다. 이것을이용해서다음문제를셀프조인으로해결해보면다음과같다. 자신과상위, 차상위관리자를같은줄에표시하라. 이문제를해결하기위해서는 FROM 절에사원테이블을두번사용해야한다.

28 셀프조인은동일한테이블 ( 사원 ) 이지만 [ 그림 Ⅱ-2-11] 과같이개념적으로는두개의서로다른테이블 ( 사원, 관리자 ) 을사용하는것과동일하다. 동일테이블을다른테이블인것처럼처리하기위해테이블별칭을사용한다. 여기서는 E1( 사원 ), E2( 관리자 ) 테이블별칭을사용하였다. 차상위관리자를구하기위해서 E1. 관리자 = E2. 사원조인조건을사용한다. 셀프조인을이용한 SQL 문은다음과같다. [ 예제 ] SELECT E1. 사원, E1. 관리자, E2. 관리자차상위 _ 관리자 FROM 사원 E1, 사원 E2 WHERE E1. 관리자 = E2. 사원 ORDER BY E1. 사원 ; [ 실행결과 ] 사원관리자차상위 _ 관리자 B A C A D C A E C A 자신과자신의직속관리자는동일한행에서데이터를구할수있으나차상위관리자는바로구할수없다. 차상위관리자를구하기위해서는자신의직속관리자를기준으로사원테이블과한번더조인 ( 셀프조인 ) 을수행해야한다. 결과표시를위해 SELECT 절에 2 개의 관리자 칼럼을사용되었다. 한명은자신의직속관리자 (E1. 관리자 ) 이고다른한명은자신의차상위관리자 (E2. 관리자 ) 이다. 결과를보면, B 와 C 의관리자는 A 이고차상위관리자는없다. D 와 E 의관리자는 C 이고차상위관리자는 A 이다. 결과에서 A 에대한정보는누락되었다. 내부조인 (Inner Join) 을사용할경우자신의관리자가존재하지않는경우에는관리자 (E2) 테이블에서조인할대상이존재하지않기때문에해당데이터는결과에서누락된다. 이를방지하기위해서는아우터조인을사용해야한다. 다음은아우터조인을사용한예이다. [ 예제 ] SELECT E1. 사원, E1. 관리자, E2. 관리자차상위 _ 관리자 FROM 사원 E1 LEFT OUTER JOIN 사원 E2 ON (E1. 관리자 = E2. 사원 ) ORDER BY E1. 사원 ; [ 실행결과 ] 사원관리자차상위 _ 관리자 A B A C A D C A E C A 아우터조인을사용해서관리자가존재하지않는데이터까지모두결과에표시되었다.

29 04. 서브쿼리 서브쿼리 (Subquery) 란하나의 SQL 문안에포함되어있는또다른 SQL 문을말한다. 서브쿼리는알려지지않은기준을이용한검색을위해사용한다. 서브쿼리는 [ 그림 Ⅱ-2-12] 와같이메인쿼리가서브쿼리를포함하는종속적인관계이다. 조인은조인에참여하는모든테이블이대등한관계에있기때문에조인에참여하는모든테이블의칼럼을어느위치에서라도자유롭게사용할수있다. 그러나서브쿼리는메인쿼리의칼럼을모두사용할수있지만메인쿼리는서브쿼리의칼럼을사용할수없다. 질의결과에서브쿼리칼럼을표시해야한다면조인방식으로변환하거나함수, 스칼라서브쿼리 (Scalar Subquery) 등을사용해야한다. 조인은집합간의곱 (Product) 의관계이다. 즉, 1:1 관계의테이블이조인하면 1(= 1 * 1) 레벨의집합이생성되고, 1:M 관계의테이블을조인하면 M(= 1 * M) 레벨의집합이생성된다. 그리고 M:N 관계의테이블을조인하면 MN(= M * N) 레벨의집합이결과로서생성된다. 예를들어, 조직 (1) 과사원 (M) 테이블을조인하면결과는사원레벨 (M) 의집합이생성된다. 그러나서브쿼리는서브쿼리레벨과는상관없이항상메인쿼리레벨로결과집합이생성된다. 예를들어, 메인쿼리로조직 (1), 서브쿼리로사원 (M) 테이블을사용하면결과집합은조직 (1) 레벨이된다. SQL 문에서서브쿼리방식을사용해야할때잘못판단하여조인방식을사용하는경우가있다. 예를들어, 결과는조직레벨이고사원테이블에서체크해야할조건이존재한다고가정하자. 이런상황에서 SQL 문을작성할때조인을사용한다면결과집합은사원 (M) 레벨이될것이다. 이렇게되면원하는결과가아니기때문에 SQL 문에 DISTINCT 를추가해서결과를다시조직 (1) 레벨로만든다. 이와같은상황에서는조인방식이아니라서브쿼리방식을사용해야한다. 메인쿼리로조직을사용하고서브쿼리로사원테이블을사용하면결과집합은조직레벨이되기때문에원하는결과가된다. 서브쿼리를사용할때다음사항에주의해야한다. 1 서브쿼리를괄호로감싸서사용한다. 2 서브쿼리는단일행 (Single Row) 또는복수행 (Multiple Row) 비교연산자와함께사용가능하다. 단일행비교연산자는서브쿼리의결과가반드시 1 건이하이어야하고복수행비교연산자는서브쿼리의결과건수와상관없다. 3 서브쿼리에서는 ORDER BY 를사용하지못한다. ORDER BY 절은 SELECT 절에서오직한개만올수있기때문에 ORDER BY 절은메인쿼리의마지막문장에위치해야한다. 서브쿼리가 SQL 문에서사용이가능한곳은다음과같다. - SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절 - INSERT 문의 VALUES 절 - UPDATE 문의 SET 절 서브쿼리의종류는동작하는방식이나반환되는데이터의형태에따라분류할수있다. 동작하는방식에따라서브쿼리를분류하면 [ 표 Ⅱ-2-4] 와같이두가지로나눌수있다.

30 서브쿼리는메인쿼리안에포함된종속적인관계이기때문에논리적인실행순서는항상메인쿼리에서읽혀진데이터에대해서브쿼리에서해당조건이만족하지를확인하는방식으로수행되어야한다. 그러나실제서브쿼리의실행순서는상황에따라달라질수있다. 반환되는데이터의형태에따라서브쿼리는 [ 표 Ⅱ-2-5] 와같이세가지로분류된다. 1. 단일행서브쿼리 서브쿼리가단일행비교연산자 (=, <, <=, >, >=, <>) 와함께사용할때는서브쿼리의결과건수가반드시 1 건이하이어야한다. 만약, 서브쿼리의결과건수가 2 건이상을반환하면 SQL 문은실행시간 (Run Time) 오류가발생한다. 이런종류의오류는컴파일할때 (Compile Time) 는알수없는오류이다. 단일행서브쿼리의예로 ' 정남일 ' 선수가소속된팀의선수들에대한정보를표시하는문제를가지고설명해보면다음과같다.

31 [ 그림 Ⅱ-2-13] 은 2 개의 SQL 문으로구성되어있다. 정남일선수의소속팀을알아내는 SQL 문 ( 서브쿼리부분 ) 과이결과를이용해서해당팀에소속된선수들의정보를출력하는 SQL 문 ( 메인쿼리부분 ) 으로구성된다. 정남일선수가소속된팀의선수들에대한정보를표시하는문제를서브쿼리방식의 SQL 문으로작성하면다음과같다. [ 예제 ] SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = ' 정남일 ') ORDER BY PLAYER_NAME; [ 실행결과 ] 선수명포지션백넘버 강철 DF 3 김반 MF 14 김영수 MF 30 김정래 GK 33 김창원 DF 5 김회택 TM 꼬레아 FW 16 노병준 MF 개의행이선택되었다. 정남일선수의소속팀을알아내는서브쿼리가먼저수행되어정남일선수의소속팀코드가반환된다. 메인쿼리는서브쿼리에서반환된결과를이용해서조건을만족하는선수들의정보를출력한다. 만약, 정남일선수가동명이인이었다면 2 건이상의결과가반환되어 SQL 문은오류가발생될것이다. 테이블전체에하나의그룹함수를적용할때는그결과값이 1 건이생성되기때문에단일행서브쿼리로서사용가능하다. 선수들중에서키가평균이하인선수들의정보를출력하는문제를가지고그룹함수를사용한서브쿼리를알아보도록한다. [ 그림 Ⅱ-2-14] 는 2 개의 SQL 문으로구성되어있다. 선수들의평균키를알아내는 SQL 문 ( 서브쿼리부분 ) 과이결과를이용해서키가평균이하의선수들의정보를출력하는 SQL 문 ( 메인쿼리부분 ) 으로구성된다. [ 그림 Ⅱ-2-14] 를 SQL 문으로작성하면다음과같다. [ 예제 ] SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER) ORDER BY PLAYER_NAME; [ 실행결과 ] 선수명포지션백넘버 가비 MF 10 강대희 MF 26 강용 DF 2 강정훈 MF 38 강철 DF 3 고규억 DF 29 고민기 FW 24 고종수 MF 개의행이선택되었다. 12. 다중행서브쿼리 서브쿼리의결과가 2 건이상반환될수있다면반드시다중행비교연산자 (IN, ALL, ANY, SOME) 와함께사용해야한다. 그렇지않으면 SQL 문은오류를반환한다. 다중행비교연산자는다음과같다.

32 선수들중에서 정현수 라는선수가소속되어있는팀정보를출력하는서브쿼리를작성하면다음과같다. [ 예제 ] SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = ' 정현수 ') ORDER BY TEAM_NAME; ORA-01427: 단일행하위질의에 2 개이상의행이리턴되었다. 위의 SQL 문은서브쿼리의결과로 2 개이상의행이반환되어단일행비교연산자인 '=' 로는처리가불가능하기때문에에러가반환되었다. 따라서다중행비교연산자로바꾸어서 SQL 문을작성하면다음과같다. [ 예제 ] SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = ' 정현수 ') ORDER BY TEAM_NAME; [ 실행결과 ] 연고지명팀명영문팀명 전남드래곤즈 CHUNNAM DRAGONS FC 성남일화천마 SEONGNAM ILHWA CHUNMA FC 2 개의행이선택되었다. 실행결과를보면 ' 정현수 ' 란이름을가진선수가두명이존재한다. 소속팀은각각전남드래곤즈팀 (K07) 과성남일화천마팀 (K08) 이다. 본예제에서는동명이인에대한내용을예로들었지만, 서브쿼리의실행결과가 2 건이상이나오는모든경우에다중행비교연산자를사용해야한다. 3. 다중칼럼서브쿼리 다중칼럼서브쿼리는서브쿼리의결과로여러개의칼럼이반환되어메인쿼리의조건과동시에비교되는것을의미한다. 소속팀별키가가장작은사람들의정보를출력하는문제를가지고다중칼럼서브쿼리를알아보도록한다. 소속팀별키가가장작은사람들의정보는 GROUP BY 를이용하여찾을수있으므로다음과같이 SQL 문을작성할수있다. [ 예제 ] SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID) ORDER BY TEAM_ID, PLAYER_NAME; [ 실행결과 ] 팀코드선수명포지션백넘버키 K01 마르코스 FW K01 박정수 MF K02 고창현 MF K02 정준 MF K03 김중규 MF 개의행이선택되었다.

33 SQL 문의실행결과를보면서브쿼리의결과값으로소속팀코드 (TEAM_ID) 와소속팀별가장작은키를의미하는 MIN(HEIGHT) 라는두개의칼럼을반환했다. 메인쿼리에서는조건절에 TEAM_ID 와 HEIGHT 칼럼을괄호로묶어서서브쿼리결과와비교하여원하는결과를얻었다. 실행결과에서보면하나팀에서키가제일작은선수한명씩만반환된것이아니라같은팀에서여러명이반환된것을확인할수있다. 이것은동일팀내에서조건 ( 팀별가장작은키 ) 을만족하는선수가여러명이존재하기때문이다. 그러나이기능은 SQL Server 에서는지원되지않는기능이다. 4. 연관서브쿼리 연관서브쿼리 (Correlated Subquery) 는서브쿼리내에메인쿼리칼럼이사용된서브쿼리이다. 선수자신이속한팀의평균키보다작은선수들의정보를출력하는 SQL 문을연관서브쿼리를이용해서작성해보면다음과같다 [ 예제 ] SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, M.BACK_NO 백넘버, M.HEIGHT 키 FROM PLAYER M, TEAM T WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < ( SELECT AVG(S.HEIGHT) FROM PLAYER S WHERE S.TEAM_ID = M.TEAM_ID AND S.HEIGHT IS NOT NULL GROUP BY S.TEAM_ID ) ORDER BY 선수명 ; [ 실행결과 ] 팀명선수명포지션백넘버키 삼성블루윙즈가비 MF 삼성블루윙즈강대희 MF 스틸러스강용 DF 시티즌강정훈 MF 드래곤즈강철 DF 현대모터스고관영 MF 현대모터스고민기 FW 삼성블루윙즈고종수 MF 의행이선택되었다. 예를들어, 가비선수는삼성블루윙즈팀소속이므로삼성블루윙즈팀소속의평균키를구하고그평균키와가비선수의키를비교하여적을경우에선수에대한정보를출력한다. 만약, 평균키보다선수의키가크거나같으면조건에맞지않기때문에해당데이터는출력되지않는다. 이와같은작업을메인쿼리에존재하는모든행에대해서반복수행한다. EXISTS 서브쿼리는항상연관서브쿼리로사용된다. 또한 EXISTS 서브쿼리의특징은아무리조건을만족하는건이여러건이더라도조건을만족하는 1 건만찾으면추가적인검색을진행하지않는다. 다음은 EXISTS 서브쿼리를사용하여 ' ' 부터 ' ' 사이에경기가있는경기장을조회하는 SQL 문이다. [ 예제 ] SELECT STADIUM_ID ID, STADIUM_NAME 경기장명 FROM STADIUM A WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN ' ' AND ' ') [ 실행결과 ] ID 경기장명 B01 인천월드컵경기장 B04 수원월드컵경기장 B05 서울월드컵경기장 C02 부산아시아드경기장 4 개의행이선택되었다. 5. 그밖에위치에서사용하는서브쿼리 가. SELECT 절에서브쿼리사용하기 다음은 SELECT 절에서사용하는서브쿼리인스칼라서브쿼리 (Scalar Subquery) 에대해서알아본다. 스칼라서브쿼리는한행, 한칼럼 (1 Row 1 Column) 만을반환하는서브쿼리를말한다. 스칼라서브쿼리는칼럼을쓸수있는대부분의곳에서사용할수있다. 선수정보와해당선수가속한팀의평균키를함께출력하는예제로스칼라서브쿼리를설명하면다음과같다.

34 [ 그림 Ⅱ-2-15] 는 2 개의 SQL 문으로구성되어있다. 선수들의정보를출력하는 SQL 문 ( 메인쿼리부분 ) 과해당선수의소속팀별평균키를알아내는 SQL 문 ( 서브쿼리부분 ) 으로구성된다. 여기서선수의소속팀별평균키를알아내는스칼라서브쿼리는메인쿼리의결과건수만큼반복수행된다. [ 그림 Ⅱ-2-15] 를 SQL 문으로작성하면다음과같다. [ 예제 ] SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키 FROM PLAYER P [ 실행결과 ] 선수명키팀평균키 가비 가이모토 강대희 강성일 강용 강정훈 강철 고관영 개의행이선택되었다. 스칼라서브쿼리또한단일행서브쿼리이기때문에결과가 2 건이상반환되면 SQL 문은오류를반환한다. 나. FROM 절에서서브쿼리사용하기 FROM 절에서사용되는서브쿼리를인라인뷰 (Inline View) 라고한다. FROM 절에는테이블명이오도록되어있다. 그런데서브쿼리가 FROM 절에사용되면어떻게될까? 서브쿼리의결과가마치실행시에동적으로생성된테이블인것처럼사용할수있다. 인라인뷰는 SQL 문이실행될때만임시적으로생성되는동적인뷰이기때문에데이터베이스에해당정보가저장되지않는다. 그래서일반적인뷰를정적뷰 (Static View) 라고하고인라인뷰를동적뷰 (Dynamic View) 라고도한다. 뷰에대해서는뒤에서좀더설명하기로한다. 인라인뷰는테이블명이올수있는곳에서사용할수있다. 서브쿼리의칼럼은메인쿼리에서사용할수없다고했다. 그러나인라인뷰는동적으로생성된테이블이다. 인라인뷰를사용하는것은조인방식을사용하는것과같다. 그렇기때문에인라인뷰의칼럼은 SQL 문자유롭게참조할수있다. K- 리그선수들중에서포지션이미드필더 (MF) 인선수들의소속팀명및선수정보를출력하고자한다. 인라인뷰를활용해서 SQL 문을만들어보자. [ 예제 ] SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명, P.BACK_NO 백넘버 FROM (SELECT TEAM_ID, PLAYER_NAME, BACK_NO FROM PLAYER WHERE POSITION = 'MF') P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID ORDER BY 선수명 ; [ 실행결과 ] 팀명선수명백넘버 삼성블루윙즈가비 10 삼성블루윙즈강대희 26 시티즌강정훈 38 현대모터스고관영 32 삼성블루윙즈고종수 22 삼성블루윙즈고창현 8 시티즌공오균 22 일화천마곽치국 개의행이선택되었다. SQL 문을보면선수들중에서포지션이미드필더 (MF) 선수들을인라인뷰를통해서추출하고인라인뷰의결과와 TEAM 테이블과조인해서팀명 (TEAM_NAME) 을출력하고있다. 인라인뷰에서는 ORDER BY 절을사용할수있다. 인라인뷰에먼저정렬을수행하고정렬된결과중에서일부데이

35 터를추출하는것을 TOP-N 쿼리라고한다. TOP-N 쿼리를수행하기위해서는정렬작업과정렬결과중에서일부데이터만을추출할수있는방법이필요하다. Oracle 에서는 ROWNUM 이라는연산자를통해서결과로추출하고자하는데이터건수를제약할수있다. [ 예제 ] Oracle SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC) WHERE ROWNUM <= 5; [ 예제 ] SQL Server SELECT TOP(5) PLAYER_NAME AS 선수명, POSITION AS 포지션, BACK_NO AS 백넘버, HEIGHT AS 키 FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC [ 실행결과 ] 선수명포지션백넘버키 서동명 GK 권정혁 GK 김석 FW 정경두 GK 이현 GK 개의행이선택되었다. 당 SQL 문의인라인뷰에서선수의키를내림차순으로정렬 ( 가장키가큰선수부터출력 ) 한후메인쿼리에서 ROWNUM 을사용해서 5 명의선수의정보만을추출하였다. 이것은모든선수들중에서가장키가큰 5 명의선수를출력한것이다. 만약, 다른선수중에서키가 192 인선수가더존재하더라도해당 SQL 문에서는데이터가출력되지않는다. 이런데이터까지추출하고자한다면분석함수의 RANK 관련함수를사용해야한다. 다. HAVING 절에서서브쿼리사용하기 HAVING 절은그룹함수와함께사용될때그룹핑된결과에대해부가적인조건을주기위해서사용한다. 평균키가삼성블루윙즈팀의평균키보다작은팀의이름과해당팀의평균키를구하는 SQL 문을작성하면다음과같다. [ 예제 ] SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키 FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID GROUP BY P.TEAM_ID, T.TEAM_NAME HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT) FROM PLAYER WHERE TEAM_ID ='K02') [ 실행결과 ] 팀코드팀명평균키 K13 강원 FC K15 대구 FC K11 경남 FC K14 제주유나이티드 FC K12 광주상무 K07 드래곤즈 K08 일화천마 K10 시티즌 개의행이선택되었다. 라. UPDATE 문의 SET 절에서사용하기 현재 TEAM 테이블에는 STADIUM_NAME 칼럼이없다. TEAM 테이블에 STADIUM_NAME 을추가 (ALTER TABLE ADD COLUMN) 하였다고가정하자. TEAM 테이블에추가된 STADIUM_NAME 의값을 STADIUM 테이블을이용하여변경하고자할때다음과같이 SQL 문을작성할수있다. UPDATE TEAM A SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME FROM STADIUM X WHERE X.STADIUM_ID = A.STADIUM_ID); 서브쿼리를사용한변경작업을할때서브쿼리의결과가 NULL 을반환할경우해당컬럼의결과가 NULL 이될수있기때문에주의해야한다. 마. INSERT 문의 VALUES 절에서사용하기 PLAYER 테이블에 ' 홍길동 ' 이라는선수를삽입하고자한다. 이때 PLAYER_ID 의값을현재사용중인 PLAYER_ID 에 1 을더한값으로넣고자한다. 다음과같이 SQL 문을 SQL 문을작성할수있다. INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID) VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), ' 홍길동 ', 'K06');

36 6. 뷰 (View) 테이블은실제로데이터를가지고있는반면, 뷰 (View) 는실제데이터를가지고있지않다. 뷰는단지뷰정의 (View Definition) 만을가지고있다. 질의에서뷰가사용되면뷰정의를참조해서 DBMS 내부적으로질의를재작성 (Rewrite) 하여질의를수행한다. 뷰는실제데이터를가지고있지않지만테이블이수행하는역할을수행하기때문에가상테이블 (Virtual Table) 이라고도한다. 뷰는 [ 표 Ⅱ -2-7] 과같은장점을갖는다. 뷰는다음과같이 CREATE VIEW 문을통해서생성할수있다. CREATE VIEW V_PLAYER_TEAM AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID; 해당뷰는선수정보와해당선수가속한팀명을함께추출하는것이다. 뷰의명칭은 'V_PLAYER_TEAM' 이다. 뷰는테이블뿐만아니라이미존재하는뷰를참조해서도생성할수있다. CREATE VIEW V_PLAYER_TEAM_FILTER AS SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_NAME FROM V_PLAYER_TEAM WHERE POSITION IN ('GK', 'MF'); V_PLAYER_TEAM_FILTER 뷰는이미앞에서생성했던 V_PLAYER_TEAM 뷰를기반으로해서생성된뷰다. V_PLAYER_TEAM_FILTER 뷰는선수포지션이골키퍼 (GK), 미드필더 (MF) 인선수만을추출하고자하는뷰이다.( 뷰를포함하는뷰를잘못생성하는경우성능상의문제를유발할수있으므로, 뷰와 SQL 의수행원리를잘이해하고사용하기바란다 ) 뷰를사용하기위해서는해당뷰의이름을이용하면된다. 뷰를사용하는방법은다음과같다. [ 예제 ] SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME FROM V_PLAYER_TEAM WHERE PLAYER_NAME LIKE ' 황 %' [ 실행결과 ] PLAYER_NAME POSITION BACK_NO TEAM ID TEAM_NAME 황철민 MF 35 K06 아이파크황승주 DF 98 K05 현대모터스황연석 FW 16 K08 일화천마 3 개의행이선택되었다. 이것은 V_PLAYER_TEAM 뷰에서성이 ' 황 ' 씨인선수만을추출하는 SQL 문이다. 결과로서 3 건이추출되었다. 뷰를사용하는경우에는 DBMS 가내부적으로 SQL 문을다음과같이재작성한다. SELECT PLAYER_NAME, POSITION, BACK_NO, TEAM_ID, TEAM_NAME FROM (SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME FROM PLAYER P, TEAM T WHERE P.TEAM_ID = T.TEAM_ID) WHERE PLAYER_NAME LIKE ' 황 %' 이것은앞에서설명했던인라인뷰와유사한모습임을알수있다. 이와같은형태로사용되기때문에뷰는데이터를저장하지않고도데이터를조회할수있다. 뷰를제거하기위해서는 DROP VIEW 문을사용한다. DROP VIEW V_PLAYER_TEAM; DROP VIEW V_PLAYER_TEAM_FILTER;

37 05. 그룹함수 1. 데이터분석개요 ANSI/ISO SQL 표준은데이터분석을위해서다음세가지함수를정의하고있다. - AGGREGATE FUNCTION - GROUP FUNCTION - WINDOW FUNCTION AGGREGATE FUNCTION GROUP AGGREGATE FUNCTION 이라고도부르며, GROUP FUNCTION 의한부분으로분류할수있다. 1 장 7 절에서설명한 COUNT, SUM, AVG, MAX, MIN 외각종집계함수들이포함되어있다. GROUP FUNCTION 결산개념의업무를가지는원가나판매시스템의경우는소계, 중계, 합계, 총합계등여러레벨의결산보고서를만드는것이중요업무중의하나이다. 개발자들이이런보고서를작성하기위해서는 SQL 이포함된 3GL 으로배치프로그램을작성하거나, 레벨별집계를위한여러단계의 SQL 을 UNION, UNION ALL 로묶은후하나의테이블을여러번읽어다시재정렬하는복잡한단계를거쳐야했다. 그러나그룹함수를사용한다면하나의 SQL 로테이블을한번만읽어서빠르게원하는리포트를작성할수있다. 추가로, 소계 / 합계를표시하기위해 GROUPING 함수와 CASE 함수를이용하면쉽게원하는포맷의보고서작성도가능하다. 그룹함수로는집계함수를제외하고, 소그룹간의소계를계산하는 ROLLUP 함수, GROUP BY 항목들간다차원적인소계를계산할수있는 CUBE 함수, 특정항목에대한소계를계산하는 GROUPING SETS 함수가있다. ROLLUP 은 GROUP BY 의확장된형태로사용하기가쉬우며병렬로수행이가능하기때문에매우효과적일뿐아니라시간및지역처럼계층적분류를포함하고있는데이터의집계에적합하도록되어있다. CUBE 는결합가능한모든값에대하여다차원적인집계를생성하게되므로 ROLLUP 에비해다양한데이터를얻는장점이있는반면에, 시스템에부하를많이주는단점이있다. GROUPING SETS 는원하는부분의소계만손쉽게추출할수있는장점이있다. ROLLUP, CUBE, GROUPING SETS 결과에대한정렬이필요한경우는 ORDER BY 절에정렬칼럼을명시해야한다. WINDOW FUNCTION 분석함수 (ANALYTIC FUNCTION) 나순위함수 (RANK FUNCTION) 로도알려져있는윈도우함수는데이터웨어하우스에서발전한기능이며, 자세한내용은다음절에서설명한다. 2. ROLLUP 함수 ROLLUP 에지정된 Grouping Columns 의 List 는 Subtotal 을생성하기위해사용되어지며, Grouping Columns 의수를 N 이라고했을때 N+1 Level 의 Subtotal 이생성된다. 중요한것은, ROLLUP 의인수는계층구조이므로인수순서가바뀌면수행결과도바뀌게되므로인수의순서에도주의해야한다. ROLLUP 과 CUBE 의효과를알아보기위해단계별로데이터를출력해본다. STEP 1. 일반적인 GROUP BY 절사용 [ 예제 ] 부서명과업무명을기준으로사원수와급여합을집계한일반적인 GROUP BY SQL 문장을수행한다. [ 예제 ] SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB; [ 실행결과 ] DNAME JOB Total Empl Total Sal SALES MANAGER SALES CLERK ACCOUNTING MANAGER RESEARCH ANALYST 2

38 6000 ACCOUNTING CLERK SALES SALESMAN RESEARCH MANAGER ACCOUNTING PRESIDENT RESEARCH CLERK 개의행이선택되었다. Oracle 을포함한일부 DBMS 의과거버전에서는 GROUP BY 절사용시자동적으로정렬을수행하였으나, 현재대부분의 DBMS 버전은집계기능만지원하고있으며정렬이필요한경우는 ORDER BY 절에명시적으로정렬칼럼이표시되어야한다. STEP 1-2. GROUP BY 절 + ORDER BY 절사용 [ 예제 ] 부서명과업무명을기준으로집계한일반적인 GROUP BY SQL 문장에 ORDER BY 절을사용함으로써부서, 업무별로정렬이이루어진다. [ 예제 ] SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB ORDER BY DNAME, JOB; [ 실행결과 ] DNAME JOB Total Empl Total Sal ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT RESEARCH ANALYST RESEARCH CLERK RESEARCH MANAGER SALES CLERK SALES MANAGER SALES SALESMAN 개의행이선택되었다. STEP 2. ROLLUP 함수사용 [ 예제 ] 부서명과업무명을기준으로집계한일반적인 GROUP BY SQL 문장에 ROLLUP 함수를사용한다. [ 예제 ] SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB); [ 실행결과 ] DNAME JOB Total Empl Total Sal SALES CLERK SALES MANAGER SALES SALESMAN SALES RESEARCH CLERK RESEARCH ANALYST RESEARCH MANAGER RESEARCH ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT ACCOUNTING 개의행이선택되었다. 실행결과에서 2 개의 GROUPING COLUMNS(DNAME, JOB) 에대하여다음과같은추가 LEVEL 의집계가생성된것을볼수있다. L1 - GROUP BY 수행시생성되는표준집계 (9 건 ) L2 - DNAME 별모든 JOB 의 SUBTOTAL (3 건 ) L3 - GRAND TOTAL ( 마지막행, 1 건 ) 추가로 ROLLUP 의경우계층간집계에대해서는 LEVEL 별순서 (L1 L2 L3) 를정렬하지만, 계층내 GROUP BY 수행시생성되는표준집계에는별도의정렬을지원하지않는다. L1, L2, L3 계층내정렬을위해서는별도의 ORDER BY 절을사용해야한다. STEP 2-2. ROLLUP 함수 + ORDER BY 절사용 [ 예제 ] 부서명과업무명을기준으로집계한일반적인 GROUP BY SQL 문장에 ROLLUP 함수를사용한다. 추가로 ORDER BY 절을사용해서부서, 업무별로정렬한다. [ 예제 ] SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB) ORDER BY DNAME, JOB ; [ 실행결과 ] DNAME JOB Total Empl Total Sal ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT ACCOUNTING RESEARCH ANALYST RESEARCH CLERK RESEARCH MANAGER RESEARCH SALES CLERK SALES MANAGER SALES SALESMAN SALES 개의행이선택되었다.

39 STEP 3. GROUPING 함수사용 ROLLUP, CUBE, GROUPING SETS 등새로운그룹함수를지원하기위해 GROUPING 함수가추가되었다. - ROLLUP 이나 CUBE 에의한소계가계산된결과에는 GROUPING(EXPR) = 1 이표시되고, - 그외의결과에는 GROUPING(EXPR) = 0 이표시된다. GROUPING 함수와 CASE/DECODE 를이용해, 소계를나타내는필드에원하는문자열을지정할수있어, 보고서작성시유용하게사용할수있다. [ 예제 ] ROLLUP 함수를추가한집계보고서에서집계레코드를구분할수있는 GROUPING 함수가추가된 SQL 문장이다. [ 예제 ] SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB); [ 실행결과 ] DNAME GROUPING(DNAME) JOB --- GROUPING(JOB) Total Empl Total Sal SALES 0 CLERK SALES 0 MANAGER SALES 0 SALESMAN SALES RESEARCH 0 CLERK RESEARCH 0 ANALYST RESEARCH 0 MANAGER RESEARCH ACCOUNTING 0 CLERK ACCOUNTING 0 MANAGER ACCOUNTING 0 PRESIDENT ACCOUNTING 개의행이선택되었다. 부서별, 업무별과전체집계를표시한레코드에서는 GROUPING 함수가 1 을리턴한것을확인할수있다. 그리고전체합계를나타내는결과라인에서는부서별 GROUPING 함수와업무별 GROUPING 함수가둘다 1 인것을알수있다. STEP 4. GROUPING 함수 + CASE 사용 [ 예제 ] ROLLUP 함수를추가한집계보고서에서집계레코드를구분할수있는 GROUPING 함수와 CASE 함수를함께사용한 SQL 문장을작성한다. [ 예제 ] SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME, CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB); Oracle 의경우는 DECODE 함수를사용해서좀더짧게표현할수있다. SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB); [ 실행결과 ] DNAME JOB Total Empl Total Sal SALES CLERK SALES MANAGER SALES SALESMAN SALES All Jobs RESEARCH CLERK RESEARCH ANALYST RESEARCH MANAGER RESEARCH All Jobs ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT ACCOUNTING All Jobs All Departments All Jobs 개의행이선택되었다. 부서별과전체집계를표시한레코드에서 ALL JOBS 와 ALL DEPARTMENTS 라는사용자정의텍스트를확인할수있다. 일부 DBMS 는 GROUPING_ID 라는비슷한용도의함수를추가로사용할수도있으므로참조하기바란다. STEP 4-2. ROLLUP 함수일부사용 [ 예제 ] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로변경한경우이다.

40 [ 예제 ] SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME, CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, ROLLUP(JOB) [ 실행결과 ] DNAME JOB Total Empl Total Sal SALES CLERK SALES MANAGER SALES SALESMAN SALES All Jobs RESEARCH CLERK RESEARCH ANALYST RESEARCH MANAGER RESEARCH All Jobs ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT ACCOUNTING All Jobs 개의행이선택되었다. 결과는마지막 ALL DEPARTMENTS & ALL JOBS 줄만계산이되지않았다. ROLLUP 이 JOB 칼럼에만사용되었기때문에 DNAME 에대한집계는필요하지않기때문이다. STEP 4-3. ROLLUP 함수결합칼럼사용 [ 예제 ] JOB 과 MGR 는하나의집합으로간주하고, 부서별, JOB & MGR 에대한 ROLLUP 결과를출력한다. [ 예제 ] SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, (JOB, MGR)); JOB, MGR 을소계시하나의집합으로간주하여구분하지않음 [ 실행결과 ] DNAME JOB MGR Total Sal SALES CLERK SALES MANAGER SALES SALESMAN SALES 9400 RESEARCH CLERK RESEARCH CLERK RESEARCH ANALYST RESEARCH MANAGER RESEARCH ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT 5000 ACCOUNTING 개의행이선택되었다. ROLLUP 함수사용시괄호로묶은 JOB 과 MGR 의경우하나의집합 (JOB+MGR) 칼럼으로간주하여괄호내각칼럼별집계를구하지않는다. 3. CUBE 함수 ROLLUP 에서는단지가능한 Subtotal 만을생성하였지만, CUBE 는결합가능한모든값에대하여다차원집계를생성한다. CUBE 를사용할경우에는내부적으로는 Grouping Columns 의순서를바꾸어서또한번의 Query 를추가수행해야한다. 뿐만아니라 Grand Total 은양쪽의 Query 에서모두생성이되므로한번의 Query 에서는제거되어야만하므로 ROLLUP 에비해시스템의연산대상이많다. 이처럼 Grouping Columns 이가질수있는모든경우에대하여 Subtotal 을생성해야하는경우에는 CUBE 를사용하는것이바람직하나, ROLLUP 에비해시스템에많은부담을주므로사용에주의해야한다. CUBE 함수의경우표시된인수들에대한계층별집계를구할수있으며, 이때표시된인수들간에는계층구조인 ROLLUP 과는달리평등한관계이므로인수의순서가바뀌는경우행간에정렬순서는바뀔수있어도데이터결과는같다. 그리고 CUBE 도결과에대한정렬이필요한경우는 ORDER BY 절에명시적으로정렬칼럼이표시가되어야한다. STEP 5. CUBE 함수이용 [ 예제 ] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, JOB) 조건으로변경해서수행한다.

41 [ 예제 ] SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME, CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY CUBE (DNAME, JOB) ; [ 실행결과 ] DNAME JOB Total Empl Total Sal All Departments All Jobs All Departments CLERK All Departments ANALYST All Departments MANAGER All Departments SALESMAN All Departments PRESIDENT SALES All Jobs SALES CLERK SALES MANAGER SALES SALESMAN RESEARCH All Jobs RESEARCH CLERK RESEARCH ANALYST RESEARCH MANAGER ACCOUNTING All Jobs ACCOUNTING CLERK ACCOUNTING MANAGER ACCOUNTING PRESIDENT 개의행이선택되었다. CUBE 는 GROUPING COLUMNS 이가질수있는모든경우의수에대하여 Subtotal 을생성하므로 GROUPING COLUMNS 의수가 N 이라고가정하면, 2 의 N 승 LEVEL 의 Subtotal 을생성하게된다. 실행결과에서 CUBE 함수사용으로 ROLLUP 함수의결과에다업무별집계까지추가해서출력할수있는데, ROLLUP 함수에비해업무별집계를표시한 5 건의레코드가추가된것을확인할수있다. (All Departments - CLERK, ANALYST, MANAGER, SALESMAN, PRESIDENT 별집계가 5 건추가되었다.) STEP 5-2. UNION ALL 사용 SQL UNION ALL 은 Set Operation 내용으로, 여러 SQL 문장을연결하는역할을할수있다. 위 SQL 은첫번째 SQL 모듈부터차례대로결과가나오므로위 CUBE SQL 과결과데이터는같으나행들의정렬은다를수있다. [ 예제 ] SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB UNION ALL SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME UNION ALL SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY JOB UNION ALL SELECT 'All Departments', 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO ; CUBE 함수를사용하면서가장크게개선되는부분은 CUBE 사용전 SQL 에서 EMP, DEPT 테이블을네번이나반복액세스하는부분을 CUBE 사용 SQL 에서는한번으로줄일수있는부분이다. 기존에같은테이블을네번액세스하는이유가되었던부서와업무별소계와총계부분을 CUBE 함수를사용함으로써한번의액세스만으로구현한다. 결과적으로수행속도및자원사용율을개선할수있으며, SQL 문장도더짧아졌으므로가독성도높아졌다. 실행결과는 STEP5 의결과와동일하다. ROLLUP 함수도똑같은개선효과를얻을수있다. 4. GROUPING SETS 함수 GROUPING SETS 를이용해더욱다양한소계집합을만들수있는데, GROUP BY SQL 문장을여러번반복하지않아도원하는결과를쉽게얻을수있게되었다. GROUPING SETS 에표시된인수들에대한개별집계를구할수있으며, 이때표시된인수들간에는계층구조인 ROLLUP 과는달리평등한관계이므로인수의순서가바뀌어도결과는같다. 그리고 GROUPING SETS 함수도결과에대한정렬이필요한경우는 ORDER BY 절에명시적으로정렬칼럼이표시가되어야한다. 일반그룹함수를이용한 SQL [ 예제 ] 일반그룹함수를이용하여부서별, JOB 별인원수와급여합을구하라. [ 예제 ] SELECT DNAME, 'All Jobs' JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME UNION ALL SELECT 'All Departments'

42 DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY JOB ; [ 실행결과 ] DNAME JOB Total Empl Total Sal ACCOUNTING All Jobs RESEARCH All Jobs SALES All Jobs All Departments CLERK All Departments SALESMAN All Departments PRESIDENT All Departments MANAGER All Departments ANALYST 개의행이선택되었다. 실행결과는별도의 ORDER BY 조건을명시하지않았기때문에 DNAME 이나 JOB 에대해서정렬이되어있지않다. GROUPING SETS 사용 SQL [ 예제 ] 일반그룹함수를 GROUPING SETS 함수로변경하여부서별, JOB 별인원수와급여합을구하라. [ 예제 ] SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY GROUPING SETS (DNAME, JOB); [ 실행결과 ] DNAME JOB Total Empl Total Sal All Departments CLERK All Departments SALESMAN All Departments PRESIDENT All Departments MANAGER All Departments ANALYST ACCOUNTING All Jobs RESEARCH All Jobs SALES All Jobs 개의행이선택되었다. GROUPING SETS 함수사용시 UNION ALL 을사용한일반그룹함수를사용한 SQL 과같은결과를얻을수있으며, 괄호로묶은집합별로 ( 괄호내는계층구조가아닌하나의데이터로간주함 ) 집계를구할수있다. GROUPING SETS 의경우일반그룹함수를이용한 SQL 과결과데이터는같으나행들의정렬순서는다를수있다. GROUPING SETS 사용 SQL - 순서변경 [ 예제 ] 일반그룹함수를 GROUPING SETS 함수로변경하여부서별, JOB 별인원수와급여합을구하는데 GROUPING SETS 의인수들의순서를바꾸어본다. [ 예제 ] SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY GROUPING SETS (JOB, DNAME); [ 실행결과 ] DNAME JOB Total Empl Total Sal All Departments CLERK All Departments SALESMAN All Departments PRESIDENT All Departments MANAGER All Departments ANALYST ACCOUNTING All Jobs RESEARCH All Jobs SALES All Jobs 개의행이선택되었다. GROUPING SETS 인수들은평등한관계이므로인수의순서가바뀌어도결과는같다. (JOB 과 DNAME 의순서가바뀌었지만결과는같다.) 3 개의인수를이용한 GROUPING SETS 이용 [ 예제 ] 부서-JOB-매니저별집계와, 부서-JOB 별집계와, JOB-매니저별집계를 GROUPING SETS 함수를이용해서구해본다. [ 예제 ] SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR)); GROUPING SETS 함수사용시괄호로묶은집합별로 ( 괄호내는계층구조가아닌하나의데이터로간주함 ) 집계를구할수있다. [ 실행결과 ] DNAME JOB MGR Total Sal SALES CLERK ACCOUNTING CLERK RESEARCH CLERK RESEARCH CLERK RESEARCH ANALYST SALES MANAGER RESEARCH MANAGER 7839

43 2975 ACCOUNTING MANAGER SALES SALESMAN ACCOUNTING PRESIDENT 5000 CLERK CLERK CLERK CLERK ANALYST MANAGER SALESMAN PRESIDENT 5000 SALES MANAGER 2850 SALES CLERK 950 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 RESEARCH MANAGER 2975 SALES SALESMAN 5600 RESEARCH ANALYST 6000 RESEARCH CLERK 개의행이선택되었다. 실행결과에서첫번째 10 건의데이터는 (DNAME+JOB+MGR) 기준의집계이며, 두번째 8 건의데이터는 (JOB+MGR) 기준의집계이며, 세번째 9 건의데이터는 (DNAME+JOB) 기준의집계이다.

44 06. 윈도우함수 1. WINDOW FUNCTION 개요 기존관계형데이터베이스는칼럼과칼럼간의연산, 비교, 연결이나집합에대한집계는쉬운반면, 행과행간의관계를정의하거나, 행과행간을비교, 연산하는것을하나의 SQL 문으로처리하는것은매우어려운문제였다. PL/SQL, SQL/PL, T-SQL, PRO*C 같은절차형프로그램을작성하거나, INLINE VIEW 를이용해복잡한 SQL 문을작성해야하던것을부분적이나마행과행간의관계를쉽게정의하기위해만든함수가바로 WINDOW FUNCTION 이다. 윈도우함수를활용하면복잡한프로그램을하나의 SQL 문장으로쉽게해결할수있다. 분석함수 (ANALYTIC FUNCTION) 나순위함수 (RANK FUNCTION) 로도알려져있는윈도우함수 (ANSI/ISO SQL 표준은 WINDOW FUNCTION 이란용어를사용함 ) 는데이터웨어하우스에서발전한기능이다. SQL 사용자입장에서는 INLINE VIEW 이후 SQL 의중요한기능이추가되었다고할수있으며, 많은프로그램이나튜닝팁을대체할수있을것이다. 복잡하거나자원을많이사용하는튜닝기법들을대체할수있는 DBMS 의새로운기능은튜닝관점에서도최적화된방법이므로적극적으로활용할필요가있다. 같은결과가나오는변형된튜닝문장보다는 DBMS 벤더에서최적화된자원을사용하도록만들어진새로운기능을사용하는것이일반적으로더욱효과가좋기때문이다. WINDOW 함수는기존에사용하던집계함수도있고, 새로이 WINDOW 함수전용으로만들어진기능도있다. 그리고 WINDOW 함수는다른함수와는달리중첩 (NEST) 해서사용하지는못하지만, 서브쿼리에서는사용할수있다. WINDOW FUNCTION 종류 WINDOW FUNCTION 의종류는크게다섯개의그룹으로분류할수있는데벤더별로지원하는함수에는차이가있다. 첫번째, 그룹내순위 (RANK) 관련함수는 RANK, DENSE_RANK, ROW_NUMBER 함수가있다. ANSI/ISO SQL 표준과 Oracle, SQL Server 등대부분의 DBMS 에서지원하고있다. 두번째, 그룹내집계 (AGGREGATE) 관련함수는일반적으로많이사용하는 SUM, MAX, MIN, AVG, COUNT 함수가있다. ANSI/ISO SQL 표준과 Oracle, SQL Server 등대부분의 DBMS 에서지원하고있는데, SQL Server 의경우집계함수는뒤에서설명할 OVER 절내의 ORDER BY 구문을지원하지않는다. 세번째, 그룹내행순서관련함수는 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가있다. Oracle 에서만지원되는함수이기는하지만, FIRST_VALUE, LAST_VALUE 함수는 MAX, MIN 함수와비슷한결과를얻을수있고, LAG, LEAD 함수는 DW 에서유용하게사용되는기능이므로같이설명하도록한다. 네번째, 그룹내비율관련함수는 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수가있다. CUME_DIST, PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS 에서지원하고있으며, NTILE 함수는 ANSI/ISO SQL 표준에는없지만, Oracle, SQL Server 에서지원하고있다. 마지막으로 RATIO_TO_REPORT 함수는 Oracle 에서만지원되는함수이기는하나, 현업에서유용한기능을구현하는데참조하기위해설명하도록한다. 다섯번째, 선형분석을포함한통계분석관련함수가있는데, 통계에특화된기능이므로본가이드에서는설명을생략한다. 아래는 Oracle 의통계관련함수를참조로표시한것이다.

45 CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY WINDOW FUNCTION SYNTAX - WINDOW 함수에는 OVER 문구가키워드로필수포함된다. SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼 ] [ORDER BY 절 ] [WINDOWING 절 ] ) FROM 테이블명 ; - WINDOW_FUNCTION : 기존에사용하던함수도있고, 새롭게 WINDOW 함수용으로추가된함수도있다. - ARGUMENTS ( 인수 ) : 함수에따라 0 ~ N 개의인수가지정될수있다. - PARTITION BY 절 : 전체집합을기준에의해소그룹으로나눌수있다. - ORDER BY 절 : 어떤항목에대해순위를지정할지 ORDER BY 절을기술한다. - WINDOWING 절 : WINDOWING 절은함수의대상이되는행기준의범위를강력하게지정할수있다. ROWS 는물리적인결과행의수를, RANGE 는논리적인값에의한범위를나타내는데, 둘중의하나를선택해서사용할수있다. 다만, WINDOWING 절은 SQL Server 에서는지원하지않는다. BETWEEN 사용타입 ROWS RANGE BETWEEN UNBOUNDED PRECEDING CURRENT ROW VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING CURRENT ROW VALUE_EXPR PRECEDING/FOLLOWING BETWEEN 미사용타입 ROWS RANGE UNBOUNDED PRECEDING CURRENT ROW VALUE_EXPR PRECEDING 2. 그룹내순위함수 가. RANK 함수 RANK 함수는 ORDER BY 를포함한 QUERY 문에서특정항목 ( 칼럼 ) 에대한순위를구하는함수이다. 이때특정범위 (PARTITION) 내에서순위를구할수도있고전체데이터에대한순위를구할수도있다. 또한동일한값에대해서는동일한순위를부여하게된다. [ 예제 ] 사원데이터에서급여가높은순서와 JOB 별로급여가높은순서를같이출력한다. [ 예제 ] SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP; [ 실행결과 ] JOB ENAME SAL ALL_RANK JOB_RANK PRESIDENT KING ANALYST FORD ANALYST SCOTT MANAGER JONES MANAGER BLAKE MANAGER CLARK SALESMAN ALLEN SALESMAN TURNER CLERK MILLER SALESMAN WARD SALESMAN MARTIN CLERK ADAMS CLERK JAMES CLERK SMITH 개의행이선택되었다. 업무구분이없는 ALL_RANK 칼럼에서 FORD 와 SCOTT, WARD 와 MARTIN 은동일한 SALARY 이므로같은순위를부여한다. 그리고업무를 PARTITION 으로구분한 JOB_RANK 의경우같은업무내범위에서만순위를부여한다. 하나의 SQL 문장에 ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이충돌이났기때문에 JOB 별로는정렬이되지않고, ORDER BY SAL DESC 조건으로정렬이되었다. [ 예제 ] 앞의 SQL 문의결과는 JOB 과 SALARY 기준으로정렬이되어있지않다. 새로운 SQL 에서는전체 SALARY 순위를구하는 ALL_RANK 칼럼은제외하고, 업무별로 SALARY 순서를구하는 JOB_RANK 만알아보도록한다.

46 [ 예제 ] SELECT JOB, ENAME, SAL, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP; [ 실행결과 ] JOB ENAME SAL JOB_RANK ANALYST FORD ANALYST SCOTT CLERK MILLER CLERK ADAMS CLERK JAMES CLERK SMITH MANAGER JONES MANAGER BLAKE MANAGER CLARK PRESIDENT KING SALESMAN ALLEN SALESMAN TURNER SALESMAN MARTIN SALESMAN WARD 개의행이선택되었다. 업무별로 SALARY 순서를구하는 JOB_RANK 만사용한경우파티션의기준이된 JOB 과 SALARY 별로정렬이되어있는것을알수있다. 나. DENSE_RANK 함수 DENSE_RANK 함수는 RANK 함수와흡사하나, 동일한순위를하나의건수로취급하는것이틀린점이다. [ 예제 ] 사원데이터에서급여가높은순서와, 동일한순위를하나의등수로간주한결과도같이출력한다. [ 예제 ] SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) RANK, DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK FROM EMP; [ 실행결과 ] JOB ENAME SAL RANK DENSE_RANK PRESIDENT KING ANALYST FORD ANALYST SCOTT MANAGER JONES MANAGER BLAKE MANAGER CLARK SALESMAN ALLEN SALESMAN TURNER CLERK MILLER SALESMAN WARD SALESMAN MARTIN CLERK ADAMS CLERK JAMES CLERK SMITH 개의행이선택되었다. FORD 와 SCOTT, WARD 와 MARTIN 은동일한 SALARY 이므로 RANK 와 DENSE_RANK 칼럼에서모두같은순위를부여한다. 그러나 RANK 와 DENSE_RANK 의차이를알수있는데이터는 FORD 와 SCOTT 의다음순위인 JONES 의경우 RANK 는 4 등으로 DENSE_RANK 는 3 등으로표시되어있다. 마찬가지로 WARD 와 MARTIN 의다음순위인 ADAMS 의경우 RANK 는 12 등으로 DENSE_RANK 는 10 등으로표시되어있다. 다. ROW_NUMBER 함수 ROW_NUMBER 함수는 RANK 나 DENSE_RANK 함수가동일한값에대해서는동일한순위를부여하는데반해, 동일한값이라도고유한순위를부여한다. [ 예제 ] 사원데이터에서급여가높은순서와, 동일한순위를인정하지않는등수도같이출력한다. [ 예제 ] SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) RANK, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER FROM EMP; [ 실행결과 ] JOB ENAME SAL RANK ROW_NUMBER PRESIDENT KING ANALYST FORD ANALYST SCOTT MANAGER JONES MANAGER BLAKE MANAGER CLARK SALESMAN ALLEN SALESMAN TURNER CLERK MILLER SALESMAN WARD SALESMAN MARTIN CLERK ADAMS CLERK JAMES CLERK SMITH 개의행이선택되었다. FORD 와 SCOTT, WARD 와 MARTIN 은동일한 SALARY 이므로 RANK 는같은순위를부여했지만, ROW_NUMBER 의경우동일한순위를배제하기위해유니크한순위를정한다. 위경우는같은 SALARY 에서는어떤순서가정해질지알수없다. (Oracle 의경우 rowid 가적은행이먼저나온

47 다 ) 이부분은데이터베이스별로틀린결과가나올수있으므로, 만일동일값에대한순서까지관리하고싶으면 ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을이용해추가적인정렬기준을정의해야한다. 3. 일반집계함수 가. SUM 함수 SUM 함수를이용해파티션별윈도우의합을구할수있다. [ 예제 ] 사원들의급여와같은매니저를두고있는사원들의 SALARY 합을구한다. [ 예제 ] SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP; PARTITION BY MGR 구문을통해매니저별로데이터를파티션화한다. [ 실행결과 ] MGR ENAME SAL MGR_SUM FORD SCOTT JAMES ALLEN WARD TURNER MARTIN MILLER ADAMS BLAKE JONES CLARK SMITH KING 개의행이선택되었다. [ 예제 ] OVER 절내에 ORDER BY 절을추가해파티션내데이터를정렬하고이전 SALARY 데이터까지의누적값을출력한다. (SQL Server 의경우집계함수의경우 OVER 절내의 ORDER BY 절을지원하지않는다.) [ 예제 ] SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM FROM EMP RANGE UNBOUNDED PRECEDING : 현재행을기준으로파티션내의첫번째행까지의범위를지정한다. [ 실행결과 ] MGR ENAME SAL MGR_SUM SCOTT FORD JAMES WARD * MARTIN * TURNER ALLEN MILLER ADAMS CLARK BLAKE JONES SMITH KING 개의행이선택되었다. * 표시된 7699-WARD 와 7699-MARTIN 의급여가같으므로, 같은 ORDER 로취급하여 =3450 의값이되었다 TURNER 의경우 =4950 의누적합을가진다. 나. MAX 함수 MAX 함수를이용해파티션별윈도우의최대값을구할수있다. [ 예제 ] 사원들의급여와같은매니저를두고있는사원들의 SALARY 중최대값을같이구한다. [ 예제 ] SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX FROM EMP; [ 실행결과 ] MGR ENAME SAL MGR_MAX FORD SCOTT JAMES ALLEN WARD TURNER MARTIN MILLER ADAMS BLAKE JONES CLARK SMITH KING 개의행이선택되었다. 실행결과를확인하면파티션내의최대값을파티션내모든행에서 MGR_MAX 라는칼럼값으로가질수있다. [ 예제 ] 추가로, INLINE VIEW 를이용해파티션별최대값을가진행만추출할수도있다.

48 [ 예제 ] SELECT MGR, ENAME, SAL FROM (SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL FROM EMP) WHERE SAL = IV_MAX_SAL ; [ 실행결과 ] MGR ENAME SAL FORD SCOTT ALLEN MILLER ADAMS JONES SMITH 800 KING 개의행이선택되었다. 실행결과를보면 MGR 7566 의 SCOTT, FORD 는같은최대값을가지므로, WHERE SAL = IV_MAX_SAL 조건에의해두건모두추출되었다. 다. MIN 함수 MIN 함수를이용해파티션별윈도우의최소값을구할수있다. [ 예제 ] 사원들의급여와같은매니저를두고있는사원들을입사일자를기준으로정렬하고, SALARY 최소값을같이구한다. [ 예제 ] SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP; [ 실행결과 ] MGR ENAME HIREDATE SAL MGR_MIN FORD SCOTT ALLEN WARD TURNER MARTIN JAMES MILLER ADAMS JONES BLAKE CLARK SMITH KING 개의행이선택되었다. 라. AVG 함수 AVG 함수와파티션별 ROWS 윈도우를이용해원하는조건에맞는데이터에대한통계값을구할수있다. [ 예제 ] EMP 테이블에서같은매니저를두고있는사원들의평균 SALARY 를구하는데, 조건은같은매니저내에서자기바로앞의사번과바로뒤의사번인직원만을대상으로한다. [ 예제 ] SELECT MGR, ENAME, HIREDATE, SAL, ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG FROM EMP; ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재행을기준으로파티션내에서앞의한건, 현재행, 뒤의한건을범위로지정한다. (ROWS 는현재행의앞뒤건수를말하는것임 ) [ 실행결과 ] MGR ENAME HIREDATE SAL MGR_AVG FORD SCOTT ALLEN WARD TURNER MARTIN JAMES MILLER ADAMS JONES BLAKE CLARK SMITH KING 개의행이선택되었다. 실행결과에서 ALLEN 의경우파티션내에서첫번째데이터이므로앞의한건은평균값집계대상이없다. 결과적으로평균값집계대상은본인의데이터와뒤의한건으로평균값을구한다. ( ) / 2 = 1425 의값을가진다. TURNER 의경우앞의한건과, 본인의데이터와, 뒤의한건으로평균값을구한다. ( ) / 3 = 1333 의값을가진다. JAMES 의경우파티션내에서마지막데이터이므로뒤의한건을제외한, 앞의한건과본인의데이터를가지고평균값을구한다. ( ) / 2 = 1100 의값을가진다.

49 마. COUNT 함수 COUNT 함수와파티션별 ROWS 윈도우를이용해원하는조건에맞는데이터에대한통계값을구할수있다. [ 예제 ] 사원들을급여기준으로정렬하고, 본인의급여보다 50 이하가적거나 150 이하로많은급여를받는인원수를출력하라. [ 예제 ] SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT FROM EMP; RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재행의급여값을기준으로급여가 -50 에서 +150 의범위내에포함된모든행이대상이된다. (RANGE 는현재행의데이터값을기준으로앞뒤데이터값의범위를표시하는것임 ) [ 실행결과 ] ENAME SAL SIM_CNT ( 범위값 ) SMITH ( 750~ 950) JAMES ( 900~1100) ADAMS ** (1050~1250) WARD (1200~1400) MARTIN (1200~1400) MILLER (1250~1450) TURNER (1450~1650) ALLEN (1550~1750) CLARK (2400~2600) BLAKE (2800~3000) JONES (2925~3125) SCOTT (2950~3100) FORD (2950~3100) KING (4950~5100) 14 개의행이선택되었다. 위 SQL 문장은파티션이지정되지않았으므로모든건수를대상으로 -50 ~ +150 기준에맞는지검사하게된다. ORDER BY SAL 로정렬이되어있으므로비교연산이쉬워진다. ** 표시된 ADAMS 의경우자기가가지고있는 SALARY 1100 을기준으로 -50 에서 +150 까지값을가진 1050 에서 1250 까지의값을가진 JAMES(950), ADAMS(1100), WARD(1250) 3 명의데이터건수를구할수있다. 4. 그룹내행순서함수 가. FIRST_VALUE 함수 FIRST_VALUE 함수를이용해파티션별윈도우에서가장먼저나온값을구한다. SQL Server 에서는지원하지않는함수이다. MIN 함수를활용하여같은결과를얻을수도있다. [ 예제 ] 부서별직원들을연봉이높은순서부터정렬하고, 파티션내에서가장먼저나온값을출력한다. [ 예제 ] SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH FROM EMP; RANGE UNBOUNDED PRECEDING : 현재행을기준으로파티션내의첫번째행까지의범위를지정한다. [ 실행결과 ] DEPTNO ENAME SAL DEPT_RICH KING 5000 KING 10 CLARK 2450 KING 10 MILLER 1300 KING 20 SCOTT * 3000 SCOTT 20 FORD * 3000 SCOTT 20 JONES 2975 SCOTT 20 ADAMS 1100 SCOTT 20 SMITH 800 SCOTT 30 BLAKE 2850 BLAKE 30 ALLEN 1600 BLAKE 30 TURNER 1500 BLAKE 30 MARTIN 1250 BLAKE 30 WARD 1250 BLAKE 30 JAMES 950 BLAKE 14 개의행이선택되었다. 실행결과를보면같은부서내에최고급여를받는사람이둘있는경우, 즉, * 표시가있는부서번호 20 의 SCOTT 과 FORD 중에서어느사람이최고급여자로선택될지는위의 SQL 문만가지고는판단할수없다. FIRST_VALUE 는다른함수와달리공동등수를인정하지않고처음나온행만을처리한다. 위처럼공동등수가있을경우에의도적으로세부항목을정렬하고싶다면별도의정렬조건을가진 INLINE VIEW 를사용하거나, OVER () 내의 ORDER BY 절에칼럼을추가해야한다. [ 예제 ] 앞의 SQL 문장에서같은값을가진 FIRST_VALUE 를처리하기위해 ORDER BY 정렬조건을추가한다.

50 [ 예제 ] SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP FROM EMP; [ 실행결과 ] DEPTNO ENAME SAL RICH_EMP KING 5000 KING 10 CLARK 2450 KING 10 MILLER 1300 KING 20 FORD 3000 FORD 20 SCOTT 3000 FORD 20 JONES 2975 FORD 20 ADAMS 1100 FORD 20 SMITH 800 FORD 30 BLAKE 2850 BLAKE 30 ALLEN 1600 BLAKE 30 TURNER 1500 BLAKE 30 MARTIN 1250 BLAKE 30 WARD 1250 BLAKE 30 JAMES 950 BLAKE 14 개의행이선택되었다. SQL 에서같은부서내에최고급여를받는사람이둘있는경우를대비해서이름을두번째정렬조건으로추가한다. 실행결과를확인하면부서번호 20 의최고급여자가이전의 SCOTT 값에서 ASCII 코드가적은값인 FORD 로변경된것을확인할수있다. 나. LAST_VALUE 함수 LAST_VALUE 함수를이용해파티션별윈도우에서가장나중에나온값을구한다. SQL Server 에서는지원하지않는함수이다. MAX 함수를활용하여같은결과를얻을수도있다. [ 예제 ] 부서별직원들을연봉이높은순서부터정렬하고, 파티션내에서가장마지막에나온값을출력한다. [ 예제 ] SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR FROM EMP; ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재행을포함해서파티션내의마지막행까지의범위를지정한다. [ 실행결과 ] DEPTNO ENAME SAL DEPT_POOR KING 5000 MILLER 10 CLARK 2450 MILLER 10 MILLER 1300 MILLER 20 SCOTT 3000 SMITH 20 FORD 3000 SMITH 20 JONES 2975 SMITH 20 ADAMS 1100 SMITH 20 SMITH 800 SMITH 30 BLAKE 2850 JAMES 30 ALLEN 1600 JAMES 30 TURNER 1500 JAMES 30 MARTIN 1250 JAMES 30 WARD 1250 JAMES 30 JAMES 950 JAMES 14 개의행이선택되었다. 실행결과에서 LAST_VALUE 는다른함수와달리공동등수를인정하지않고가장나중에나온행만을처리한다. 만일공동등수가있을경우를의도적으로정렬하고싶다면별도의정렬조건을가진 INLINE VIEW 를사용하거나, OVER () 내의 ORDER BY 조건에칼럼을추가해야한다. 다. LAG 함수 LAG 함수를이용해파티션별윈도우에서이전몇번째행의값을가져올수있다. SQL Server 에서는지원하지않는함수이다. [ 예제 ] 직원들을입사일자가빠른기준으로정렬을하고, 본인보다입사일자가한명앞선사원의급여를본인의급여와함께출력한다. [ 예제 ] SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' ; [ 실행결과 ] ENAME HIREDATE SAL PREV_SAL ALLEN WARD TURNER MARTIN 개의행이선택되었다. [ 예제 ] LAG 함수는 3 개의 ARGUMENTS 까지사용할수있는데, 두번째인자는몇번째앞의행을가져올지결정하는것이고 (DEFAULT 1), 세번째인자는예를들어파티션의첫번째행의경우가져올데이터가없어 NULL 값이들어오는데이경우다른값으로바꾸어줄수있다. 결과적으로 NVL 이나 ISNULL 기능과같다.

51 [ 예제 ] SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' LAG(SAL, 2, 0) 의기능은두행앞의 SALARY 를가져오고, 가져올값이없는경우는 0 으로처리한다. [ 실행결과 ] ENAME HIREDATE SAL PREV_SAL ALLEN WARD TURNER MARTIN 개의행이선택되었다. 라. LEAD 함수 LEAD 함수를이용해파티션별윈도우에서이후몇번째행의값을가져올수있다. 참고로 SQL Server 에서는지원하지않는함수이다. [ 예제 ] 직원들을입사일자가빠른기준으로정렬을하고, 바로다음에입사한인력의입사일자를함께출력한다. [ 예제 ] SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" FROM EMP; [ 실행결과 ] ENAME HIREDATE NEXTHIRED ALLEN WARD TURNER MARTIN 개의행이선택되었다. LEAD 함수는 3 개의 ARGUMENTS 까지사용할수있는데, 두번째인자는몇번째후의행을가져올지결정하는것이고 (DEFAULT 1), 세번째인자는예를들어파티션의마지막행의경우가져올데이터가없어 NULL 값이들어오는데이경우다른값으로바꾸어줄수있다. 결과적으로 NVL 이나 ISNULL 기능과같다. 5. 그룹내비율함수 가. RATIO_TO_REPORT 함수 RATIO_TO_REPORT 함수를이용해파티션내전체 SUM( 칼럼 ) 값에대한행별칼럼값의백분율을소수점으로구할수있다. 결과값은 > 0 & <= 1 의범위를가진다. 그리고개별 RATIO 의합을구하면 1 이된다. SQL Server 에서는지원하지않는함수이다. [ 예제 ] JOB 이 SALESMAN 인사원들을대상으로전체급여에서본인이차지하는비율을출력한다. [ 예제 ] SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R FROM EMP WHERE JOB = 'SALESMAN'; [ 실행결과 ] ENAME SAL R_R ALLEN (1600 / 5600) WARD (1250 / 5600) MARTIN (1250 / 5600) TURNER / 5600) 4 개의행이선택되었다. 실행결과에서전체값은 = 5600 이되고, RATIO_TO_REPORT 함수연산의분모로사용된다. 그리고개별 RATIO 의전체합을구하면 1 이되는것을확인할수있다 = 1 나. PERCENT_RANK 함수 PERCENT_RANK 함수를이용해파티션별윈도우에서제일먼저나오는것을 0 으로, 제일늦게나오는것을 1 로하여, 값이아닌행의순서별백분율을구한다. 결과값은 >= 0 & <= 1 의범위를가진다. 참고로 SQL Server 에서는지원하지않는함수이다.

52 [ 예제 ] 같은부서소속사원들의집합에서본인의급여가순서상몇번째위치쯤에있는지 0 과 1 사이의값으로출력한다. [ 예제 ] SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R FROM EMP; [ 실행결과 ] DEPTNO ENAME SAL P_R KING CLARK MILLER SCOTT FORD JONES ADAMS SMITH BLAKE ALLEN TURNER MARTIN WARD JAMES 개의행이선택되었다. DEPTNO 10 의경우 3 건이므로구간은 2 개가된다. 0 과 1 사이를 2 개의구간으로나누면 0, 0.5, 1 이된다. DEPTNO 20 의경우 5 건이므로구간은 4 개가된다. 0 과 1 사이를 4 개의구간으로나누면 0, 0.25, 0.5, 0.75, 1 이된다. DEPTNO 30 의경우 6 건이므로구간은 5 개가된다. 0 과 1 사이를 5 개의구간으로나누면 0, 0.2, 0.4, 0.6, 0.8, 1 이된다. SCOTT, FORD 와 WARD, MARTIN 의경우 ORDER BY SAL DESC 구문에의해급여가같으므로같은 ORDER 로취급한다. 다. CUME_DIST 함수 CUME_DIST 함수를이용해파티션별윈도우의전체건수에서현재행보다작거나같은건수에대한누적백분율을구한다. 결과값은 > 0 & <= 1 의범위를가진다. 참고로 SQL Server 에서는지원하지않는함수이다. [ 예제 ] 같은부서소속사원들의집합에서본인의급여가누적순서상몇번째위치쯤에있는지 0 과 1 사이의값으로출력한다. [ 예제 ] SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST FROM EMP; [ 실행결과 ] DEPTNO ENAME SAL CUME_DIST KING CLARK MILLER SCOTT * FORD * JONES ADAMS SMITH BLAKE ALLEN TURNER MARTIN ** WARD ** JAMES 개의행이선택되었다. DEPTNO 가 10 인경우윈도우가전체 3 건이므로 단위의간격을가진다. 즉, , , 1 의값이된다. DEPTNO 가 20 인경우윈도우가전체 5 건이므로 단위의간격을가진다. 즉, , , , , 1 의값이된다. DEPTNO 가 30 인경우윈도우가전체 6 건이므로 단위의간격을가진다. 즉, , , , , , 1 의값이된다. * 표시가있는 SCOTT, FORD 와 ** 표시가있는 WARD, MARTIN 의경우 ORDER BY SAL 에의해 SAL 이같으므로같은 ORDER 로취급한다. 다른 WINDOW 함수의경우동일순서면앞행의함수결과값을따르는데, CUME_DIST 의경우는동일순서면뒤행의함수결과값을기준으로한다. 라. NTILE 함수 NTILE 함수를이용해파티션별전체건수를 ARGUMENT 값으로 N 등분한결과를구할수있다. [ 예제 ] 전체사원을급여가높은순서로정렬하고, 급여를기준으로 4 개의그룹으로분류한다. [ 예제 ] SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE FROM EMP [ 실행결과 ] DEPTNO ENAME SAL QUAR_TILE KING FORD SCOTT JONES BLAKE CLARK ALLEN

53 TURNER MILLER WARD MARTIN ADAMS JAMES SMITH 개의행이선택되었다. 위예제에서 NTILE(4) 의의미는 14 명의팀원을 4 개조로나눈다는의미이다. 전체 14 명을 4 개의집합으로나누면몫이 3 명, 나머지가 2 명이된다. 나머지두명은앞의조부터할당한다. 즉, 4 명 + 4 명 + 3 명 + 3 명으로조를나누게된다.

54 07.DCL 1. DCL 개요 지금까지살펴본 SQL 문장을분류하면테이블생성과조작에관련된명령어 (DDL) 와, 데이터를조작하기위한명령어 (DML), 그리고 TRANSACTION 을제어하기위한명령어 (TCL) 이다. 추가로, 이런명령어들이외에도유저를생성하고권한을제어할수있는 DCL(DATA CONTROL LANGUAGE) 명령어가있다. 2. 유저와권한 다른부서간에또는다른회사간에데이터를공유하기위해데이터베이스를오픈해야하는경우가가끔발생한다. 물론데이터베이스를오픈하는것자체가문제가될수있다. 즉, 운영시스템에서사용하던유저를오픈하면데이터의손실우려가너무커지게되는것이다. 이런경우에새로운유저를생성하고, 생성한유저에게공유할테이블이나기타오브젝트에대한접근권한만을부여한다면문제는쉽게해결할수있다. 일반적으로회원제웹사이트를방문하여서비스를이용하려면먼저회원가입을해야한다. 유저아이디, 패스워드, 기타개인정보를입력하고약관에동의하면회원가입이된다. 그리고유저아이디와패스워드로로그인하면웹사이트의서비스를이용할수있게된다. 그러나영화나유료게임과같은특정컨텐츠를이용하려면 권한이없다 라는메시지를볼수있다. 여기서유저아이디와패스워드를유저라할수있고, 유료서비스에대한결재여부를권한이라할수있다. 대부분의데이터베이스는데이터보호와보안을위해서유저와권한을관리하고있는데, 예를들어 Oracle 을설치하면기본적으로제공되는유저들인 SYS, SYSTEM, SCOTT 유저에대해서 [ 표 Ⅱ- 2-8] 을통해서간단하게알아본다. Oracle 과 SQL Server 의사용자에대한아키텍처는다른면이많다. Oracle 은유저를통해데이터베이스에접속을하는형태이다. 즉, 아이디와비밀번호방식으로인스턴스에접속을하고그에해당하는스키마에오브젝트생성등의권한을부여받게된다. SQL Server 는인스턴스에접속하기위해로그인이라는것을생성하게되며, 인스턴스내에존재하는다수의데이터베이스에연결하여작업하기위해유저를생성한후로그인과유저를매핑해주어야한다. 더나아가특정유저는특정데이터베이스내의특정스키마에대해권한을부여받을수있다. SQL Server 로그인은두가지방식으로가능하다. 첫번째, Windows 인증방식으로 Windows 에로그인한정보를가지고 SQL Server 에접속하는방식이다. Microsoft Windows 사용자계정을통해연결되면 SQL Server 는운영체제의 Windows 보안주체토큰을사용하여계정이름과암호가유효한지확인한다. 즉, Windows 에서사용자 ID 를확인한다. SQL Server 는암호를요청하지않으며 ID 의유효성검사를수행하지않

55 는다. Windows 인증은기본인증모드이며 SQL Server 인증보다훨씬더안전하다. Windows 인증은 Kerberos 보안프로토콜을사용하고, 암호정책을적용하여강력한암호에대해적합한복잡성수준을유지하도록하며, 계정잠금및암호만료를지원한다. SQL Server 가 Windows 에서제공하는자격증명을신뢰하므로 Windows 인증을사용한연결을트러스트된연결이라고도한다. 두번째, 혼합모드 (Windows 인증또는 SQL 인증 ) 방식으로기본적으로 Windows 인증으로도 SQL Server 에접속가능하며, Oracle 의인증과같은방식으로사용자아이디와비밀번호로 SQL Server 에접속하는방식이다. SQL 인증을사용할때는강력한암호 ( 숫자 + 문자 + 특수문자등을혼합하여사용 ) 를사용해야한다. 예를들어, 아래 [ 그림 Ⅱ-1-16] 을보면 SCOTT 이라는 LOGIN 이름으로인스턴스 INST1 에접속을하여미리매핑되어있는 SCOTT 이라는유저를통해 PRODUCT 라는스키마에속해있는 ITEM 이라는테이블의데이터를액세스하고있다. 가. 유저생성과시스템권한부여 유저를생성하고데이터베이스에접속한다. 하지만데이터베이스에접속했다고해서테이블, 뷰, 인덱스등과같은오브젝트 (OBJECT) 를생성할수는없다. 사용자가실행하는모든 DDL 문장 (CREATE, ALTER, DROP, RENAME 등 ) 은그에해당하는적절한권한이있어야만문장을실행할수있다. 이러한권한을시스템권한이라고하며약 100 개이상의종류가있다. 일반적으로시스템권한은일일이유저에게부여되지않는다. 100 개이상의시스템권한을일일이사용자에게설정하는것은너무복잡하고, 특히유저로부터권한을관리하기가어렵기때문이다. 그래서롤 (ROLE) 을이용하여간편하고쉽게권한을부여하게된다. 롤에대한자세한설명은차후에하도록하고먼저유저를생성하고권한을부여한다. 새로운유저를생성하려면일단유저생성권한 (CREATE USER) 이있어야한다. [ 예제 ] SCOTT 유저로접속한다음 PJS 유저 ( 패스워드 : KOREA7) 를생성해본다. [ 예제 ] Oracle CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; CREATE USER PJS IDENTIFIED BY KOREA7; * 1 행에오류 : ERROR: 권한이불충분하다 현재 SCOTT 유저는유저를생성할권한을부여받지못했기때문에권한이불충분하다는오류가발생한다. Oracle 의 DBA 권한을가지고있는 SYSTEM 유저로접속하면유저생성권한 (CREATE USER) 을다른유저에게부여할수있다. [ 예제 ] SCOTT 유저에게유저생성권한 (CREATE USER) 을부여한후다시 PJS 유저를생성한다. [ 예제및실행결과 ] Oracle GRANT CREATE USER TO SCOTT; 권한이부여되었다. CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가생성되었다. SQL Server 는유저를생성하기전먼저로그인을생성해야한다. 로그인을생성할수있는권한을가진로그인은기본적으로 sa 이다.

56 [ 예제 ] sa 로로그인을한후 SQL 인증을사용하는 PJS 라는로그인 ( 패스워드 : KOREA7) 을생성해본다. 로그인후최초로접속할데이터베이스는 AdventureWorks 데이터베이스로설정한다. [ 예제및실행결과 ] SQL Server CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks SQL Server 에서의유저는데이터베이스마다존재한다. 그러므로유저를생성하기위해서는생성하고자하는유저가속할데이터베이스로이동을한후처리해야한다. [ 예제및실행결과 ] SQL Server USE ADVENTUREWORKS; GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo; [ 예제 ] 생성된 PJS 유저로로그인한다. [ 예제및실행결과 ] Oracle CONN PJS/KOREA7; 오류 : ERROR: 사용자 PJS 는 CREATE SESSION 권한을가지고있지않음 ; 로그온이거절되었다. PJS 유저가생성됐지만아무런권한도부여받지못했기때문에로그인을하면 CREATE SESSION 권한이없다는오류가발생한다. 유저가로그인을하려면 CREATE SESSION 권한을부여받아야한다. [ 예제 ] PJS 유저가로그인할수있도록 CREATE SESSION 권한을부여한다. [ 예제및실행결과 ] Oracle CONN SCOTT/TIGER 연결되었다. GRANT CREATE SESSION TO PJS; 권한이부여되었다. CONN PJS/KOREA7 연결되었다. [ 예제 ] PJS 유저로테이블을생성한다. [ 예제및실행결과 ] Oracle SELECT * FROM TAB; 선택된레코드가없다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) ); CREATE TABLE MENU ( * 1 행에오류 : ERROR: 권한이불충분하다. [ 예제및실행결과 ] SQL Server CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 데이터베이스 AdventureWorks' 에서 CREATE TABLE 사용권한이거부되었다. PJS 유저는로그인권한만부여되었기때문에테이블을생성하려면테이블생성권한 (CREATE TABLE) 이불충분하다는오류가발생한다.(Oracle, SQL Server) [ 예제 ] SYSTEM 유저를통하여 PJS 유저에게 CREATE TABLE 권한을부여한후다시테이블을생성한다. [ 예제및실행결과 ] Oracle CONN SYSTEM/MANAGER 연결되었다. GRANT CREATE TABLE TO PJS; 권한이부여되었다. CONN PJS/KOREA7 연결되었다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) ); 테이블이생성되었다. [ 예제및실행결과 ] SQL Server GRANT CREATE TABLE TO PJS; 권한이부여되었다. 스키마에권한을부여한다. GRANT Control ON SCHEMA::dbo TO PJS 권한이부여되었다. PJS 로로그인한다. CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 테이블이생성되었다. 나. OBJECT 에대한권한부여 앞에서 PJS 유저를생성하여로그인하고테이블을만드는과정에서몇가지의권한에대해서살펴보았다. 이제는특정유저가소유한객체 (OBJECT) 권한에대해알아본다. 오브젝트권한은특정오브젝트인테이블, 뷰등에대한 SELECT, INSERT, DELETE, UPDATE 작업명령어를의미한다. [ 표 Ⅱ-2-9] 는오브젝트권한과오브젝트와의관계를보여주고있다.

57 앞에서 PJS 유저가생성한 MENU 테이블을 SCOTT 유저를통해서조회하면어떻게될까? SCOTT, PJS 뿐만아니라모든유저는각각자신이생성한테이블외에다른유저의테이블에접근하려면해당테이블에대한오브젝트권한을소유자로부터부여받아야한다. 우리가남의집에방문했을때집주인의허락없이는집에들어갈수없는것과같은이치이다. SQL Server 도같은방식으로동작한다. 한가지다른점은위에서언급했듯이유저는단지스키마에대한권한만을가진다. 다시말하면테이블과같은오브젝트는유저가소유하는것이아니고스키마가소유를하게되며유저는스키마에대해특정한권한을가지는것이다. 먼저 SCOTT 유저로접속하여 PJS.MENU 테이블을조회한다. 다른유저가소유한객체에접근하기위해서는객체앞에객체를소유한유저의이름을붙여서접근해야한다. SQL Server 는객체앞에소유한유저의이름을붙이는것이아니고객체가속한스키마이름을붙여야한다. [ 예제및실행결과 ] Oracle CONN SCOTT/TIGER 연결되었다. SELECT * FROM PJS.MENU; SELECT * FROM PJS.MENU * 1 행에오류 : ERROR: 테이블또는뷰가존재하지않는다. [ 예제및실행결과 ] SQL Server SCOTT 로로그인한다. SELECT * FROM dbo.menu; 개체이름 dbo.menu' 이 ( 가 ) 잘못되었다. SCOTT 유저는 PJS 유저로부터 MENU 테이블을 SELECT 할수있는권한을부여받지못했기때문에 MENU 테이블을조회할수없다. [ 예제 ] PJS 유저로접속하여 SCOTT 유저에게 MENU 테이블을 SELECT 할수있는권한을부여한다.

58 [ 예제및실행결과 ] Oracle CONN PJS/KOREA7 연결되었다. INSERT INTO MENU VALUES (1, ' 화이팅 '); 1 개의행이만들어졌다. COMMIT; 커밋이완료되었다. GRANT SELECT ON MENU TO SCOTT; 권한이부여되었다. [ 예제및실행결과 ] SQL Server PJS 로로그인한다. INSERT INTO MENU VALUES (1, ' 화이팅 '); 1 개의행이만들어졌다. GRANT SELECT ON MENU TO SCOTT; 권한이부여되었다. 다시한번 SCOTT 유저로접속하여 PJS.MENU 테이블을조회한다. 이제 PJS.MENU 테이블을 SELECT 하면테이블자료를볼수있다. SCOTT 유저는 PJS.MENU 테이블을 SELECT 하는권한만부여받았기때문에 UPDATE, INSERT, DELETE 와같은다른작업을할수없다. 오브젝트권한은 SELECT, INSERT, DELETE, UPDATE 등의권한을따로따로관리한다. [ 예제 ] PJS.MENU 테이블에 UPDATE 를시도한다. [ 예제및실행결과 ] Oracle CONN SCOTT/TIGER 연결되었다. SELECT * FROM PJS.MENU; MENU_SEQ TITLE 화이팅 UPDATE PJS.MENU SET TITLE = ' 코리아 ' WHERE MENU_SEQ = 1; UPDATE PJS.MENU * 1 행에오 =text> [ 예제및실행결과 ] SQL Server SCOTT 으로로그인한다. SELECT * FROM PJS.MENU; MENU_SEQ TITLE 화이팅 UPDATE PJS.MENU SET TITLE = ' 코리아 ' WHERE MENU_SEQ = 1; 개체 MENU', 데이터베이스 AdventureWorks', 스키마 dbo' 에대한 UPDATE 권한이거부되었다. 권한이부족하여 UPDATE 를할수없다는오류가나타난다. PJS 유저에게 UPDATE 권한을부여한후다시시도하면업데이트가가능하다. 3. Role 을이용한권한부여 유저를생성하면기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등많은권한을부여해야한다. 데이터베이스관리자는유저가생성될때마다각각의권한들을유저에게부여하는작업을수행해야하며간혹권한을빠뜨릴수도있으므로각유저별로어떤권한이부여되었는지를관리해야한다. 하지만관리해야할유저가점점늘어나고자주변경되는상황에서는매우번거로운작업이될것이다. 이와같은문제를줄이기위하여많은데이터베이스에서유저들과권한들사이에서중개역할을하는 ROLE 을제공한다. 데이터베이스관리자는 ROLE 을생성하고, ROLE 에각종권한들을부여한후 ROLE 을다른 ROLE 이나유저에게부여할수있다. 또한 ROLE 에포함되어있는권한들이필요한유저에게는해당 ROLE 만을부여함으로써빠르고정확하게필요한권한을부여할수있게된다. [ 그림 Ⅱ-2-17] 에서는유저들과권한들사이간 ROLE 의역할을보여주고있다. 왼쪽그림은권한을직접유저에게할당할때를나타내는것이며, 오른쪽그림은 ROLE 에권한을부여한후 ROLE 을유저들에게부여하는것을나타내고있다.

59 ROLE 에는시스템권한과오브젝트권한을모두부여할수있으며, ROLE 은유저에게직접부여될수도있고, 다른 ROLE 에포함하여유저에게부여될수도있다. [ 예제 ] JISUNG 유저에게 CREATE SESSION 과 CREATE TABLE 권한을가진 ROLE 을생성한후 ROLE 을이용하여다시권한을할당한다. 권한을취소할때는 REVOKE 를사용한다. [ 예제및실행결과 ] Oracle CONN SYSTEM/MANAGER 연결되었다. REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG; 권한이취소되었다. CONN JISUNG/KOREA7 ERROR: 사용자 JISUNG 은 CREATE SESSION 권한을가지고있지않음. 로그온이거절되었다. [ 예제및실행결과 ] SQL Server sa 로로그인한다. REVOKE CREATE TABLE FROM PJS; 권한이취소되었다. PJS 로로그인한다. CREATE TABLE MENU ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) ); 데이터베이스 AdventureWorks' 에서 CREATE TABLE 사용권한이거부되었다. [ 예제 ] 이제 LOGIN_TABLE 이라는 ROLE 을만들고, 이 ROLE 을이용하여 JISUNG 유저에게권한을부여한다. [ 예제및실행결과 ] Oracle CONN SYSTEM/MANAGER 연결되었다. CREATE ROLE LOGIN_TABLE; 롤이생성되었다. GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 권한이부여되었다. GRANT LOGIN_TABLE TO JISUNG; 권한이부여되었다. CONN JISUNG/KOREA7 연결되었다. CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이생성되었다. 이와같이 ROLE 을만들어사용하는것이권한을직접부여하는것보다빠르고안전하게유저를관리할수있는방법이다. Oracle 에서는기본적으로몇가지 ROLE 을제공하고있다. 그중가장많이사용하는 ROLE 은 CONNECT 와 RESOURCE 이다. 참조를위해 [ 표 Ⅱ-2-11] 은 CONNECT 와 RESOURCE ROLE 에부여된권한목록을정리한것이다. CONNECT 는 CREATE SESSION 과같은로그인권한이포함되어있고, RESOURCE 는 CREATE TABLE 과같은오브젝트의생성권한이포함되어있다. 일반적으로유저를생성할때 CONNECT 와 RESOURCE ROLE 을사용하여기본권한을부여한다. 유저를삭제하는명령어는 DROP USER 이고, CASCADE 옵션을주면해당유저가생성한오브젝트를먼저삭제한후유저를삭제한다. [ 예제 ] 앞에서 MENU 라는테이블을생성했기때문에 CASCADE 옵션을사용하여 JISUNG 유저를삭제한후, 유저재생성및기본적인 ROLE 을부여한다. [ 예제및실행결과 ] Oracle CONN SYSTEM/MANAGER 연결되었다. DROP USER JISUNG CASCADE; 사용자가삭제되었다. JISUNG 유저가만든 MENU 테이블도같이삭제되었다. CREATE USER JISUNG IDENTIFIED BY KOREA7; 사용자가생성되었다. GRANT CONNECT, RESOURCE TO JISUNG; 권한이부여되었다. CONN JISUNG/KOREA7 연결되었다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이생성되었다.

60 SQL Server 에서는위와같이 ROLE 을생성하여사용하기보다는기본적으로제공되는 ROLE 에멤버로참여하는방식으로사용한다. 특정로그인이멤버로참여할수있는서버수준역할 (ROLE) 은 [ 표 Ⅱ-2-12] 와같다. 데이터베이스에존재하는유저에대해서는아래와같은데이터베이스역할의멤버로참여할수있다. SQL Server 에서는 Oracle 과같이 Role 을자주사용하지않는다. 대신위에서언급한서버수준역할및데이터베이스수준역할을이용하여로그인및사용자권한을제어한다. 인스턴스수준의

61 작업이필요한경우서버수준역할을부여하고그보다작은개념인데이터베이스수준의권한이필요한경우데이터베이스수준의역할을부여하면된다. 즉, 인스턴스수준을요구하는로그인에는서버수준역할을, 데이터베이스수준을요구하는사용자에게는데이터베이스수준역할을부여한다.

62 08. 절차형 SQL 1. 절차형 SQL 개요 일반적인개발언어처럼 SQL 에도절차지향적인프로그램이가능하도록 DBMS 벤더별로 PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의절차형 SQL 을제공하고있다. 절차형 SQL 을이용하면 SQL 문의연속적인실행이나조건에따른분기처리를이용하여특정기능을수행하는저장모듈을생성할수있다. 본절에서는절차형 SQL 을이용하여만들수있는저장모듈인 Procedure, User Defined Function, Trigger 에대해서간단하게살펴본다. ( 상세한내역은각 DBMS 벤더의매뉴얼을참조한다.) 2. PL/SQL 개요 가. PL/SQL 특징 Oracle 의 PL/SQL 은 Block 구조로되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고절차형언어 (IF, LOOP) 등을사용할수있으며, 절차적프로그래밍을가능하게하는트랜잭션언어이다. 이런 PL/SQL 을이용하여다양한저장모듈 (Stored Module) 을개발할수있다. 저장모듈이란 PL/SQL 문장을데이터베이스서버에저장하여사용자와애플리케이션사이에서공유할수있도록만든일종의 SQL 컴포넌트프로그램이며, 독립적으로실행되거나다른프로그램으로부터실행될수있는완전한실행프로그램이다. Oracle 의저장모듈에는 Procedure, User Defined Function, Trigger 가있다. PL/SQL 의특징은다음과같다. - PL/SQL 은 Block 구조로되어있어각기능별로모듈화가가능하다. - 변수, 상수등을선언하여 SQL 문장간값을교환한다. - IF, LOOP 등의절차형언어를사용하여절차적인프로그램이가능하도록한다. - DBMS 정의에러나사용자정의에러를정의하여사용할수있다. - PL/SQL 은 Oracle 에내장되어있으므로 Oracle 과 PL/SQL 을지원하는어떤서버로도프로그램을옮길수있다. - PL/SQL 은응용프로그램의성능을향상시킨다. - PL/SQL 은여러 SQL 문장을 Block 으로묶고한번에 Block 전부를서버로보내기때문에통신량을줄일수있다. [ 그림 Ⅱ-2-18] 은 PL/SQL Architecture 이다. PL/SQL Block 프로그램을입력받으면 SQL 문장과프로그램문장을구분하여처리한다. 즉프로그램문장은 PL/SQL 엔진이처리하고 SQL 문장은 Oracle 서버의 SQL Statement Executor 가실행하도록작업을분리하여처리한다.

63 나. PL/SQL 구조 다음은 PL/SQL 의블록구조를표현한내용이다. - DECLARE : BEGIN ~ END 절에서사용될변수와인수에대한정의및데이터타입을선언하는선언부이다. - BEGIN ~ END : 개발자가처리하고자하는 SQL 문과여러가지비교문, 제어문을이용하여필요한로직을처리하는실행부이다. - EXCEPTION : BEGIN ~ END 절에서실행되는 SQL 문이실행될때에러가발생하면그에러를어떻게처리할것이지를정의하는예외처리부이다. 다. PL/SQL 기본문법 (Syntax) 앞으로살펴볼 User Defined Function 이나 Trigger 의생성방법이나사용목적은다르지만기본적인문법은비슷하기때문에여기에서는 Stored Procedure 를통해서 PL/SQL 에대한기본적인문법을정리한다. CREATE [OR REPLACE] Procedure [Procedure_name] ( argument1 [mode] data_type1, argument2 [mode] date_type2, ) IS [AS] BEGIN EXCEPTION END; / 다음은생성된프로시저를삭제하는명령어이다. DROP Procedure [Procedure_name]; CREATE TABLE 명령어로테이블을생성하듯 CREATE 명령어로데이터베이스내에프로시저를생성할수있다. 이렇게생성한프로시저는데이터베이스내에저장된다. 프로시저는개발자가자주실행해야하는로직을절차적인언어를이용하여작성한프로그램모듈이기때문에필요할때호출하여실행할수있다. [OR REPLACE] 절은데이터베이스내에같은이름의프로시저가있을경우, 기존의프로시저를무시하고새로운내용으로덮어쓰기하겠다는의미이다. Argument 는프로시저가호출될때프로시저안으로어떤값이들어오거나혹은프로시저에서처리한결과값을운영체제로리턴시킬매개변수를지정할때사용한다. [mode] 부분에지정할수있는매개변수의유형은 3 가지가있다. 먼저 IN 은운영체제에서프로시저로전달될변수의 MODE 이고, OUT 은프로시저에서처리된결과가운영체제로전달되는 MODE 이다. 마지막으로잘쓰지는않지만 INOUT MODE 가있는데이 MODE 는 IN 과 OUT 두가지의기

64 능을동시에수행하는 MODE 이다. 마지막에있는슬래쉬 ( / ) 는데이터베이스에게프로시저를컴파일하라는명령어이다. 앞에서잠깐언급했지만 PL/SQL 과관련된내용은상당히다양하고분량이많기때문에본가이드에서는간단한문법과사용목적에초점을맞춰이해하기바란다. 3. T-SQL 개요 가. T-SQL 특징 T-SQL 은근본적으로 SQL Server 를제어하기위한언어로서, T-SQL 은엄격히말하면, MS 사에서 ANSI/ISO 표준의 SQL 에약간의기능을더추가해보완적으로만든것이다. T-SQL 을이용하여다양한저장모듈 (Stored Module) 을개발할수있는데, T-SQL 의프로그래밍기능은아래와같다. - 이라는전역변수 ( 시스템함수 ) 이라는지역변수가있다. - 지역변수는사용자가자신의연결시간동안만사용하기위해만들어지는변수이며전역변수는이미 SQL 서버에내장된값이다. - 데이터유형 (Data Type) 을제공한다. 즉 int, float, varchar 등의자료형을의미한다. - 연산자 (Operator) 산술연산자 ( +, -, *, /) 와비교연산자 (=, <, >, <>) 논리연산자 (and, or, not) 사용이가능하다. - 흐름제어기능 IF-ELSE 와 WHILE, CASE-THEN 사용이가능하다. - 주석기능한줄주석 : -- 뒤의내용은주석범위주석 : /* 내용 */ 형태를사용하며, 여러줄도가능함 T-SQL 과타 DBMS 가제공하는 SQL 은약간만다를뿐그맥락은같이하기때문에, 조금의변경사항만적용하면같은기능을수행할수있다. 그리고많은사람들이 SQL 서버에엔터프라이즈매니저의 UI 를통하여접근하는경우가많은데, 실제로는 SQL 또는 T-SQL 구문을사용하여접근하는것이더바람직하다. 나. T-SQL 구조 다음은 T-SQL 의구조를표현한내용이다. PL/SQL 과유사하다. - DECLARE : BEGIN ~ END 절에서사용될변수와인수에대한정의및데이터타입을선언하는선언부이다. - BEGIN ~ END : 개발자가처리하고자하는 SQL 문과여러가지비교문, 제어문을이

65 용하여필요한로직을처리하는실행부이다. T-SQL 에서는 BEGIN, END 문을반드시사용해야하는것은아니지만블록단위로처리하고자할때는반드시작성해야한다. - ERROR 처리 : BEGIN ~ END 절에서실행되는 SQL 문이실행될때에러가발생하면그에러를어떻게처리할것이지를정의하는예외처리부이다. 다. T-SQL 기본문법 (Syntax) 앞으로살펴볼 User Defined Function 이나 Trigger 의생성방법과사용목적은 Stored Procedure 와다르지만기본적인문법은비슷하기때문에여기에서는 Stored Procedure 를통해서 T-SQL 에대한기본적인문법을정리한다. CREATE Procedure data_type1 date_type2 [mode], WITH AS BEGIN ERROR 처리 END; 다음은생성된프로시저를삭제하는명령어이다. DROP Procedure [schema_name.]procedure_name; CREATE TABLE 명령어로테이블을생성하듯 CREATE 명령어로데이터베이스내에프로시저를생성할수있다. 이렇게생성한프로시저는데이터베이스내에저장된다. 프로시저는개발자가자주실행해야하는로직을절차적인언어를이용하여작성한프로그램모듈이기때문에필요할때호출하여실행할수있다. 프로시저의변경이필요할경우 Oracle 은 [CREATE OR REPLACE] 와같이하나의구문으로처리하지만 SQL Server 는 CREATE 구문을 ALTER 는프로시저가호출될때프로시저안으로어떤값이들어오거나혹은프로시저에서처리한결과값을리턴시킬매개변수를지정할때사용한다. [mode] 부분에지정할수있는매개변수 (@parameter) 의유형은 4 가지가있다. 1 VARYING 결과집합이출력매개변수로사용되도록지정합니다. CURSOR 매개변수에만적용된다. 2 DEFAULT 지정된매개변수가프로시저를호출할당시지정되지않을경우지정된기본값으로처리한다. 즉, 기본값이지정되어있으면해당매개변수를지정하지않아도프로시저가지정된기본값으로정상적으로수행이된다. 3 OUT, OUTPUT 프로시저에서처리된결과값을 EXECUTE 문호출시반환한다. 4 READONLY 자주사용되지는않는다. 프로시저본문내에서매개변수를업데이트하거나수정할수없음을나타낸다. 매개변수유형이사용자정의테이블형식인경우 READONLY 를지정해야한다. WITH 부분에지정할수있는옵션은 3 가지가있다. 1 RECOMPILE 데이터베이스엔진에서현재프로시저의계획을캐시하지않고프로시저가런타임에컴파일된다. 데이터베이스엔진에서저장프로시저안에있는개별쿼리에대한계획을삭제하려할때 RECOMPILE 쿼리힌트를사용한다. 2 ENCRYPTIONCREATE PROCEDURE 문의원본텍스트가알아보기어려운형식으로변환된다. 변조된출력은 SQL Server 의카탈로그뷰어디에서도직접표시되지않는다. 원본을볼수있는방법이없기때문에반드시원본은백업을해두어야한다. 3 EXECUTE AS 해당저장프로시저를실행할보안컨텍스트를지정한다. 앞에서잠깐언급했지만 T-SQL 과관련된내용은상당히다양하고분량이많기때문에본가이드에서는간단한문법과사용목적에초점을맞춰이해하기바란다. 4. Procedure 의생성과활용 [ 그림 Ⅱ-2-21] 은앞으로생성할 Procedure 의기능을 Flow Chart 로나타낸그림이다.

66 [ 예제 ] SCOTT 유저가소유하고있는 DEPT 테이블에새로운부서를등록하는 Procedure 를작성한다. SCOTT 유저가기본적으로소유한 DEPT 테이블의구조는 [ 표 Ⅱ-2-14] 와같다. [ 예제 ] Oracle CREATE OR REPLACE Procedure p_dept_insert ( v_deptno in number, v_dname in varchar2, v_loc in varchar2, v_result out varchar2) IS cnt number := 0; BEGIN SELECT COUNT(*) INTO CNT FROM DEPT WHERE DEPTNO = v_deptno AND ROWNUM = 1; if cnt > 0 then v_result := ' 이미등록된부서번호이다 '; else INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (v_deptno, v_dname, v_loc); COMMIT; v_result := ' 입력완료!!'; end if; EXCEPTION WHEN OTHERS THEN ROLLBACK; v_result := 'ERROR 발생 '; END; / [ 예제 ] SQL Server CREATE Procedure dbo.p_dept_insert varchar(100) OUTPUT AS int = 0 BEGIN FROM DEPT WHERE DEPTNO > BEGIN = ' 이미등록된부서번호이다 ' RETURN END ELSE BEGIN BEGIN TRAN INSERT INTO DEPT (DEPTNO, DNAME, LOC) BEGIN ROLLBACK = 'ERROR 발생 ' RETURN END ELSE BEGIN COMMIT = ' 입력완료!!' RETURN END END END DEPT 테이블은 DEPTNO 칼럼이 PRIMARY KEY 로설정되어있으므로, DEPTNO 칼럼에는유일한값을넣어야만한다. [ 예제 ] 에대한설명은다음과같다.

67 1 DEPT 테이블에들어갈칼럼값 ( 부서코드, 부서명, 위치 ) 을입력받는다. 2 입력받은부서코드가존재하는지확인한다. 3 부서코드가존재하면 ' 이미등록된부서번호입니다 ' 라는메시지를출력값에넣는다. 4 부서코드가존재하지않으면입력받은필드값으로새로운부서레코드를입력한다. 5 새로운부서가정상적으로입력됐을경우에는 COMMIT 명령어를통해서트랜잭션을종료한다. 6 에러가발생하면모든트랜잭션을취소하고 'ERROR 발생 ' 라는메시지를출력값에넣는다. 앞에있는프로시저를작성하면서주의해야할몇가지문법적요소가있다. 첫째, PL/SQL 및 T-SQL 에서는다양한변수가있다. 예제에서나온 cnt 라는변수를 SCALAR 변수라고한다. SCALAR 변수는사용자의임시데이터를하나만저장할수있는변수이며거의모든형태의데이터유형을지정할수있다. 둘째, PL/SQL 에서사용하는 SQL 구문은대부분지금까지살펴본것과동일하게사용할수있지만 SELECT 문장은다르다. PL/SQL 에서사용하는 SELECT 문장은결과값이반드시있어야하며, 그결과역시반드시하나여야한다. 조회결과가없거나하나이상인경우에는에러를발생시킨다. T-SQL 에서는결과값이없어도에러가발생하지않는다. 셋째, T-SQL 을비롯하여일반적으로대입연산자는 = 을사용하지만 PL/SQL 에서는 := 를사용한다. 넷째, 에러처리를담당하는 EXCEPTION 에는 WHEN ~ THEN 절을사용하여에러의종류별로적절히처리한다. OTHERS 를이용하여모든에러를처리할수있지만정확하게에러를처리하는것이좋다. T-SQL 에서는에러처리를다양하게처리할수있으며위의예제는그한예이다. 다음은지금까지작성한프로시저를실행하여기능을테스트한과정이다. [ 실행결과 ] Oracle SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> variable rslt varchar2(30); SQL> EXECUTE p_dept_insert(10,'dev','seoul',:rslt); PL/SQL 처리가정상적으로완료되었다. SQL> print rslt; RSLT 이미등록된부서번호이다 SQL> EXECUTE p_dept_insert(50,'newdev','seoul',:rslt); PL/SQL 처리가정상적으로완료되었다. SQL> print rslt; RSLT 입력완료!! SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDev SEOUL 5 개의행이선택되었다. 1 DEPT 테이블을조회하면총 4 개행의결과가출력된다. 2 Procedure 를실행한결과값을받을변수를선언한다. (BIND 변수 ) 3 존재하는 DEPTNO(10) 를가지고 Procedure 를실행한다. 4 DEPTNO 가 10 인부서는이미존재하기때문에변수 rslt 를 print 해보면 ' 이미등록된부서번호이다 ' 라고출력된다. 5 이번에는새로운 DEPTNO(50) 를가지고입력한다. 6 rslt 를출력해보면 ' 입력완료!!' 라고출력된다. 7 DEPT 테이블을조회하여보면 DEPTNO 가 50 인데이터가정확하게저장되었음을확인할수있다. T-SQL 로작성한프로시저를실행하기위해서는일반적으로 SQL Server 에서제공하는기본클라이언트프로그램인 SQL Server MANAGEMENT STUDIO 를사용한다. [ 실행결과 ] SQL Server SELECT * FROM DEPT; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON VARCHAR(100) EXECUTE dbo.p_dept_insert 10, 'dev', OUTPUT AS RSLT RSLT 이미등록된부서번호이다 VARCHAR(100) EXECUTE dbo.p_dept_insert 50, 'dev', OUTPUT AS RSLT RSLT 입력완료! SELECT * FROM DEPT; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20

68 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 NewDev SEOUL 5 개의행에서선택되었다. 1 DEPT 테이블을조회하면총 4 개행의결과가출력된다. 2 Procedure 를실행한결과값을받을변수를선언한다. 3 존재하는 DEPTNO(10) 를가지고 Procedure 를실행한다. 4 DEPTNO 가 10 인부서는이미존재하기때문에변수 rslt 를 print 해보면 이미등록된부서번호이다 라고출력된다. 5 Procedure 를실행한결과값을받을변수를선언한다. 6 이번에는새로운 DEPTNO(50) 를가지고입력한다. 7 rslt 를출력해보면 입력완료! 라고출력된다. 8 DEPT 테이블을조회하여보면 DEPTNO 가 50 인데이터가정확하게저장되었음을확인할수있다. 5. User Defined Function 의생성과활용 User Defined Function 은 Procedure 처럼절차형 SQL 을로직과함께데이터베이스내에저장해놓은명령문의집합을의미한다. 앞에서학습한 SUM, SUBSTR, NVL 등의함수는벤더에서미리만들어둔내장함수이고, 사용자가별도의함수를만들수도있다. Function 이 Procedure 와다른점은 RETURN 을사용해서하나의값을반드시되돌려줘야한다는것이다. 즉 Function 은 Procedure 와는달리 SQL 문장에서특정작업을 [ 예제 ] K- 리그 8 월경기결과와두팀간의점수차를 ABS 함수를사용하여절대값으로출력한다. [ 예제 ] Oracle SELECT SCHE_DATE 경기일자, HOMETEAM_ID ' - ' AWAYTEAM_ID 팀들, HOME_SCORE ' - ' AWAY_SCORE SCORE, ABS(HOME_SCORE - AWAY_SCORE) 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN ' ' AND ' ' ORDER BY SCHE_DATE; [ 예제 ] SQL Server SELECT SCHE_DATE 경기일자, HOMETEAM_ID + ' - ' + AWAYTEAM_ID AS 팀들, HOME_SCORE + ' - ' + AWAY_SCORE AS SCORE, ABS(HOME_SCORE - AWAY_SCORE) AS 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN ' ' AND ' ' ORDER BY SCHE_DATE; [ 실행결과 ] 경기일자팀들 SCORE 점수차 K01 - K K06 - K K08 - K K05 - K K10 - K K07 - K K03 - K K09 - K K04 - K K01 - K K05 - K K02 - K K08 - K K04 - K K06 - K K02 - K K05 - K K08 - K K10 - K K09 - K K04 - K K09 - K K03 - K K01 - K K06 - K 개의행이선택되었다. [ 예제 ] 에서사용한 ABS 함수를만드는데, INPUT 값으로숫자만들어온다고가정한다. [ 예제 ] Oracle CREATE OR REPLACE Function UTIL_ABS (v_input in number) return NUMBER IS v_return number := 0; BEGIN if v_input < 0 then v_return := v_input * -1; else v_return := v_input; end if; RETURN v_return; END; / [ 예제 ] SQL Server CREATE Function dbo.util_abs (@v_input int) RETURNS int AS BEGIN int < * -1 ELSE END [ 예제 ] 에서생성한 UTIL_ABS Function 의처리과정은다음과같다.

69 1 숫자값을입력받는다. 예제에서는숫자값만입력된다고가정한다. 2 리턴값을받아줄변수인 v_return 를선언한다. 3 입력값이음수이면 -1 을곱하여 v_return 변수에대입한다. 4 v_return 변수를리턴한다. [ 예제 ] 함수를이용하여앞의 SQL 을수정하여실행한다. [ 예제 ] Oracle SELECT SCHE_DATE 경기일자, HOMETEAM_ID ' - ' AWAYTEAM_ID 팀들, HOME_SCORE ' - ' AWAY_SCORE SCORE, UTIL_ABS(HOME_SCORE - AWAY_SCORE) 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN ' ' AND ' ' ORDER BY SCHE_DATE; [ 예제 ] SQL Server SELECT SCHE_DATE 경기일자, HOMETEAM_ID + ' - ' + AWAYTEAM_ID AS 팀들, HOME_SCORE + ' - ' + AWAY_SCORE AS SCORE, dbo.util_abs(home_score - AWAY_SCORE) AS 점수차 FROM SCHEDULE WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN ' ' AND ' ' ORDER BY SCHE_DATE; [ 실행결과 ] 경기일자팀들 SCORE 점수차 K01 - K K06 - K K08 - K K05 - K K10 - K 개의행이선택되었다. 실행결과는앞의 ABS 내장함수를사용한 SQL 문장과같은결과를확인할수있다. 6. Trigger 의생성과활용 Trigger 란특정한테이블에 INSERT, UPDATE, DELETE 와같은 DML 문이수행되었을때, 데이터베이스에서자동으로동작하도록작성된프로그램이다. 즉사용자가직접호출하여사용하는것이아니고데이터베이스에서자동적으로수행하게된다. Trigger 는테이블과뷰, 데이터베이스작업을대상으로정의할수있으며, 전체트랜잭션작업에대해발생되는 Trigger 와각행에대해서발생되는 Trigger 가있다. 요구사항은다음과같다고가정한다. 어떤쇼핑몰에하루에수만건의주문이들어온다. 주문데이터는주문일자, 주문상품, 수량, 가격이있으며, 사장을비롯한모든임직원이일자별, 상품별총판매수량과총판매가격으로구성된주문실적을온라인상으로실시간조회한다고했을때, 한사람의임직원이조회할때마다수만건의데이터를읽어계산해야한다. 가끔한번씩조회한다면문제가없을수도있으나빈번하게조회작업이일어난다면조회작업에많은시간을허비할수있다. [ 예제 ] 트리거 (Trigger) 를사용하여주문한건이입력될때마다, 일자별상품별로판매수량과판매금액을집계하여집계자료를보관하도록한다. 먼저관련테이블을생성한다.

70 [ 예제 ] Oracle CREATE TABLE ORDER_LIST ( ORDER_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL, QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL); CREATE TABLE SALES_PER_DATE ( SALE_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL, QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL); [ 예제 ] SQL Server CREATE TABLE ORDER_LIST ( ORDER_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR(10) NOT NULL, QTY INT NOT NULL, AMOUNT INT NOT NULL); CREATE TABLE SALES_PER_DATE ( SALE_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR(10) NOT NULL, QTY INT NOT NULL, AMOUNT INT NOT NULL); [ 예제 ] 이제 Trigger 를작성한다. Trigger 의역할은 ORDER_LIST 에주문정보가입력되면주문정보의주문일자 (ORDER_LIST.ORDER_DATE) 와주문상품 (ORDER_LIST.PRODUCT) 을기준으로판매집계테이블 (SALES_PER_DATE) 에해당주문일자의주문상품레코드가존재하면판매수량과판매금액을더하고존재하지않으면새로운레코드를입력한다. [ 예제 ] Oracle CREATE OR REPLACE Trigger SUMMARY_SALES AFTER INSERT ON ORDER_LIST FOR EACH ROW DECLARE o_date ORDER_LIST.order_date%TYPE; o_prod ORDER_LIST.product%TYPE; BEGIN o_date := :NEW.order_date; o_prod := :NEW.product; UPDATE SALES_PER_DATE SET qty = qty + :NEW.qty, amount = amount + :NEW.amount WHERE sale_date = o_date AND product = o_prod; if SQL%NOTFOUND then INSERT INTO SALES_PER_DATE VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); end if; END; / SUMMARY_SALES Trigger 의처리절차를설명하면다음과같다. 1 Trigger 를선언한다.CREATE OR REPLACE Trigger SUMMARY_SALES : Trigger 선언문 AFTER INSERT : 레코드가입력이된후 Trigger 발생 ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정 FOR EACH ROW : 각 ROW 마다 Trigger 적용 2 o_date( 주문일자 ), o_prod( 주문상품 ) 값을저장할변수를선언하고, 신규로입력된데이터를저장한다. : NEW 는신규로입력된레코드의정보를가지고있는구조체 : OLD 는수정, 삭제되기전의레코드를가지고있는구조체 [ 표 Ⅱ-2-17] 참조 3 먼저입력된주문내역의주문일자와주문상품을기준으로 SALES_PER_DATE 테이블에업데이트한다. 4 처리결과가 SQL%NOTFOUND 이면해당주문일자의주문상품실적이존재하지않으며, SALES_ PER_DATE 테이블에새로운집계데이터를입력한다.

71 [ 예제 ] SQL Server CREATE Trigger dbo.summary_sales ON ORDER_LIST AFTER INSERT AS DATETIME,@o_prod INT,@qty int FROM inserted UPDATE SALES_PER_DATE SET qty = qty amount = amount WHERE sale_date AND product INSERT INTO @amount) END SUMMARY_SALES Trigger 의처리절차를설명하면다음과같다. 1 Trigger 를선언한다.CREATE Trigger SUMMARY_SALES : Trigger 선언문 ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정 AFTER INSERT : 레코드가입력이된후 Trigger 발생 2 o_date( 주문일자 ), o_prod( 주문상품 ), qty( 수량 ), amount( 금액 ) 값을저장할변수를선언하고, 신규로입력된데이터를저장한다.inserted 는신규로입력된레코드의정보를가지고있는구조체 deleted 는수정, 삭제되기전의레코드를가지고있는구조체. [ 표 Ⅱ-2-18] 참조 3 먼저입력된주문내역의주문일자와주문상품을기준으로 SALES_PER_DATE 테이블에업데이트한다. 4 처리결과가 0 건이면해당주문일자의주문상품실적이존재하지않으며, SALES_PER_DATE 테이블에새로운집계데이터를입력한다. [ 예제 ] ORDER_LIST 테이블에주문정보를입력한다. [ 예제 ] Oracle SQL> SELECT * FROM ORDER_LIST; 선택된레코드가없다. SQL> SELECT * FROM SALES_PER_DATE; 선택된레코드가없다. SQL> INSERT INTO ORDER_LIST VALUES(' ', 'MONOPACK', 10, ); 1 개의행이만들어졌다. SQL> COMMIT; 커밋이완료되었다. [ 예제 ] SQL Server SELECT * FROM ORDER_LIST; 선택된레코드가없다. SELECT * FROM SALES_PER_DATE; 선택된레코드가없다. INSERT INTO ORDER_LIST VALUES(' ', 'MONOPACK', 10, ); 1 개의행이만들어졌다. [ 예제 ] 주문정보와판매집?. [ 실행결과 ] SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK [ 예제 ] 다시한번같은데이터를입력해보고, 두테이블의데이터를확인한다. [ 실행결과 ] Oracle SQL> INSERT INTO ORDER_LIST VALUES(' ','MONOPACK',20,600000); 1 개의행이만들어졌다. SQL> COMMIT; 커밋이완료되었다. SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK MONOPACK SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT

72 QTY AMOUNT MONOPACK [ 실행결과 ] SQL Server INSERT INTO ORDER_LIST VALUES(' ','MONOPACK',20,600000); 1 개의행이만들어졌다. SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK MONOPACK SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK [ 예제 ] 이번에는다른상품으로주문데이터를입력한후두테이블의결과를조회해보고트랜잭션을 ROLLBACK 수행한다. 판매데이터의입력취소가일어나면, 주문정보테이블과판매집계테이블에동시에입력 ( 수정 ) 취소가일어나는지확인해본다. [ 실행결과 ] Oracle SQL> INSERT INTO ORDER_LIST VALUES(' ','MULTIPACK',10,300000); 1 개의행이만들어졌다. SQL> SELECT * FROM ORDER_LIST; ORDER_DA PRODUCT QTY AMOUNT MONOPACK MONOPACK MULTIPACK SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK MULTIPACK SQL> ROLLBACK; 롤백이완료되었다. SQL> SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK MONOPACK SQL> SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK [ 실행결과 ] SQL Server BEGIN TRAN INSERT INTO ORDER_LIST VALUES(' ','MULTIPACK',10,300000); 1 개의행이만들어졌다. SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK MONOPACK MULTIPACK SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK MULTIPACK ROLLBACK; 롤백이완료되었다. SELECT * FROM ORDER_LIST; ORDER_DATG PRODUCT QTY AMOUNT MONOPACK MONOPACK SELECT * FROM SALES_PER_DATE; SALE_DATG PRODUCT QTY AMOUNT MONOPACK ROLLBACK 을하면하나의트랜잭션이취소가되어 Trigger 로입력된정보까지하나의트랜잭션으로인식하여두테이블모두입력취소가되는것을보여주고있다. Trigger 는데이터베이스에의해자동호출되지만결국 INSERT, UPDATE, DELETE 문과하나의트랜잭션안에서일어나는일련의작업들이라할수있다. Trigger 는데이터베이스보안의적용, 유효하지않은트랜잭션의예방, 업무규칙자동적용제공등에사용될수있다. 7. 프로시저와트리거의차이점 프로시저는 BEGIN ~ END 절내에 COMMIT, ROLLBACK 과같은트랜잭션종료명령어를사용할수있지만, 데이터베이스트리거는 BEGIN ~ END 절내에사용할수없다.