SQLite Self Join: 같은 테이블 자기 자신과 조인하기

셀프 조인은 결국 별칭을 붙인 조인일 뿐

sqlite의 self join은 사실 특별할 게 없습니다. 양쪽이 같은 테이블이라는 점만 빼면 평범한 JOIN 그 자체죠. 다만 SQLite가 두 복사본을 구분할 방법이 필요하기 때문에, 각각에 별칭(alias)을 붙여줘야 합니다.

self join을 쓰는 상황은 정해져 있습니다. 한 행이 같은 테이블의 다른 행을 참조할 때입니다. 가장 대표적인 예가 바로 사원-관리자 관계예요. employees 테이블의 각 행에 다른 사원을 가리키는 manager_id가 들어 있는 구조입니다:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada는 매니저가 없습니다. Boris와 Cleo는 Ada에게 보고하고, Diego와 Esme는 Boris에게 보고하죠. 이 관계는 전부 하나의 테이블 안에 들어 있습니다 — 바로 이런 상황이 sqlite self join이 진가를 발휘하는 순간입니다.

셀프 조인의 기본 형태

각 직원과 그 직원의 매니저 이름을 짝지어 보고 싶다면, employees 테이블을 자기 자신과 조인하면 됩니다. 한쪽은 "직원" 역할, 다른 한쪽은 "매니저" 역할을 맡는 거죠:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

같은 테이블이지만 마치 서로 다른 두 테이블처럼 읽으면 됩니다. e는 사원 행, m은 관리자 행이라고 보세요. 조인 조건 e.manager_id = m.id가 이 둘을 연결해 줍니다. 즉, 각 사원에 대해 그 사원의 manager_id와 id가 일치하는 행을 m에서 찾아 짝을 맞추는 것이죠.

그런데 결과에 Ada가 빠진 게 보이시나요? Ada의 manager_id는 NULL인데, INNER JOIN은 매칭되지 않는 행을 그냥 버려 버리기 때문입니다.

매칭 안 되는 행도 살리기: LEFT JOIN

관리자가 없는 사람까지 포함해서 모든 직원을 결과에 담고 싶다면 LEFT JOIN으로 바꾸면 됩니다:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

이제 Ada가 결과에 등장하는데, 매니저 컬럼은 NULL로 채워집니다. self join 동작 방식은 동일하고, 조인 타입만 LEFT JOIN이 늘 하던 일을 합니다 — 왼쪽 테이블의 모든 행은 그대로 두고, 매칭이 없는 자리는 빈칸으로 메우는 거죠.

사람 목록을 화면에 뿌려줄 때는 보통 이 형태를 쓰게 됩니다. "매니저 없음"도 엄연한 정보니까요. 행을 통째로 빼버리면 안 되죠.

self join에서 별칭은 선택이 아니다

별칭 없이 self join을 시도해 보면, SQLite는 무슨 말인지 도저히 알아듣지 못합니다:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- 오류: 모호한 열 이름: name

모든 컬럼이 두 번씩 — 테이블의 사본마다 하나씩 — 등장하기 때문에 SQLite는 어떤 걸 골라야 할지 알 수 없습니다. 별칭(alias)을 붙여 각 인스턴스에 고유한 이름을 주면 이 문제가 해결되죠. 별칭은 테이블 이름이 아니라 그 행이 맡고 있는 역할을 드러내도록 짓는 게 좋습니다:

• 사원/관리자 관계라면 e와 m.
• 계층 구조라면 parent와 child.
• 임의의 두 행을 비교한다면 a와 b.

self join이 깔끔하게 읽히는 비결은 결국 이 별칭에 있습니다.

같은 테이블 안에서 쌍 찾기

셀프 조인은 계층 구조 전용이 아닙니다. 같은 테이블 안의 행끼리 비교하고 싶을 때라면 언제든 어울리는 패턴이죠. 예를 들어 아래는 상품 목록인데, 가격이 같은 모든 쌍을 뽑아내고 싶다고 해봅시다:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('머그컵',   9.99),
    ('노트',     9.99),
    ('펜',       2.50),
    ('연필',     2.50),
    ('램프',    29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

여기서 두 가지를 짚어 보자. 먼저 a.price = b.price는 실제로 매칭하는 조건이다. 그리고 a.id < b.id는 같은 쌍이 두 번 나오는 걸 막아 준다 — 예를 들어 머그컵, 노트이 한 번 나오고 노트, 머그컵로 또 한 번 나오는 일을 방지하고, 자기 자신과 짝을 이루는 것도 같이 막아 준다. 이 < 트릭은 꼭 기억해 두자. 쌍을 나열해야 하는 상황이라면 어김없이 등장한다.

두 단계 위까지 거슬러 올라가기

셀프 조인 한 번이면 계층 구조에서 한 단계를 처리할 수 있다. 그렇다면 각 사원의 "상사의 상사"까지 알고 싶다면? 세 번 조인하면 된다.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

별칭(alias)을 하나 더 추가할 때마다 트리에서 한 단계씩 더 올라가는 셈입니다. 두세 단계 정도라면 이 방식도 괜찮지만, 그 이상이 되면 금세 한계가 드러납니다. 쿼리를 작성하는 시점에 계층의 깊이를 미리 알고 있어야 하고, 단계마다 조인을 하나씩 더 붙여야 하니까요. 바로 이 벽을 깨기 위해 만들어진 게 재귀 CTE입니다.

self join을 쓰지 말아야 할 때

self join은 결과에 관계 양쪽의 컬럼이 모두 필요할 때 적합한 도구입니다. 단순히 필터링만 하면 되는 경우 — 예를 들어 관리자가 Ada인 사원을 모두 찾는 정도라면 — 서브쿼리로 작성하는 편이 더 깔끔하게 읽힙니다.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

별칭으로 곡예를 부릴 일도 없고, 의도도 한눈에 들어옵니다. 판단 기준은 간단합니다. 결과에 양쪽 행의 데이터가 모두 필요한가? 그렇다면 self join. 단순히 비교할 값 하나만 필요한가? 그렇다면 서브쿼리입니다.

다만 깊이가 정해지지 않은 계층 구조(조직도, 파일 트리, 댓글 스레드 같은 것들)에는 두 방식 모두 한계가 있습니다. 이런 경우는 재귀 CTE의 영역이죠.

다음 주제: 서브쿼리

self join과 서브쿼리는 해결할 수 있는 문제 영역이 겹치기 때문에, 어느 쪽이 적합한지 미리 감을 잡아두면 나중에 SQL을 들여다보며 머리 싸맬 일이 줄어듭니다. 다음 글에서는 서브쿼리를 본격적으로 파헤칩니다 — 스칼라 서브쿼리, 상관 서브쿼리, 그리고 IN 형태까지, 각각이 빛을 발하는 상황을 짚어볼게요.