[SQL] 관계형대수, 계층형 질의, 재귀

1. 관계형 대수란(STANDARD SQL)?

- 관계형 데이터베이스에서 원하는 정보를 유도하기 위한 기본 연산 집합

 

1) 일반 집합 연산

- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나

- 컬럼의 수와 각 컬럼의 데이터 타입이 같아야 함

- 합집합(UNION) : A 또는 B에 존재하는 모든 데이터(중복처리를 위해 정렬 후 중복제거, UNION ALL은 중복 포함, 정렬X)

- 교집합(INTERSECT) : A와 B 둘 다에 있는 데이터, 공통된 부분을 추출하고 중복된 결과는 제거(ORACLE, MariaDB는 지원되지만 MySQL에서는 지원되지 않아 JOIN 사용)

- 차집합(EXCEPT): A에는 있지만 B에는 없는 데이터(A-B)

- 카디션곱(CROSS JOIN): A의 각 행 * B의 각 행(모든 행 조합)

 

2) 순수 관계 연산

- 셀렉션(WHERE) : 특정 행 선택

- 프로젝션(SELECT) : 특정 컬럼 선택

- 조인(JOIN): 두 테이블을 공통 키로 결합

- 디비전(GROUP BY + HAVING): 모든 조건을 만족하는 행

 

2. 계층형질의

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

- 대표적인 데이터베이스: ORACLE, SQL Server

 

1) 계층형 데이터

- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어있는 데이터

- 예: 사원/관리자 열이 함께 있는 테이블

 

2) 계층형 질의(ORACLE)

- START WITH 어떤 데이터가 최상위 데이터가 될것인지 기준을 잡아줌

- CONNECT BY  상위데이터와 하위데이터의 연결 방식

SELECT LEVEL, 사원번호, 관리자
FROM 직원
START WITH 관리자 IS NULL(최상위 ROOT 데이터)
CONNECT BY PRIOR 사원번호(상위데이터) = 관리자(하위데이터);

- LPAD(a,b) : 왼쪽에 a를 b개 추가

LPAD('',n) -- 왼쪽에 공백 n개 추가

- ORACLE에서 사용되는 CONNECT BY키워드

키워드	설명
LEVEL	검색 항목의 깊이를 의미하며, 계층 구조에서 루트(최상위)의 레벨이 1
CONNECT_BY_ROOT	현재 전개할 데이터의 루트(최상위) 데이터 값 표시
CONNECT_BY_ISLEAF	현재 전개할데이터가 리프(최하위) 데이터인지에 대한 값 표시(0 또는 1)
SYS_CONNECT_BY_PATH(A,B)	루트 데이터부터 현재까지 전개한 경로 표시(A: 컬럼명, B:구분자)

 

3) 계층형질의(SQL Server)

- SQL Server version.2000 이전: 저장 프로시저를 재귀 호출 / while 루프 문에서 임시테이블 사용

- SQL Server version.2005 이후: CTE(Common Table Expression)을 이용해 재귀 호출 => WITH ( ) AS ( ) 문 사용

*CTE : 가상의 테이블

 

4) 계층형 질의(MySQL, MariaDB)

- MariaDB의경우 10.2 이후 CTE (Common Table Expression)을 이용해 재귀 호출

- WITH RECURSIVE CTE (추출하고자 하는 컬럼명) AS (쿼리문)

- 조회: FROM CTE

- 최상위 설정 ORACLE의 STARTWITH => MariaDB의 WHERE

WITH RECURSIVE CTE (member_id, manager_id, lvl) AS (
    SELECT member_id, manager_id, 0 as lvl  # 처음에는 manager_id 가 null인거 1개 행
    FROM MEMBER                             # 오라클의 start with 와 같은 역할
    WHERE manager_id IS NULL

    UNION ALL

    SELECT a.member_id, a.manager_id, b.lvl+1  # 처음에는 CTE 테이블이 없으니까 빈 테이블
    FROM MEMBER AS a                           # 오라클의 connect by 와 같은 역할
    JOIN CTE AS b
    ON a.manager_id = b.member_id
)

SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY member_id, lvl;


--step1: 처음에는 1개행 + member & 빈행 => CTE : 1개행
--step2: 처음에는 1개행 + member & 1개행 (총 2개행) => CTE : 3개행
-- CTE 테이블이 계속 업데이트됨

 

3. 재귀

- 자기 자신을 다시 호출해 스택처럼 쌓였다가

- 더 이상 자기 자신을 호출하지 않고 멈추면, 종료조건에서부터 하나씩 돌아가며 마무리(콜스택)