SELF JOIN을 활용한 SQL 쿼리

다음 양식에 따라 내용을 작성하겠습니다. 최근에 보내주신 사진에 따르면 “SELF JOIN”에 대한 내용으로 보입니다.

1. 핵심 원리

SELF JOIN은 하나의 테이블을 마치 두 개의 테이블처럼 사용하여 자신과 조인하는 방식입니다. 이는 동일한 테이블 내의 데이터를 비교하거나 계층 구조를 나타낼 때 유용합니다. 주로 자신과의 비교, 예를 들어, 상위와 하위 관계를 분석하거나, 특정 기준에 따른 그룹을 형성할 때 사용됩니다.

2. 실무에서 쓰이는 이유

SELF JOIN은 실무에서 주로 조직 내의 계층 구조, 예를 들어 사원과 그 사원의 상사를 매핑하거나, 제품 카탈로그에서 상위 제품과 하위 제품을 연결하는 등, 데이터 간의 관계를 명확히 해야 할 때 사용됩니다. 또한, 동일 테이블 내의 복수 조건 비교, 이력 데이터 분석 등 다양한 상황에서 활용됩니다.

3. 실무에서 구체적으로 사용되는 상황 예측

• 상사와 부하 직원의 관계 분석: 직원 테이블에서 상사와 그 상사에게 속한 부하 직원을 분석하는 경우.
• 제품 계층 분석: 제품 테이블에서 상위 카테고리와 하위 카테고리 제품을 연계하는 경우.
• 부모-자식 관계 분석: 예를 들어, 각 직원의 부서와 해당 부서의 상위 부서를 연결할 때.

4. 자주 사용되는 윈도우 표시 및 설명

SELF JOIN과 자주 사용되는 윈도우 함수 및 연산자들은 다음과 같습니다:

• ROW_NUMBER(): 각 행에 순위를 부여할 때 사용하며, 특정 조건에 따라 데이터를 나열하고 그 순서를 구할 수 있습니다.
• 예시: 각 사원에 대해 월급 순으로 순위를 매긴다.
• RANK(): 동일한 값이 있을 경우 같은 순위를 부여하되, 다음 순위는 중복된 수만큼 건너뛰는 순위 부여 방식입니다.
• 예시: 동일한 월급을 받는 직원들이 있을 경우, 같은 순위를 부여하고 다음 순위는 그만큼 건너뛰게 됩니다.
• DENSE_RANK(): 동일한 값이 있을 경우 같은 순위를 부여하되, 다음 순위는 연속적으로 부여하는 방식입니다.
• 예시: 동일한 월급을 받는 직원들이 있어도, 다음 순위는 연속적으로 부여됩니다.
• PARTITION BY: 특정 조건에 따라 데이터를 그룹화하여, 각 그룹 내에서 개별적으로 순위를 매깁니다.
• 예시: 부서별로 월급 순위를 매기고 싶을 때 사용합니다.

5. 연습 문제

사원 테이블(emp)에서 사원과 해당 사원의 상사를 조회하세요. 상사는 동일 테이블의 다른 사원 중 해당 사원의 상사입니다.

SELECT e.ename AS 사원, m.ename AS 관리자
FROM emp e, emp m
WHERE e.mgr = m.empno;

6. 연습 문제 해답 및 테이블 표시

위 쿼리를 실행한 결과는 아래와 같습니다:

사원	관리자
MARTIN	BLAKE
ALLEN	BLAKE
TURNER	BLAKE
WARD	BLAKE

7. 응용 문제

각 부서별로 사원과 그 사원의 상사를 조회하세요. 상사는 동일 테이블의 다른 사원 중 해당 사원의 상사입니다.

SELECT e.deptno, e.ename AS 사원, m.ename AS 관리자
FROM emp e, emp m
WHERE e.mgr = m.empno;

8. 응용 문제 해답 및 테이블 표시

위 쿼리를 실행한 결과는 아래와 같습니다:

부서번호	사원	관리자
30	MARTIN	BLAKE
30	ALLEN	BLAKE
30	TURNER	BLAKE
30	WARD	BLAKE

9. 전자상거래 업계에서 쓰일 수 있는 경우 예측

전자상거래에서는 제품 카테고리의 상하 관계나, 주문과 그에 따른 서브 주문 등을 SELF JOIN을 통해 분석할 수 있습니다. 예를 들어, 특정 제품의 메인 카테고리와 서브 카테고리를 조회하거나, 메인 주문과 관련된 서브 주문들을 조회하는 경우에 활용될 수 있습니다.

10. 전자상거래 업계에서 사용할 수 있는 경우 예상 상황 문제 2가지 및 그 경우에 대한 SQL 코드 작성

1. 문제 1: 주문 테이블에서 메인 주문과 그에 속한 서브 주문을 조회하세요.

   SELECT o1.order_id AS 메인주문, o2.order_id AS 서브주문
   FROM orders o1, orders o2
   WHERE o1.order_id = o2.parent_order_id;

2. 문제 2: 제품 카테고리 테이블에서 메인 카테고리와 그에 속한 서브 카테고리를 조회하세요.

   SELECT c1.category_name AS 메인카테고리, c2.category_name AS 서브카테고리
   FROM categories c1, categories c2
   WHERE c1.category_id = c2.parent_category_id;

11. 10번에 대한 SQL 코드를 시각적으로 이해하기 쉽도록 테이블 예시 작성

문제 1: 주문 테이블의 메인 주문과 서브 주문 결과 예시:

메인주문	서브주문
10001	10011
10001	10012
10002	10021

문제 2: 제품 카테고리 테이블의 메인 카테고리와 서브 카테고리 결과 예시:

메인카테고리	서브카테고리
전자제품	스마트폰
전자제품	노트북
가전제품	냉장고