SQL_JOIN, 표준조인, 계층형 질의, 셀프조인

1. JOIN

JOIN이란?

• 두개 이상의 테이블을 연결, 결합하여 데이터를 출력
• PK나 FK에 의해 JOIN이 성립됨

EQUI JOIN (등가조인)

• 두 테이블 간의 컬럼 값들이 서로 같은 경우
• 기본구조

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
FROM 테이블1, 테이블2 
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;

• 예시

SELECT p.first_name, 
	     n.country_name, 
	     n.population
FROM participant p, nation n
WHERE p.nation_id = n.nation_id AND p.gender = 'F'
ORDER BY p.first_name DESC;

Non EQUI JIN (비등가조인)

• 두개 테이블 간에 컬럼들이 서로 정확하게 일치하는 것이 아닌 특정 범위 내에 있는 경우
• = 연산자가 아닌 다른 (BETWEEN, >, >=, <, <= ) 연산자들을 사용
  

select *
from emp.e, salgrades s
where e.sal between s.losal and s.hisal

• 예시

SELECT COUNT(*) AS CNT
FROM emp_tbl a, rule_tbl b
WHERE a.ename LIKE b.rule;

• 카티션곱, 즉 모든 행의 조합이 나올 수 있으므로 where에 원하는 조건을 더해준다

SELECT e.employee_id, e.employee_name, e.salary, b.bonus_amount
FROM employees e, bonuses b
WHERE e.salary > 55000;

SELECT e.employee_id, e.employee_name, e.salary, b.bonus_amount
FROM employees e, bonuses b
WHERE e.employee_id = b.employee_id AND e.salary > 55000;

3개 이상 테이블 JOIN

• 모든 테이블을 한번에 join하는게 아니라
  1번과 2번 join -> 만들어진 테이블과 3번 join 이런 식으로 실행

SELECT P.PLAYER_NAME  '선수명',
       P.POSITION     '포지션',
       T.REGION_NAME  '연고지',
       T.TEAM_NAME    '팀명',
       S.STADIUM_NAME '구장명'
FROM PLAYER P,
     TEAM T,
     STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
  AND T.STADIUM_ID = S.STADIUM_ID
ORDER BY '선수명';

2. 표준조인

일반 집합 연산자

• UNION
  • 중복을 없앤 합집합
  • UNION ALL : 중복을 제거하지 않은 합집합
• INTERSECTION
  • 교집합, 공통행을 출력함
• DIFFERENCE
  • 차집합
  • Oracle에서는 MINUS, 대부분은 EXCEPT로 사용
• PRODUCT
  • 곱집합, 모든 데이터의 조합
  • 데카르트의 곱(CARTESIAN PRODUCT)이라고도 함

JOIN의 종류

• INNER JOIN(EQUI 조인)
  • 조건에 일치하는 행만 반환함
  • 반드시 USING 조건절이나 ON 조건절을 사용해줘야 함
• NATURAL JOIN
  • JOIN 되는 컬럼을 별도로 지정하지 않아도 동일한 이름을 갖는 모든 컬럼들에 대해 자동으로 EQUI JOIN을 수행함
  • JOIN 기준이 되는 컬럼에 ALIAS나 테이블명 같은 접두사 사용 안됨
  • 동일한 컬럼명이라도 다른 데이터 유형이 저장되었다면 제대로 작동되지 않음

SELECT first_name, unit_name, rank
FROM Soldier NATURAL JOIN Unit;

• USING 조건절
  • 같은 이름을 가진 컬럼들 중에서 원하는 커럶에 대해서만 선택적으로 EQUI JOIN할 수 있다.
  • 수행 결과는 INNER JOIN과 같다
  • 조인컬럼 ALIAS 사용 불가

SELECT unit_id,
			 s.first_name,
       u.unit_name,
			 s.rank
FROM Soldier s JOIN Unit u
USING (unit_id);

• ON 조건절
  • USING 조건절은 같은 컬럼명일때 쓰지만 ON 조건절은 컬럼명이 달라도 사용 가능
  • ALIAS나 테이블명 접두사 사용 가능
  • WHERE과도 혼용해 쓸 수 있다.
• 다중테이블 JOIN
  • 세개 이상의 테이블 조인
  • 주스 판매량이 100에서 600사이인 부대의 이름과 판매량, 주스이름, 해당 부대에 소속된 군인 이름, 계급을 출력해 보세요.

SELECT u.unit_name, v.juice_type, v.quantity_sold, s.first_name, s.rank
FROM VolunteerActivity v JOIN Unit u
	ON v.activity_id = u.activity_id JOIN Soldier s
	ON u.unit_id = s.unit_id
WHERE v.quantity_sold BETWEEN 100 AND 600;

• CROSS JOIN
  • 카티션 프로덕트라고도 하며 모든 데이터의 조합
  • 많이 사용하지는 않지만 간혹 튜닝이나 리포트 작성을 위해 씀
• OUTER JOIN
  • 교차하는 값이 없는 데이터도 반환할 수 있음
  • LEFT OUTER JOIN(좌측테이블 기준), RIGHT OUTER JOIN(우측테이블 기준)
  • FULL OUTER JOIN : 합집합
    
    

3. 계층형 질의와 표준 셀프 조인

계층형 질의

• 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미
• ex) 회사 상부부서-하위부서의 관계
• 이러한 계층형 구조는 트리구조로 보면 쉽지만 테이블로 보면 어려움
  

관련 용어

• LEVEL : 각 데이터의 계층
• NODE : 각각의 데이터
• ROOT : 최상위 노드
• Parent Node : 노드의 상위 노드
• Child Node : 노드의 하위 노드
• Leaf Node : 하위 노드가 없는 노드

Oracle 계층형 질의 구문

a. Oracle 계층형 질의 구문

• SELECT 칼럼
  • 조회하고자 하는 칼럼을 지정합니다.
• FROM 테이블
  • 대상이 되는 테이블을 지정합니다.
• WHERE
  • 모든 전개를 수행하고 나서 지정 조건을 만족하는 데이터만 추출할 수 있습니다.
  • 계층적으로 진행 후에 특정한 조건에 맞는 데이터만 가져옵니다.
• START WITH
  • 계층 구조 전개의 시작 위치를 지정하는 구문입니다.
  • 어디서부터 계층 질의를 시작하는지 루트 데이터(루트 노드 행)를 설정하는 구문입니다.
• CONNECT BY [NOCYCLE]
  • 전개되어질 자식 데이터를 지정하는 구문 (연결 고리)
  • 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 합니다.
  • NOCYCLE
    • 데이터를 전개하면서 "이미 나타났던 동일한 데이터가 전개 중에 다시 나타나는 경우를 가리켜 사이클이 형성되었다"라고 합니다.
    • 이런 사이클이 발생한 데이터는 오류가 발생하는데 NOCYCLE 구문을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않게 할 수 있습니다.
• [PRIOR] A AND B
  • CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정할 수 있습니다.
  • PRIOR 자식 = 부모 형태로 사용
    - 부모 데이터에서 자식 데이터 (부모 → 자식) 방향으로 작성하면 순방향
    - PRIOR 사원 = 관리자
    - 자식 데이터에서 부모 데이터 (자식 → 부모) 방향으로 작성하면 역방향
    - PRIOR 관리자 = 사원
    → 순환 구조를 만든다 == 계층(LEVEL)을 만들어준다
    → 이전의(prior) 컬럼 A가 = 현재의 컬럼 B와 같으면 이전에 가지고 있던 LEVEL에 1을 더해서 하위 LEVEL로 지정

    계층형 질의에서 문제가 나온다면 🤔 START WITH & PRIOR 부분을 해석하는게 핵심!

• OREDER SIBLINGS BY
  • 형제 노드(동일한 LEVEL) 사이에서 정렬을 수행하는 구문입니다.

b. 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공합니다.

• LEVEL
  • 루트 데이터이면 1이 값으로 정해지고 그 하위 데이터이면 2가 값으로 정해집니다.
  • 하위 데이터로 내려갈수록 1씩 증가하게 됩니다.
• CONNECT_BY_ISLEAF
  • 전개 과정에서 해당 데이터가 각 트리 경로의 마지막 값이라면 1이되고 그렇지 않다면 0이 출력됩니다.
• CONNECT_BY_ISCYCLE
  • 전개 과정에서 자식 데이터를 확인합니다.
  • 그 자식 데이터를 전개했을 때 다시 부모 데이터가 정해져 있다면 무한 반복으로 전개가 될 것입니다. 이렇게 무한 반복이 되는 경우 값이 1, 그렇지 않으면 값이 0이 됩니다.
  • 무한 반복을 방지하기 위한 NOCYCLE 옵션을 사용했을 때만 사용할 수 있습니다.

기본구조

SELECT COL,...[LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE]
FROM TB1
WHERE condition AND condition
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY COL1, COL2, ...]

c. (참고) Oracle은 계층형 질의를 사용할 때 사용자 편의성을 위해 추가적인 함수를 제공합니다.

• SYS_CONNECT_BY_PATH(칼럼명, 경로구분자)
  • 루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시해주는 함수입니다.
• CONNECT_BY_ROOT 칼럼
  • 현재 전개할 데이터의 루트 데이터를 표시하는 단일행 연산자 입니다.
• 예시
  • START WITH 로 시작하는 루트 데이터가 1건이기 때문에 CONNECT_BY_ROOT empno 는 전부 1개의 값으로 출력됩니다.
  • SYS_CONNECT_BY_PATH 는 현재 데이터까지의 경로이기 때문에 값이 D라면 A / C / D 로 출력이 됩니다.

4. SELF JOIN

• 동일한 테이블 간의 JOIN
• FROM 절에서 동일 테이블을 두번 이상 JOIN 테이블로 명시하면 됨
• 테이블과 컬럼 모두 동일하기 때문에 반드시 ALIAS를 붙여야 함

SELECT 
       s1.sport_id, 
       s1.sport_name, 
       s2.sport_id "UPPER ID", 
       s2.sport_name "UPPER SPORT"
FROM sport_type s1 LEFT JOIN sport_type s2
  ON s1.main_event_id = s2.sport_id
ORDER BY s1.sport_id;