[SQL 기초] Self 조인

물박사의 저장공간 2025. 3. 17. 00:18

2025.02.24 - [프로그래밍/SQL, Hive, SAS 관련 정보] - [SQL] Table of Contents

셀프 조인(Self Join)은 어떤 테이블을 자기 자신과 조인하는 방법입니다. 테이블 내에서 행 간의 관계를 분석하거나 비교할 때 유용하게 사용할 수 있습니다. 셀프 조인을 사용하는 대표적인 예를 한 번 살펴보겠습니다.

1. Hierarchical Data :

예를 들어서 테이블이 하나 있는데 여기에 Employee와 Manager가 모두 한꺼번에 들어있다고 해보겠습니다. 여기서 employer와 그 manager 관계를 조회하고 싶을 때 Self Join을 활용하면 됩니다.

SELECT
    e.id,
    e.first_name,
    e.last_name,
    e.salary,
    m.first_name AS boss_first_name,
    m.last_name AS boss_last_name
FROM employee AS e
INNER JOIN employee AS m
    ON e.manager_id = m.id;

2. Sequential Data

말 그대로 정보에 순차성이 존재하는데 각 단계의 다음 정보를 보여주려 한다면 Self Join을 해주어야겠죠.

SELECT
    i1.id AS step_id,
    i1.content AS step_content,
    i2.id AS next_step_id,
    i2.content AS next_step_content
FROM instruction i1
LEFT JOIN instruction i2 ON i1.next_id = i2.id;

3. Graphs (Multiple relationships within the same Table)

Network Analysis에서 많이 등장하는 상황일 겁니다. 테이블 안의 항목끼리 다중관계를 이루고 있는 경우입니다.

SELECT
    start.name AS start_city,
    destination.name AS destination_city
FROM city AS start
INNER JOIN route AS r ON start.id = r.from_city_id
INNER JOIN city AS destination ON destination.id = r.to_city_id;

참고) https://learnsql.com/blog/illustrated-guide-sql-self-join/

이를 응용해서 적용해 볼 수도 있겠죠. 만약 장바구니에서 두 상품을 동시에 담은 장바구니의 수를 조회하는 쿼리를 작성한다면 아래와 같은 방법을 생각해 볼 수 있습니다.

WITH DistinctCartProducts AS (
    SELECT DISTINCT CART_ID, NAME  -- 1. 장바구니별 중복 제거
    FROM CART_PRODUCTS
)
SELECT 
    a.NAME AS X,
    b.NAME AS Y,
    COUNT(DISTINCT a.CART_ID) AS COUNT
FROM DistinctCartProducts a
JOIN DistinctCartProducts b
    ON a.CART_ID = b.CART_ID
    AND a.NAME < b.NAME  -- 2. 이름 순서 고정 (사전식 정렬)
GROUP BY a.NAME, b.NAME  -- 3. 상품 조합별 그룹화
ORDER BY X, Y;  -- 4. 결과 정렬

또, row_number(), rank() 등을 사용하기 어려운 경우에도 self join을 써먹을 수도 있습니다(raw table을 재정렬해서 번호를 붙이는 게 아니라 raw table 원본 그 상태에서 앞/뒤 행과의 비교 등을 수행)

https://leetcode.com/problems/consecutive-numbers/?envType=study-plan-v2&envId=top-sql-50

이 문제가 보기보다 까다로운 것이

select id, num, row_number() over(order by num) rn
from logs

select id, num, row_number() over(partition by num order by id) rn
from logs

num을 기준으로 partition을 치긴해야하는데 order by가 아니라 partition만을 num 기준으로 설정해도 알아서 "같은 파티션"인 num을 그룹핑해서(즉, 원본 순서가 정렬되어) 번호를 매깁니다. 이런 접근으로는 어려워진 것이죠..

그래서 이럴 때

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
INNER JOIN logs l2 ON l2.id = l1.id + 1 AND l2.num = l1.num
INNER JOIN logs l3 ON l3.id = l2.id + 1 AND l3.num = l2.num

와 같이 Self join을 해주면 원하는 결과를 이끌어 낼 수 있습니다. (참고로 INNER를 생략하고 JOIN이라고만 쓰면 DBMS에서 INNER JOIN으로 이해합니다)