셀프 조인과 별칭

자체 조인: 테이블을 자기 자신과 조인하기

설정

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

직원과 관리자를 조인하기

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

별칭이 없으면 컴파일되지 않습니다

-- ERROR: column reference "full_name" is ambiguous
SELECT full_name FROM employees JOIN employees ON ...

최상위 직원을 위한 LEFT JOIN

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

스레드형 댓글

SELECT c.id, c.body, p.body AS parent_body
FROM comments c
LEFT JOIN comments p ON p.id = c.parent_id;

쌍과 조합

SELECT a.id AS product_a, b.id AS product_b
FROM order_items a
JOIN order_items b
  ON a.order_id = b.order_id
 AND a.product_id < b.product_id;

인접한 행 비교하기

SELECT a.id, a.created_at, b.created_at AS prev_at
FROM events a
LEFT JOIN events b ON b.id = a.previous_id;

자체 조인과 윈도 함수 비교

재귀적 자체 조인

FK 열에 인덱스 추가하기

CREATE INDEX employees_manager_idx ON employees(manager_id);

복습

빠른 확인