데이터베이스 기초 (3) - JOIN

데이터베이스 기초 시리즈의 3편입니다. 전체 목차는 0편에 있습니다.

왜 JOIN인가

2편에서 완성한 게시판 schema에서 posts의 user_id column은 users.id를 참조하는 숫자입니다. 그래서 SELECT * FROM posts의 결과에는 글쓴이가 1, 2, 3 같은 숫자로만 나오고, 그 숫자가 alice인지 bob인지는 users table을 따로 조회해야 알 수 있습니다. 글 목록에 글쓴이 이름을 함께 보여주려면 posts의 row와 users의 row를 한 쿼리에서 붙여야 하고, 이 작업을 하는 문법이 JOIN입니다. 실습 환경은 PostgreSQL 편에서 만든 PostgreSQL 18 container와 psql이며, 2편의 table과 시드 데이터를 그대로 사용합니다.

두 table을 붙이는 INNER JOIN

posts와 users를 붙이는 기본 형태입니다.

SELECT p.id, p.title, u.username
FROM posts p
JOIN users u ON p.user_id = u.id
ORDER BY p.id;

FROM posts p의 p는 table 별칭(alias)입니다. 별칭을 선언하면 posts.title을 p.title로 줄여 쓸 수 있고, table이 여러 개일 때 각 column이 어느 table 소속인지 분명해집니다. JOIN 뒤에는 붙일 table을 적고, ON 뒤에는 row를 붙이는 조건을 적습니다. 여기서는 posts의 user_id와 users의 id가 같은 row끼리 붙습니다. 시드 데이터로 따라가면 다음과 같습니다.

posts row	p.user_id	붙는 users row	u.username
1 첫 글입니다	1	id 1	alice
2 PostgreSQL 질문 있습니다	1	id 1	alice
3 중고 키보드 팝니다	2	id 2	bob
4 이번 주 모임 공지	3	id 3	carol

결과는 4개 row이고, 글마다 글쓴이 이름이 붙어서 나옵니다. JOIN이라고만 쓰면 INNER JOIN의 줄임 표기이며, INNER JOIN은 ON 조건이 성립하는 row 조합만 결과에 남깁니다.

없는 쪽을 NULL로 채우는 LEFT JOIN

이번에는 글에 댓글을 붙입니다. 먼저 INNER JOIN으로 실행해 봅니다.

SELECT p.id, p.title, c.body
FROM posts p
JOIN comments c ON c.post_id = p.id
ORDER BY p.id, c.id;

id	title	body
1	첫 글입니다	환영합니다
1	첫 글입니다	반갑습니다
2	PostgreSQL 질문 있습니다	5편을 기다리세요
2	PostgreSQL 질문 있습니다	저도 궁금합니다
2	PostgreSQL 질문 있습니다	감사합니다
3	중고 키보드 팝니다	쿨거 가능한가요

결과는 6개 row이고, 댓글이 없는 4번 글(이번 주 모임 공지)이 결과에서 사라졌습니다. INNER JOIN은 조건이 성립하는 조합만 남기므로, comments에 일치하는 row가 없는 posts row는 버려집니다. 댓글이 없는 글도 목록에 나와야 한다면 LEFT JOIN을 씁니다.

SELECT p.id, p.title, c.body
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
ORDER BY p.id, c.id;

LEFT JOIN은 왼쪽 table(FROM에 적은 posts)의 row를 전부 유지하고, 오른쪽에 일치하는 row가 없으면 오른쪽 column을 NULL로 채웁니다. 결과는 7개 row로 늘어나고, 4번 글은 c.body가 NULL인 row 하나로 결과에 남습니다.

JOIN과 집계

이제 글 목록에 글쓴이와 댓글 수를 함께 보여주는 쿼리를 완성합니다. 글쓴이는 모든 글에 있으므로 users는 INNER JOIN으로 붙이고, 댓글은 없는 글도 있으므로 comments는 LEFT JOIN으로 붙입니다. 그리고 GROUP BY로 같은 글에 속한 row들을 한 묶음으로 모아서 묶음마다 결과 row 하나를 만듭니다. 묶음마다 row 수를 세는 count(*) 같은 함수를 집계 함수라고 합니다. SELECT에 적은 column 중 집계 함수가 아닌 것은 GROUP BY에도 적는다는 규칙만 지키면 되고, 자세한 규칙은 4편에서 다룹니다.

SELECT p.id, p.title, u.username, count(*) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title, u.username
ORDER BY p.id;

id	title	username	comment_count
1	첫 글입니다	alice	2
2	PostgreSQL 질문 있습니다	alice	3
3	중고 키보드 팝니다	bob	1
4	이번 주 모임 공지	carol	1

댓글이 없는 4번 글의 comment_count가 0이 아니라 1로 나옵니다. LEFT JOIN이 4번 글에 대해 comments column이 전부 NULL인 row 하나를 만들었고, count(*)는 값과 무관하게 묶음 안의 row 수를 세기 때문입니다. 반면 count(c.id)는 c.id가 NULL이 아닌 row만 셉니다. 댓글 수를 세려면 count(c.id)로 고칩니다.

SELECT p.id, p.title, u.username, count(c.id) AS comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title, u.username
ORDER BY p.id;

이제 comment_count가 1번 글 2, 2번 글 3, 3번 글 1, 4번 글 0으로 시드 데이터와 일치합니다. 이 쿼리가 0편에서 말한 도달 기준의 첫 번째 항목입니다.

다대다와 junction table

게시판에 tag 기능을 붙입니다. 글 하나에 tag가 여러 개 붙을 수 있고, tag 하나가 글 여러 개에 붙을 수 있습니다. 이런 관계를 다대다(many-to-many)라고 합니다. posts에 tag_id column을 두면 글 하나에 tag를 하나만 달 수 있으므로 다대다를 표현할 수 없습니다. 다대다는 두 table 사이에 연결만 저장하는 세 번째 table을 두어 표현하고, 이 table을 junction table이라고 합니다.

CREATE TABLE tags (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  BIGINT REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

INSERT INTO tags (name) VALUES ('잡담'), ('질문'), ('거래'), ('공지');
INSERT INTO post_tags VALUES (1, 1), (2, 2), (3, 3), (4, 4), (2, 1);

post_tags의 row 하나가 글과 tag의 연결 하나입니다. 글을 지우면 연결 row도 함께 지워지도록 post_id 쪽에만 ON DELETE CASCADE를 붙였고, 글이 달려 있는 tag는 삭제가 거부됩니다. PRIMARY KEY (post_id, tag_id)는 두 column의 조합을 PK로 삼는 복합 PRIMARY KEY입니다. 같은 글에 같은 tag를 두 번 다는 것은 막히고, 한 글에 여러 tag를 다는 것과 한 tag를 여러 글에 다는 것은 허용됩니다. 시드에서 2번 글에는 tag가 2개(질문, 잡담) 달렸습니다. tag별 글 목록은 tags, post_tags, posts 세 table을 JOIN 두 번으로 이어서 뽑습니다.

SELECT t.name, p.title
FROM tags t
JOIN post_tags pt ON pt.tag_id = t.id
JOIN posts p ON p.id = pt.post_id
ORDER BY t.id, p.id;

name	title
잡담	첫 글입니다
잡담	PostgreSQL 질문 있습니다
질문	PostgreSQL 질문 있습니다
거래	중고 키보드 팝니다
공지	이번 주 모임 공지

tag가 2개인 2번 글은 결과에 두 번 나옵니다. 여기에 WHERE t.name = ‘잡담’ 같은 조건을 더하면 특정 tag의 글 목록이 됩니다.

MySQL 차이

이 편에서 다룬 JOIN 문법은 MySQL 8.4에서 동일하게 동작합니다. INNER JOIN, LEFT JOIN, ON, table 별칭, count(*)와 count(column)의 차이, junction table 설계까지 모두 같습니다. 따라서 이 편의 조회 쿼리는 수정 없이 MySQL 8.4에서 그대로 실행됩니다. 다만 tags와 post_tags를 만드는 CREATE TABLE은 2편의 MySQL 차이에서 본 대로 BIGSERIAL을 BIGINT AUTO_INCREMENT PRIMARY KEY로 바꾸고, inline REFERENCES 대신 FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE 같은 table 수준 표기로 선언해야 합니다.

다음 편

다음 편 데이터베이스 기초 (4) - Aggregation, Subqueries, and Window Functions에서는 GROUP BY의 규칙을 제대로 다루고 subquery, CTE, window function으로 집계를 확장합니다.