SQLP 2-2-1 SET, 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를 사용하지 못한다. (메인 쿼리의 마지막에 한번만 가능하다.)

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 ...
```

이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)

SQLP 2-2-1 SET, Hierarchical Query, Sub-query

2과목 SQL 기본 및 활용

2장 SQL 활용 (#1)

1. 집합 연산자 (SET)

2. 계층형 질의 (Hierarchical Query)

3. SELF JOIN

4. Subquery

분류

1. WHERE에 위치

2. SELECT에 위치 (Scalar Subquery)

3. FROM에 위치 (Inline View)

4. HAVING에 위치

5. UPDATE의 SET에 위치

6. INSERT의 VALUES에 위치

5. View

Dev Note

2과목 SQL 기본 및 활용

2장 SQL 활용 (#1)

1. 집합 연산자 (SET)

2. 계층형 질의 (Hierarchical Query)

3. SELF JOIN

4. Subquery

분류

1. WHERE에 위치

2. SELECT에 위치 (Scalar Subquery)

3. FROM에 위치 (Inline View)

4. HAVING에 위치

5. UPDATE의 SET에 위치

6. INSERT의 VALUES에 위치

5. View

Share this post