JOIN — 테이블을 잇기

나눈 것을 다시 잇습니다

1.1 에서 회원과 글을 따로 담고 번호로 가리키기로 했습니다. 글 테이블에는 작성자의 이름 대신 user_num 만 있습니다.

그런데 화면에는 번호가 아니라 이름이 나와야 합니다. 두 테이블을 이어 한 결과로 내는 일이 조인이고, JOIN … ON 으로 적습니다. ON 뒤에 무엇으로 이을지를 적습니다.

INNER JOIN — 양쪽에 다 있는 것만

SELECT TOP 4 P.num, P.title, U.nickname
FROM Board.POSTS AS P
    INNER JOIN Member.USERS AS U ON U.num = P.user_num
ORDER BY P.num;

AS P · AS U 는 테이블에 붙인 짧은 이름입니다. 열 이름이 겹칠 때(num 이 양쪽에 있습니다) 어느 쪽인지 밝혀야 하므로 조인에서는 거의 늘 붙입니다.

INNER JOIN 은 양쪽에서 짝을 찾은 행만 남깁니다. 글의 user_num 에 해당하는 회원이 없다면 그 글은 결과에서 빠집니다.

짝이 없으면 사라집니다

말머리로 확인해 보겠습니다. 공지사항은 말머리를 사용하지 않으므로 category_num 이 NULL 입니다.

SELECT COUNT(*) AS inner_건수
FROM Board.POSTS AS P
    INNER JOIN Board.CATEGORIES AS C ON C.num = P.category_num;

SELECT COUNT(*) AS left_건수
FROM Board.POSTS AS P
    LEFT JOIN Board.CATEGORIES AS C ON C.num = P.category_num;

글은 모두 507건인데 INNER JOIN 은 472건만 냈습니다. 말머리가 없는 공지사항 35건이 빠진 것입니다.

LEFT JOIN 은 왼쪽 테이블의 행을 모두 남기고, 오른쪽에서 짝을 못 찾으면 그 열을 NULL 로 채웁니다.

SELECT TOP 4 P.num, P.board_num, P.title, C.name AS 말머리
FROM Board.POSTS AS P
    LEFT JOIN Board.CATEGORIES AS C ON C.num = P.category_num
WHERE P.board_num = 1
ORDER BY P.num;

공지사항이 남았고 말머리 자리가 NULL 입니다. 화면에 낼 때는 COALESCE(C.name, N'없음') 으로 채우면 됩니다(1.9).

첨부는 차이가 더 큽니다

첨부가 있는 글은 507건 가운데 100건뿐입니다.

목록 화면에서 글이 통째로 안 보이는 일이 흔히 이 자리에서 생깁니다. 첨부·댓글·프로필처럼 없을 수도 있는 것을 이을 때는 LEFT JOIN 입니다.

LEFT JOIN 을 INNER 로 만들어 버리는 실수

이 단원에서 가장 중요한 이야기입니다. LEFT JOIN 을 적어 놓고 오른쪽 테이블의 열을 WHERE 에 걸면, 조인이 INNER 로 바뀝니다.

-- 조건을 WHERE 에 두면
SELECT COUNT(*) AS where로걸면
FROM Board.POSTS AS P
    LEFT JOIN Board.FILES AS F ON F.post_num = P.num
WHERE F.origin_name LIKE N'%.png';

-- 같은 조건을 ON 에 두면
SELECT COUNT(*) AS ON에넣으면
FROM Board.POSTS AS P
    LEFT JOIN Board.FILES AS F
        ON F.post_num = P.num AND F.origin_name LIKE N'%.png';

507 이 25 가 됐습니다. 까닭은 실행되는 차례에 있습니다.

1. LEFT JOIN 이 먼저 돕니다. 507행이 나오고, 첨부가 없는 글은 F.origin_name 이 NULL 입니다.
2. 그다음 WHERE 가 걸립니다. NULL LIKE '%.png' 는 참이 아니므로(1.9) 그 407건이 통째로 빠집니다.

ON 에 두면 이을 때의 조건이 되므로, 짝을 못 찾은 글은 그대로 남고 그 열만 NULL 이 됩니다.

왼쪽 테이블의 조건은 WHERE 에, 오른쪽 테이블의 조건은 ON 에 두는 것이 요령입니다.

조인 조건을 빠뜨리면

ON 을 적지 않으면 왼쪽의 모든 행이 오른쪽의 모든 행과 짝지어집니다. 이것을 CROSS JOIN 이라 합니다.

SELECT COUNT(*) AS 조건없이
FROM Board.BOARDS AS B
    CROSS JOIN Board.CATEGORIES AS C;

게시판 3건과 말머리 10건이 30건이 됐습니다. 행 수가 곱해집니다. 507건과 20건을 조건 없이 이으면 10,140건이 되고, 수십만 건짜리 테이블이라면 서버가 멈춥니다.

일부러 사용하는 자리도 있습니다. 날짜 목록과 게시판 목록을 곱해 빈칸 없는 통계표를 만들 때입니다(6.8). 그런 뜻이 아니라면 ON 을 빠뜨린 것이니 확인하십시오.

나머지 두 가지

RIGHT JOIN 은 거의 사용하지 않습니다. 테이블 차례를 바꾸면 LEFT JOIN 으로 같은 결과를 낼 수 있고, 읽는 사람이 "왼쪽이 기준" 이라는 한 가지 규칙만 기억하면 되기 때문입니다.

셋 이상 잇기

JOIN 을 이어서 적습니다. 종류를 섞어도 됩니다.

SELECT TOP 3 P.num, P.title, U.nickname, C.name AS 말머리
FROM Board.POSTS AS P
    INNER JOIN Member.USERS AS U ON U.num = P.user_num
    LEFT JOIN Board.CATEGORIES AS C ON C.num = P.category_num
WHERE P.board_num = 2
ORDER BY P.num;

작성자는 반드시 있으므로 INNER, 말머리는 없을 수 있으므로 LEFT 입니다. 한 번 LEFT 로 이은 테이블에 다시 무언가를 이을 때는 그것도 LEFT 여야 합니다. 중간이 NULL 이면 그 뒤도 짝을 찾을 수 없기 때문입니다.

직접 해보기