라즈베리파이반

라즈베리파이 등 컴퓨터계열 게시판입니다.

제목SQL: 집합연산자 / 계층형 질의문2023-03-29 23:33
작성자user icon Level 4

88x31.png


1. 집합연산자(Set Operation)


집합연산자는 두 개 이상의 테이블을 연결하여 하나로 결합하는 연산자 입니다. 집합연산자를 사용하기 위해서는 컬럼의 수와 타입이 동일해야 합니다.


집합연산자에는 다음과 같은 유형이 있습니다.


집합연산자 유형

 유형

 설명

 UNION

 합집합 연산을 수행하며, 중복을 제거하여 결과를 표시합니다.

 UNION ALL

 합집합 연산을 수행하며, 중복을 포함하여 결과를 표시합니다.

 INTERSECT

 교집합 연산을 수행합니다.

 MINUS

 차집합 연산을 수행합니다.


기본 구조는 다음과 같습니다.


집합연산자 기본 구조

SELECT ...

[UNION | UNION ALL | INTERSECT | MINUS]

SELECT ...



1) UNION


UNION은 합집합 연산을 수행하며, 중복된 행은 하나로 표시합니다. 내부적으로 중복된 값을 제거하는 과정에서 SORT 기능이 동작합니다.


아래와 같은 예제 데이터가 있습니다.


EMPLOYEES_1

 ID

 NAME

 1

 Alice

 2

 Bob

 3

 Charlie

 4

 David


EMPLOYEES_2

 ID

 NAME

 5

 Emily

 2

 Bob

 4

 David

 6

 Fiona



두 테이블의 결과에 대하여 중복을 제거하여 합쳐 출력하기 위해서는 다음과 같은 쿼리문을 작성하면 됩니다.


UNION 예제

SELECT ID, NAME

  FROM EMPLOYEES_1

UNION

SELECT ID, NAME

  FROM EMPLOYEES_2


UNION 결과 

 ID

 NAME

 1

 Alice

 2

 Bob

 3

 Charlie

 4

 David

 5

 Emily

 6

 Fiona



2) UNION ALL


UNION ALL은 합집합 연산을 수행하며, 중복된 행을 그대로 표시합니다. UNION과 달리 데이터의 중복을 제거하거나 정렬 과정을 거치지 않기 때문에 성능상 UNION보다 유리합니다.


UNION ALL 예제

SELECT ID, NAME

  FROM EMPLOYEES_1

UNION ALL

SELECT ID, NAME

  FROM EMPLOYEES_2


UNION ALL 결과

 ID

 NAME

 1

 Alice

 2

 Bob

 3

 Charlie

 4

 David

 5

 Emily

 2

 Bob

 4

 David

 6

Fiona 



3) INTERSECT


INTERSECT는 교집합 연산을 수행하며, 중복된 행은 제거하여 출력합니다.


INTERSECT 예제

SELECT ID, NAME

  FROM EMPLOYEES_1

INTERSECT

SELECT ID, NAME

  FROM EMPLOYEES_2


INTERSECT  결과

 ID

 NAME

 1

 Alice

 3

 Charlie



4) MINUS


MINUS는 차집합 연산을 수행하며, 중복된 행은 제거하여 출력합니다. SQL Server에서는 EXCEPT를 사용합니다.


MINUS 예제

SELECT ID, NAME

  FROM EMPLOYEES_1

MINUS

SELECT ID, NAME

  FROM EMPLOYEES_2


MINUS 결과

 ID

 NAME

2

 Bob

 4

 David


MINUS는 다음과 같이 NOT EXISTS나 NOT IN 서브쿼리를 통해서도 출력할 수 있습니다.


NOT EXISTS를 사용한 차집합 예제

 SELECT ID, NAME

   FROM EMPLOYEES_1 A

 WHERE NOT EXISTS (

         SELECT 1

           FROM EMPLOYEES_2 B

         WHERE 1 = 1

              AND A.ID = B.ID

              AND A.NAME = B.NAME

       )


NOT IN을 사용한 차집합 예제 

SELECT ID, NAME

  FROM EMPLOYEES_1

 WHERE (ID, NAME) NOT IN (SELECT ID, NAME FROM EMPLOYEES_2)



2. 계층형 질의(Hierarchical Query)


계층형 질의는 동일한 테이블에 대하여 상위와 하위 포함 관계를 가진 계층형 데이터를 계층형 구조로 조회하기 위한 질의어 입니다.


기본 구조는 다음과 같습니다.


계층형 질의 기본 구조

 SELECT 컬럼명

   FROM 테이블명

  WHERE 조건문

  START WITH 조건문

CONNECT BY [NOCYCLE] 조건문

  ORDER SIBLINGS BY 컬럼명



START WITH 키워드에서는 계층 구조의 시작(ROOT)를 지정하고, CONNECT BY 키워드에서는 자식 노드의 조건을 설정합니다. 이때 PRIOR 키워드를 사용하는데, 해당 키워드의 위치에 따라 순방향 계층형 질의 또는 역방향 계층형 질의를 할 수 있습니다.


조건문 앞에 NOCYCLE 키워드를 붙인다면 순환 데이터를 조회하지 않게 됩니다. 즉, 그래프 구조가 아닌 트리형 구조로 데이터를 조회할 수 있습니다.


같은 레벨인 형제 노드 간 정렬을 위해서는 ORER SIBLINGS BY 키워드를 사용합니다.


계층형 질의문에서는 다음과 같은 가상 컬럼을 사용할 수 있습니다.


계층형 질의문 가상 컬럼

 가상 컬럼

 설명

 LEVEL

 루트 데이터를 1로 하며, 리프 데이터까지 1씩 증가합니다.

 CONNECT_BY_ISLEAF

 리프 데이터이면 1, 아니면 0을 출력합니다.

 CONNECT_BY_ISCYCLE

 사이클이 존재하면 1, 아니면 0을 출력합니다.


다음과 같은 함수도 사용할 수 있습니다.


계층형 질의문 함수

 함수

 설명

 SYS_CONNECT_BY_PATH

 루트 데이터부터 현재 데이터까지의 경로를 출력합니다.

 CONNECT_BY_ROOT

 현재 데이터의 루트 데이터를 출력합니다.


예를 들어 아래와 같은 예제 데이터가 있습니다.


EMPLOYEES

 EMPLOYEE_ID

 EMPLOYEE_NAME

 MANAGER_ID

 1

 John

 

 2

 Adam

 1

 3

 Emily

 1

 4

 Sarah

 2

 5

 Tom

 2

 6

 Jason

 3

 7

 Alex

 4

 8

 Chris

 4


MANAGER_ID를 상위노드로 하는 트리구조를 다음 그림과 같이 나타낼 수 있습니다.


트리구조

 


해당 데이터를 계층형 질의문으로 출력해보겠습니다.


계층형 질의문 예제

      SELECT EMPLOYEES_ID

         , EMPLOYEES_NAME

         , MANAGER_ID

         , LEVEL

         , CONNECT_BY_ISLEAF AS IS_LEAF

           , SYS_CONNECT_BY_PATH(EMPLOYEES_NAME, '/') AS PATH

         , CONNECT_BY_ROOT(EMPLOYEES_NAME) AS ROOT

        FROM EMPLOYEES

        START WITH MANAGER_ID IS NULL

 CONNECT BY NOCYCLE PRIOR EMPLOYEES_ID = MANAGER_ID


MANAGER_ID가 NULL인 데이터가 루트 노드가 되고, EMPLOYEES_ID의 상위 데이터가 MANAGER_ID인 트리 구조를 DFS로 탐색합니다. 해당 트리는 순방향으로 진행됩니다. 결과는 다음과 같습니다.


계층형 질의문 결과

 EMPLOYEES_ID

 EMPLOYEES_NAME

 MANAGER_ID

 LEVEL

 IS_LEAF

 PATH

 ROOT

 1

 John

 

 1

 0

/John 

 John

 2

 Adam

 1

 2

 0

 /John/Adam

 John

 4

 Sarah

 2

 3

 0

 /John/Adam/Sarah

 John

 7

 Alex

 4

 4

 1

 /John/Adam/Sarah/Alex

 John

 8

 Chris

 4

 4

 1

 /John/Adam/Sarah/Chris

 John

 5

 Tom

 2

 3

 1

 /John/Adam/Tom

 John

 3

 Emily

 1

 2

 0

 /John/Emily

 John

 6

 Jason

 3

 3

 1

 /John/Emily/Jason

 John


해당 계층형 질의문은 다음과 같이 셀프조인을 통해 나타낼 수도 있습니다.


셀프 조인 예제

 SELECT A.EMPLOYEES_ID,

      A.EMPLOYEES_NAME,

      A.MANAGER_ID,

      B.EMPLOYEES_NAME AS MANAGER_NAME

   FROM EMPLOYEES A, EMPLOYEES B

 WHERE B.EMPLOYEES_ID = A.MANAGER_ID


결과는 다음과 같습니다.


셀프 조인 결과

 EMPLOYEES_ID

 EMPLOYEES_NAME

 MANAGER_ID

 MANAGER_NAME

 3

 Emily

 1

 John

 2

 Adam

 1

 John

 5

 Tom

 2

 Adam

 4

 Sarah

 2

 Adam

 6

 Jason

 3

 Emily

 8

 Chris

 4

 Sarah

 7

 Alex

 4

 Sarah



#SQL# 집합 연산자# 계층형 질의문# 셀프 조인
댓글
자동등록방지
(자동등록방지 숫자를 입력해 주세요)