SQL JOIN

src/content/documents/data-analysis/sql-join-types-and-algorithms.json

한 줄 정의

JOIN은 두 행 집합의 관련 행을 조건으로 연결하며, JOIN 종류는 결과에 남길 행을, JOIN 알고리즘은 PostgreSQL이 그 결과를 계산하는 방법을 결정한다.

“길드원의 승리 횟수”를 구하려면 플레이어, 길드, 매치에 흩어진 값을 이어야 한다. INNER와 LEFT는 SQL의 의미이고, Hash Join과 Nested Loop는 옵티마이저가 통계와 비용을 바탕으로 선택하는 물리 실행 방식이다. 둘을 섞어 생각하지 않는 것이 핵심이다.

실습 데이터 만들기

루나는 길드 10, 카이는 길드 20에 속하고 솔은 무소속이다. 길드 30은 아직 회원이 없다. 매치 101은 루나와 카이, 102는 루나와 솔이 겨뤘다. 아래 SQL은 PostgreSQL의 빈 데이터베이스에서 실행할 수 있다.

DROP TABLE IF EXISTS match_participants, matches, players, guilds;

CREATE TABLE guilds (
  guild_id integer PRIMARY KEY,
  guild_name text NOT NULL UNIQUE
);
CREATE TABLE players (
  player_id integer PRIMARY KEY,
  nickname text NOT NULL UNIQUE,
  guild_id integer REFERENCES guilds(guild_id)
);
CREATE TABLE matches (
  match_id integer PRIMARY KEY,
  played_at timestamptz NOT NULL
);
CREATE TABLE match_participants (
  match_id integer REFERENCES matches(match_id),
  player_id integer REFERENCES players(player_id),
  score integer NOT NULL CHECK (score >= 0),
  PRIMARY KEY (match_id, player_id)
);

INSERT INTO guilds VALUES (10, '달빛'), (20, '태양'), (30, '별빛');
INSERT INTO players VALUES
  (1, '루나', 10), (2, '카이', 20), (3, '솔', NULL);
INSERT INTO matches VALUES
  (101, '2026-08-10 10:00+09'), (102, '2026-08-10 11:00+09');
INSERT INTO match_participants VALUES
  (101, 1, 12), (101, 2, 8), (102, 1, 7), (102, 3, 15);

INNER JOIN: 양쪽에 모두 있는 행

INNER JOIN은 ON 조건이 참인 쌍만 남긴다. 따라서 무소속 플레이어 솔과 회원이 없는 별빛 길드는 결과에서 사라진다. JOIN이라고만 쓰면 INNER JOIN과 같다.

SELECT p.nickname, g.guild_name
FROM players AS p
INNER JOIN guilds AS g ON g.guild_id = p.guild_id
ORDER BY p.player_id;
 nickname | guild_name
----------+-----------
 루나     | 달빛
 카이     | 태양

LEFT·RIGHT·FULL OUTER JOIN: 짝 없는 행도 보존한다

LEFT JOIN

LEFT JOIN은 왼쪽 players를 전부 보존한다. 짝이 없는 솔의 오른쪽 guild_name은 NULL이다. “모든 플레이어와, 있다면 길드”라는 요구에 맞는다.

SELECT p.nickname, g.guild_name
FROM players AS p
LEFT JOIN guilds AS g ON g.guild_id = p.guild_id
ORDER BY p.player_id;
 nickname | guild_name
----------+-----------
 루나     | 달빛
 카이     | 태양
 솔       | NULL

RIGHT JOIN

RIGHT JOIN은 오른쪽 guilds를 전부 보존해 회원 없는 별빛도 보여 준다. 같은 결과를 테이블 순서를 바꾼 LEFT JOIN으로 쓸 수 있어, 팀에서는 읽기 방향을 통일하려고 LEFT JOIN을 더 자주 선택하기도 한다.

SELECT p.nickname, g.guild_name
FROM players AS p
RIGHT JOIN guilds AS g ON g.guild_id = p.guild_id
ORDER BY g.guild_id;
 nickname | guild_name
----------+-----------
 루나     | 달빛
 카이     | 태양
 NULL     | 별빛

FULL JOIN

FULL JOIN은 양쪽의 짝 없는 행을 모두 보존한다. 운영 데이터 대조에서 “길드 없는 플레이어”와 “회원 없는 길드”를 한 번에 찾을 때 유용하다.

SELECT p.nickname, g.guild_name
FROM players AS p
FULL JOIN guilds AS g ON g.guild_id = p.guild_id
ORDER BY p.player_id NULLS LAST, g.guild_id;
 nickname | guild_name
----------+-----------
 루나     | 달빛
 카이     | 태양
 솔       | NULL
 NULL     | 별빛

LEFT JOIN 뒤 WHERE g.guild_name = '달빛'처럼 오른쪽 컬럼을 필터링하면 NULL 행이 제거되어 사실상 INNER JOIN처럼 된다. 보존 행을 유지하며 오른쪽 대상만 제한하려면 조건을 ON 절에 넣을지 의도적으로 결정한다.

CROSS JOIN: 모든 조합을 만든다

CROSS JOIN은 ON 조건 없이 왼쪽 각 행과 오른쪽 각 행을 결합하는 카테시안 곱이다. 3명의 플레이어와 2개의 매치로 모든 참가 후보 조합 6개를 만들 수 있다.

∣A×B∣=∣A∣⋅∣B∣=3⋅2=6|A\times B|=|A|\cdot|B|=3\cdot2=6
SELECT p.nickname, m.match_id
FROM players AS p
CROSS JOIN matches AS m
ORDER BY p.player_id, m.match_id;
 nickname | match_id
----------+---------
 루나     | 101
 루나     | 102
 카이     | 101
 카이     | 102
 솔       | 101
 솔       | 102

CROSS JOIN은 캐릭터×난이도 조합표처럼 모든 경우가 필요할 때 정확하다. 하지만 실수로 JOIN 조건을 빠뜨리면 행 수가 곱으로 폭증하므로 예상 행 수를 먼저 계산한다.

매치 점수에서 JOIN의 중복을 이해한다

JOIN은 왼쪽 행 하나를 반드시 한 번만 돌려주지 않는다. 루나는 두 매치 참가 행과 연결되므로 두 번 나온다. 매치별 참가자를 보는 데는 맞지만 플레이어 수를 세려면 중복을 고려해야 한다.

SELECT p.nickname, mp.match_id, mp.score
FROM players AS p
JOIN match_participants AS mp USING (player_id)
ORDER BY mp.match_id, mp.score DESC;

SELECT count(*) AS participant_rows,
       count(DISTINCT player_id) AS unique_players
FROM match_participants;
 nickname | match_id | score
----------+----------+------
 루나     |      101 |    12
 카이     |      101 |     8
 솔       |      102 |    15
 루나     |      102 |     7

 participant_rows | unique_players
------------------+---------------
                4 |             3

세 가지 JOIN 실행 알고리즘

알고리즘

잘 맞는 상황

주의점

Nested Loop

바깥쪽 결과가 작고 안쪽 키 인덱스 탐색이 빠를 때

안쪽 전체 스캔이 반복되면 비쌈

Hash Join

정렬되지 않은 큰 집합의 동등 조건

해시 메모리와 배치·디스크 사용

Merge Join

양쪽이 JOIN 키 순서로 준비됐을 때, 큰 입력

정렬이 필요하면 비용 증가

Nested Loop

바깥쪽의 각 행마다 안쪽에서 짝을 찾는다. 단순 비교라면 최대 비교 횟수는 두 입력 행 수의 곱에 비례한다.

Cnested≈Nouter×CinnerLookupC_{nested}\approx N_{outer}\times C_{innerLookup}

player_id 하나로 참가 기록을 찾고 적절한 인덱스가 있다면 안쪽 탐색이 작아져 효율적이다. 반대로 수십만 바깥 행마다 안쪽 전체를 읽으면 급격히 느려진다.

Hash Join

보통 작은 입력으로 JOIN 키 해시 테이블을 만들고 큰 입력의 키로 탐색한다. 동등 조건에 사용되며 이상적인 시간 복잡도를 거칠게 쓰면 다음과 같다.

Chash≈O(N+M),Memory≈O(min⁡(N,M))C_{hash}\approx O(N+M),\qquad Memory\approx O(\min(N,M))

해시가 work_mem에 맞지 않으면 여러 배치로 나뉘고 임시 파일 I/O가 생길 수 있다. EXPLAIN ANALYZE의 Batches와 Memory Usage를 확인한다.

Merge Join

양쪽 입력을 JOIN 키 순서로 훑으며 같은 키를 만날 때 결합한다. 인덱스가 이미 순서를 제공하면 정렬을 피할 수 있다. 정렬부터 해야 한다면 대략 다음 비용이 더해진다.

Cmerge≈O(Nlog⁡N+Mlog⁡M+N+M)C_{merge}\approx O(N\log N+M\log M+N+M)

EXPLAIN으로 PostgreSQL의 선택 읽기

EXPLAIN은 추정 계획, EXPLAIN ANALYZE는 쿼리를 실제 실행한 측정값을 보여 준다. INSERT·UPDATE·DELETE에 ANALYZE를 붙이면 실제 변경되므로 트랜잭션에서 ROLLBACK하거나 복제 환경에서 주의한다. 작은 실습 데이터에서는 Seq Scan이나 Nested Loop가 가장 싸게 추정될 수 있으며 출력은 버전·통계·설정에 따라 달라진다.

ANALYZE players;
ANALYZE match_participants;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.nickname, mp.match_id, mp.score
FROM players AS p
JOIN match_participants AS mp ON mp.player_id = p.player_id
WHERE p.player_id = 1;
Nested Loop  (cost=... rows=... width=...) (actual time=... rows=2 loops=1)
  ->  Seq Scan on players p  (... actual ... rows=1 loops=1)
        Filter: (player_id = 1)
  ->  Seq Scan on match_participants mp (... actual ... rows=2 loops=1)
        Filter: (player_id = 1)
Planning Time: ... ms
Execution Time: ... ms

수치는 환경마다 다르며 핵심은 노드, 추정 rows, 실제 rows, loops, Buffers다.

cost는 밀리초가 아니라 PostgreSQL 비용 모델의 상대 단위다. 괄호의 첫 cost는 첫 행을 내기 전 시작 비용, 둘째는 모든 행을 낼 때 총비용이다. 실제 rows와 추정 rows 차이가 크면 오래된 통계, 컬럼 상관관계, 데이터 치우침을 의심하고 ANALYZE와 확장 통계를 검토한다.

EstimationRatio=actual rowsestimated rowsEstimationRatio=\frac{actual\ rows}{estimated\ rows}

비율이 1에 가까울수록 행 수 추정이 잘 맞는다. 한 노드의 actual rows는 기본적으로 loop당 값이므로 전체 처리량을 볼 때 loops를 함께 읽는다. 알고리즘 설정을 억지로 끄기 전에 통계와 인덱스, 조건 선택도를 먼저 점검한다.

JOIN 문제 해결 체크리스트

  • 먼저 보존해야 할 기준 집합을 문장으로 쓰고 INNER 또는 OUTER JOIN을 고른다.

  • PK·FK 관계가 1:1인지 1:N인지 확인하고 예상 결과 행 수를 계산한다.

  • OUTER JOIN의 오른쪽 조건을 WHERE에 둘지 ON에 둘지 NULL 보존 의도로 결정한다.

  • 실제와 비슷한 데이터량에서 EXPLAIN (ANALYZE, BUFFERS)로 추정·실제 행과 I/O를 비교한다.

  • Nested Loop·Hash·Merge 중 이름만 보고 좋고 나쁨을 판단하지 말고 입력 크기, 정렬, 인덱스, 메모리와 측정값을 본다.

참고 자료

댓글 0

댓글을 불러오는 중…