SQLP 요점정리 #04 : 2과목 SQL - 2장-1 SQL활용 : UNION, Hierarchical Query, Sub-query

2과목 SQL 기본 및 활용

• 20문제
• 각 상황별로 SQL문 작성이 가능할 정도로 연습해야 합니다.
• 기본적인 SQL문법 및 함수는 생략하고 Tuning에 활용될수 있는 것 위주로 정리하고자 합니다.

2장 SQL 활용 (#1)

1. 집합 연산자 (SET)

• UNON : 합집합, 중복된 행은 제거
• UNION ALL : 합집합, 중복된 행을 그대로 표시하는 대신에 성능이 빠름
• INTERSECT : 교집합, 중복된 행 제거
• MINUS : 차집합, 앞의 SQL 결과에서 뒤의 결과를 제거한다. (오라클 이외의 대부분은 EXCEPT로 사용함)

솔직히 UNION과 UNION ALL의 차이만 이해하고 있으면 된다.
UNION 과 UNION ALL의 결과가 같다는것이 보장되면 무조건 UNION ALL을 사용하는 것이 성능이 뛰어나다.
INTERSECT 는 EXISTS 나 IN 서브쿼리로 변경이 가능하다. (그러므로 쓸일이 거의 없다.)
MINUS 는 그냥 WHERE의 조건에서 논리적으로 제외시키는 것이 가능하다. (심지어 출력하지 않아도 될 ROW와 거기서 빼야할 ROW들을 읽어야 하므로 성능을 생각해서는 절대로 사용하면 안된다.)

2. 계층형 질의 (Hierarchical Query)

계층형 데이터를 조회할때 사용한다.

계층형 데이터 : 동일 테이블에 계층적으로 상위, 하위 데이터가 포함된 데이터
ex. EMP Table에 EMPNO, MANAGER 가 있으며 EMPNO는 해당 사원의 사원번호, MANAGER는 해당 사원의 부서장 사원번호인 경우

계층형 질의는 다음과 같은 모양이다. (Oracle 기준)

• 질의 구문

SELECT ...
  FROM table name
 WHERE (filter condition) 
 START WITH (root condition)                   -- START WITH MANAGER IS NULL
 CONNECT BY [NOCYCLE] (hierarchical condition) -- CONNECT BY NOCYCLE PRIOR EMPNO = MANAGER
 [ORDER SIBLINGS BY columns...]

• START WITH : 시작조건. root data가 되는 조건을 적는다.
• CONNECT BY : 부모와 자식의 관계를 적는다. PRIOR 키워드는 전개방향 상 이전 레코드를 가리킨다.
• ORDER SIBLINGS BY :(같은 부모를 가진) 형재 node 의 ORDER BY이다.

SELECT, FROM, WHERE 등은 일반 SQL문법과 동일하다.

• 가상 칼럼
• LEVEL : ROOT = 1, LEAF방향으로 1씩 증가
• CONNECT_BY_ISLEAF : LEAF이면 1, 자식이 있으면 0
• CONNECT_BY_ISCYCLE : 자식을 가지면서, 자신이 자기 조상에도 있으면 1, 아니면 0 (CYCLE 옵션에서만 허용)
• 전용 함수
• SYS_CONNECT_BY_PATH(column, separator) : Root부터 현재까지의 경로를 표시한다.
• CONNECT_BY_ROOT column : root 데이터의 칼럼을 표시한다.

START WITH 조건과 CONNECT BY 조건을 어떻게 하냐에 따라서 부모 -> 자식 방향 또는 자식 -> 부모 방향으로의 전개가 가능하다.

• 부모 -> 자식 방향전개
  • START WITH : ROOT 조건
  • CONNECT BY : PRIOR 자식ID = 부모ID
    • 이 부분 해석이 좀 해깔릴수 있는데, EMPNO, MANAGER의 경우 PRIOR EMPNO = MANAGER 로 써야한다.
    • 그냥 START WITH 조건의 row와 전개방향 바로 다음 row의 비교조건을 적는다고 생각하면 됨.

SELECT LPAD(' ', 4 * (LEVEL-1)) || LEVEL AS "LEVEL", EMPNO, MGR, CONNECT_BY_ISLEAF AS LEAF
  FROM SCOTT.EMP
 START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;

• 자식 -> 부모 방향전개
  • START WITH : LEAF 조건
  • CONNECT BY : PRIOR 부모ID = 자식ID

SELECT LEVEL, EMPNO, MGR
  FROM SCOTT.EMP
 START WITH EMPNO = 7369
 CONNECT BY PRIOR MGR = EMPNO;

3. SELF JOIN

이것도 설명해야 할까 ?
그냥 같은 테이블 2개를 JOIN, alias 필수(그래야 서로 다른 view인듯 제어가 가능하니깐)

4. Subquery

• SQL문 안에 포함되는 SQL문을 의미한다.
• 반드시 (괄호)로 감싸야한다.
• ORDER BY를 사용하지 못한다. (메인 쿼리의 마지막에 한번만 가능하다.)

분류

• 동작 방식
  • 비연관 (Un-Correlated) : 메인쿼리의 컬럼을 가지고 있지 않음. 메인쿼리에 결과를 제공하기 위한 목적
  • 연관 (Correlated) : 메인쿼리의 칼럼을 가지고 있음. 메인쿼리가 먼저 수행되면서 서브쿼리에서 조건 체크할 목적
• 반환되는 데이터 형태
  • Single row : 결과가 1건 이하 (단일행 비교연산자)
  • Multi row : 결과가 여러 건 (IN, ALL, ANY, SOME, EXISTS 연산자)
  • Multi column : 결과가 여러 컬럼 (메인쿼리와 비교하자고 하는 컬럼의 위치와 개수가 같아야함)
  • Scalar Subquery : 1개의 data (1 row 1 column)만 반환. SQL문 중 column이 위치할 수 있는 대부분의 곳에 사용 가능

일단 이 2가지 분류방식에 대해서 먼저 이해를 하고, 이제 위치에 따라 올 수 있는 형태를 살펴보겠다.

1. WHERE에 위치

• Single row

SCOTT이 속한 부서의 직원목록을 보기 위해서는
먼저 SCOTT의 DEPTNO를 가져와서 해당 값과 같은 직원들을 조회해야 한다.

SELECT * FROM EMP 
 WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT') -- 먼저 수행

• Multi row

SALEMAN job이 있는 부서와 해당 부서에 있는 CLERK job의 목록을 보기 위해서는
먼저 SALEMAN 이 속한 부서의 DEPTNO를 구한 뒤, 해당 값과 같은 부서인원중 CLERK를 찾아야 한다.

SELECT * FROM EMP
 WHERE JOB = 'CLERK'
   AND DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP WHERE JOB = 'SALESMAN') -- 먼저 수행

• Multi column

각 팀별 최고연봉자를 출력하기 위해서는
먼저 각 팀별 최고연봉자에 대해서 GROUP BY로 구한 뒤 해당 칼럼정보와 같은 직원정보를 출력하면 된다.

SELECT * FROM EMP
 WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) AS SAL FROM EMP GROUP BY DEPTNO) -- 먼저 수행

• 연관 서브쿼리 (Correlated subuery)

메인쿼리 칼럼이 서브쿼리 내에서 사용된다.
메인쿼리가 먼저 수행되면서 row마다 서브쿼리를 한번씩 수행하므로 성능상에는 좋지않다.

각 부서별 평균영봉 이상을 받는 직원정보를 출력하고자 하기 위해서는
매 직원정보마다 해당 직원의 연봉이 해당 부서의 평균이상인지를 비교(check)해야 한다.

SELECT * FROM EMP A               -- 메인쿼리가 먼저 수행되면서 매 ROW마다 아래 서브쿼리 수행
 WHERE SAL >= (SELECT AVG(SAL) AS SAL FROM EMP B WHERE B.DEPTNO = A.DEPTNO GROUP BY B.DEPTNO) 

2. SELECT에 위치 (Scalar Subquery)

대부분 연관(correlated) 관계로 호출한다.
비연관으로 가능하다면 그냥 Inline View로 하는게 더 효과적이다.

직원정보를 호출하면서 소속부서의 평균연봉도 같이 출력할 경우 다음과 같다.

SELECT DEPTNO, ENAME, SAL,
       ROUND((SELECT AVG(SAL) FROM EMP B WHERE B.DEPTNO = A.DEPTNO GROUP BY B.DEPTNO)) AS AVG_SAL
  FROM EMP A
 ORDER BY DEPTNO, SAL;

3. FROM에 위치 (Inline View)

View나 Table이 올수 있는 위치에는 사용이 가능하다.
해당 query내에서는 마치 dynamic view처럼 사용된다.
사용상 아무런 제약을 받지않고 SELECT문을 자유롭게 쓸 수 있다. (ORDER BY도 사용이 가능하다.)

바로 위에 연관관계로 호출된 Scalar subquery는 성능이 좋지 않으므로 inline view로 고쳐보자.
각 부서별 평균연봉을 dynamic view로 구한 다음 JOIN을 하면 된다.

SELECT A.DEPTNO, A.ENAME, A.SAL, B.AVG_SAL
  FROM EMP A,
       (SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL FROM EMP GROUP BY DEPTNO) B
 WHERE A.DEPTNO = B.DEPTNO
 ORDER BY DEPTNO, SAL;

• TOP-N 쿼리

data를 정렬하고 그 중 일부만 추출하고자 할때는 inline view로 ORDER BY를 한 뒤 ROWNUM으로 추출하면 된다.

MANAGER 중 연봉 BEST 2를 순서대로 출력하고자 할 경우 (MANAGER는 총 3명)

SELECT *
  FROM (SELECT * FROM SCOTT.EMP WHERE JOB = 'MANAGER' ORDER BY SAL DESC)
 WHERE ROWNUM <= 2;

4. HAVING에 위치

WHERE에 위치와 크게 다르지않다.
하지만 집계함수 사용시 그 결과를 filtering 하는 것이므로 의미상 필요할 경우 사용하면 된다.

부서별 평균연봉이 전체평균연봉보다 큰 부서명을 출력하고자 할 경우

SELECT C.DNAME, B.AVG_SAL
  FROM DEPT C,
       (SELECT A.DEPTNO, ROUND(AVG(A.SAL)) AS AVG_SAL
          FROM EMP A
         GROUP BY DEPTNO
        HAVING AVG(A.SAL) >= (SELECT AVG(SAL) FROM EMP)) B
 WHERE C.DEPTNO = B.DEPTNO;

5. UPDATE의 SET에 위치

UPDATE 할 TABLE과 JOIN하여 여러 row에 대한 UPDATE를 한번에 수행이 가능하다. DEPT table에 AVG_SAL(평균연봉) 컬럼을 추가한 뒤 해당 값을 한번에 넣고자 하는 경우

UPDATE DEPT A
   SET AVG_SAL = (SELECT AVG(SAL) AS AVG_SAL FROM EMP B WHERE B.DEPTNO = A.DEPTNO GROUP BY B.DEPTNO);

6. INSERT의 VALUES에 위치

주로 새로운 값을 추가할 때 기존에 저장된 마지막 일련번호(s/n)의 다음 번호로 저장하고자 할때 사용하며 편하다.

INSERT INTO EMP (EMPNO, ENAME)
VALUES ((SELECT MAX(EMPNO) + 1 FROM EMP), 'LUNA');

5. View

SELECT 질의문을 이용하여 가상의 table을 선언하는 것이다.
실제로 물리적으로 해당 table이 생성되는 것이 아니라 수행할때마다 DBMS 내부적으로 View의 정의를 참조하여 쿼리를 재정의한다.

• View의 장점
  • 독립성 : Table의 구조가 변경되어도, View를 사용하는 application은 변경하지 않아도 된다. (물론 View를 변경해줘야 하지만)
  • 편리성 : 자주 이용되는 복잡한 query를 미리 view로 정의해두면 편리하게 재사용이 가능하다.
  • 보안성 : 보안상 숨기고 싶은 데이터에 대해서는 view 생성시 해당 정보를 제외하고 view를 배포하면 된다.
• View 생성방법

CREATE VIEW 뷰명칭
AS
SELECT ...