[SQLD] 집합연산자와 계층형 질의

집합연산자와 계층형 질의

 

 

집합 연산자(Set Operator)

  - 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용

  - 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 줌

  - 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때와 동일테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때 사용할 수 있다.

  - 튜닝관점에서 실행계획을 분리하고자 하는 목적으로도 사용

  Caution.

    * SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 한다. 그렇지 않으면 데이터베이스가 오류를 반환한다.

 

집합 연산자의 종류

집합 연산자	의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 베타적일 때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다.
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합. 중복된 행은 하나의 행으로 만든다.
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다.

 

 

집합 연산자 SQL 문 예시

SELECT 문 1 집합연산자 SELECT 문 2 [ORDER BY 1, 2 [ASC 또는 DESC]];

예제1.

K-리그 소속 선수들 중에서 소속이 삼성블루윙즈팀인 선수들과 전남드레곤즈팀인 선수들에 대한 내용을 모두 보고싶다. (팀 코드: 삼성 -> K02, 전남 ->K07)

select P.TEAM_ID, P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.HEIGHT FROM PLAYER as P where P.TEAM_ID='K02' UNION select P.TEAM_ID, P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.HEIGHT FROM PLAYER as P where P.TEAM_ID='K07' ORDER BY 2;

 

예제 2.

K-리그 소속 선수들 중에서 소속이 삼성블루윙즈팀인 선수들과 전남드레곤즈팀인 선수들에 대한 내용을 모두 보고싶다. (팀 코드: 삼성 -> K02, 전남 ->K07), (중복되도 상관없다.)

select P.TEAM_ID, P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.HEIGHT FROM PLAYER as P where P.TEAM_ID='K02' UNION select P.TEAM_ID, P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.HEIGHT FROM PLAYER as P where P.TEAM_ID='K07' ORDER BY 2;

EXCEPT와 INTERSET는 제가 사용하는 My SQL에는 없으므로 예시를 못보여드립니다. 

그렇지만 UNION처럼 똑같이만 써주면 되는 부분이라 쉽게 하실수 있으실 겁니다. 

 

 

계층형 질의 

  - 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용

  (계층형 데이터 : 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터)

  1. Oracle의 계층형 질의 (저는 Oracle 유저가 아니므로 간단히 설명하겠습니다.)

SELECT...... FROM 테이블 WHERE condition and condition..... START WITH condition CONNECT BY [NOCYCLE] condition and condition..... [ORDER SIBLINGS BY column, column ......]

  - START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정

  - CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야한다.            (JOIN사용)

  - PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정. PRIOR 자식=부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 순방향 전개를 사용

  - NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 Cycle이 형성되었다 한다. Cycle이 발생한 데이터는 런타임 오류를 발생한다. 이때 사용하는게 NOCYCLE이다. NOCYCLE을 사용하면 이후의 데이터는 전개하지 않는다.

  - ORDER SIBLINGS : 형제 노드(동일 Level) 사이에서 정렬을 수행한다.

  - WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다(필터링)

 

 

계층형 질의에서 사용되는 가상 칼럼

가상 칼럼	설명
LEVEL	루트 데이터면 1, 그 하위 데이터이면 2, LEAF데이터까지 1씩 증가한다.
CONNECT_BY ISLEAF	전개 과정에서 해당 데이터가 LEAF DATA이면 1, 그렇지 않으면 0.
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0, 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다.

 

계층형 질의에서 사용되는 함수

함수	설명
SYS_CONNECT_BY_PAHE	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다. 사용 : SYS_CONNECT_BY_PATH( 칼럼, 경로 분리자)
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용: CONNECT_BY_ROOT 칼럼

 

 

 

셀프 조인

  - 동일 테이블 사이의 조인을 말한다.

  - FROM절에 동일 테이블이 두 번 이상 나타난다.

  - 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(ALIAS)를 사용해야 한다.

  - 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다.

자신과 상위, 차상위 관리자를 같은 줄에 표시하라.

select t1.ename as 사원,t1.mgr as 관리자, t2.mgr as 차상위_관리자 from emp as t1,emp as t2 where t1.mgr=t2.empno order by 사원;

  위 예제는 테이블의 정보가 누락되어있다. 따라서 Outer join을 이용해서 다시 해보기로 하자.

 

자신과 상위, 차상위 관리자를 같은 줄에 표시하라.

select t1.ename as 사원,t1.mgr as 관리자, t2.mgr as 차상위_관리자 from emp as t1 left outer join emp as t2 on(t1.mgr=t2.empno) order by 사원;

important.

  1번째 예제와 2번째 예제의 결과물이 다르게 나온걸 볼 수 있다. SELECT 문 사용 후 결과가 잘 나왔는지 꼭 확인해보기 바랍니다.