조인

따로 담긴 두 표를 짝지어 한 표로 이어 붙이는 일입니다. 어느 행과 어느 행이 한 짝인지는 조건이 정합니다. 짝을 못 찾은 행을 버릴지 남길지는 조인의 종류가 정합니다.

상세

학번만 적힌 출석부와 이름만 적힌 명렬표가 따로 있다고 해 봅시다. 두 장을 학번에 맞춰 나란히 놓고 한 줄씩 읽어 내면 학번과 이름이 한 줄에 옵니다.

조인은 두 릴레이션과 짝짓는 조건을 받아 하나의 릴레이션을 내놓는 연산입니다. 관계형 모델에서는 행과 열로 이뤄진 표를 릴레이션이라고 부릅니다. 결과의 열은 왼쪽 릴레이션의 열 전체 뒤에 오른쪽 릴레이션의 열 전체를 이어 붙인 것입니다. 결과의 행은 조건을 만족하는 행의 짝입니다.

조건은 양쪽에서 행을 하나씩 받아 참이나 거짓을 내는 식입니다. 이 식이 보는 열을 조인 키라고 부릅니다. 앞의 예에서는 학번이 조인 키입니다. 어느 열로 짝을 지을지는 조인이 정하지 않습니다. 쓰는 쪽이 정합니다.

flowchart TD
    L[왼쪽 릴레이션] --> C{조인 조건}
    R[오른쪽 릴레이션] --> C
    C -->|참| K[결과 행]
    C -->|짝 없음| N[조인의 종류가 정한다]

결과도 다시 릴레이션입니다. 그래서 조인의 결과를 또 다른 릴레이션과 조인할 수 있습니다. 표 셋 이상을 잇는 질의가 이 성질 위에 섭니다.

조인은 무엇을 한 짝으로 볼지만 정합니다. 그 짝을 어떤 차례로 찾아낼지는 정하지 않습니다. 같은 결과를 내는 실행 방법이 여럿 있고, 비용은 조인 자체가 아니라 그중 무엇을 골랐느냐가 정합니다.

배경

같은 사실을 여러 자리에 적어 두면 한 자리만 고쳤을 때 나머지가 어긋납니다. 그래서 데이터를 주제별로 나눠 각각 다른 표에 담습니다. 다른 표의 내용이 필요한 자리에는 내용 대신 그 표의 행을 가리키는 값 하나만 적습니다. 주문 표에 고객 이름 대신 고객 번호만 적어 두는 것이 그런 예입니다. 이 값이 외래 키이고, 이렇게 겹치는 것을 덜어 내며 나누는 일이 정규화입니다.

나눠 담으면 중복은 줄지만 하나였던 사실이 여러 표에 흩어집니다. 사람이 읽고 싶은 모양은 여전히 한 줄입니다. 주문 한 건을 보려는데 이름은 없이 고객 번호만 보입니다. 그래서 흩어진 것을 읽는 시점에 다시 모으는 자리가 필요해집니다.

그 자리에 놓인 연산이 조인입니다. 두 표를 이어 붙인다는 말 그대로 조인이라고 부릅니다. 저장할 때는 나누고 읽을 때는 모은다는 분업이 여기서 갈립니다.

갈래

짝을 못 찾은 행을 결과에 남기느냐가 축입니다. 아래 이름과 구문은 SQL(Structured Query Language, 구조화 질의 언어)의 것입니다.

내부 조인

PostgreSQL 공식 문서는 내부 조인을 이렇게 적습니다. 왼쪽 표의 각 행에 대해, 조인 조건을 만족하는 오른쪽 표의 행마다 결과 행이 하나씩 생깁니다. 짝을 못 찾은 행은 결과에 나오지 않습니다.

INNER 는 적지 않아도 됩니다. 아무것도 안 적으면 내부 조인입니다.

외부 조인

먼저 내부 조인을 수행합니다. 그다음 짝을 못 찾은 행을 되살려 붙입니다. 이때 반대편 표의 열 자리는 널 값으로 채웁니다.

질의에서 JOIN 앞에 적은 표가 왼쪽 표이고 JOIN 뒤에 적은 표가 오른쪽 표입니다. 어느 쪽 행을 되살리느냐로 셋으로 갈립니다. 왼쪽 외부 조인은 왼쪽 표의 행을, 오른쪽 외부 조인은 오른쪽 표의 행을, 완전 외부 조인은 양쪽 모두를 되살립니다.

그래서 왼쪽 외부 조인의 결과에는 왼쪽 표의 각 행마다 적어도 한 행이 언제나 있습니다. 오른쪽 외부 조인은 그 반대라서 오른쪽 표의 각 행마다 한 행이 있습니다. LEFT · RIGHT · FULL 중 하나를 적으면 외부 조인이 되고, OUTER 는 적지 않아도 됩니다.

교차 조인

조건이 없습니다. 양쪽의 가능한 모든 행 조합이 결과가 됩니다. 이것을 카티션 곱이라고 부릅니다. 왼쪽이 N행이고 오른쪽이 M행이면 결과는 N 곱하기 M 행입니다.

PostgreSQL 공식 문서는 T1 CROSS JOIN T2 가 T1 INNER JOIN T2 ON TRUE 와 같고, 쉼표로 나열한 FROM T1, T2 와도 같다고 적습니다.

조건을 줄여 적는 형태

여기까지가 짝 없는 행을 어떻게 하느냐로 갈린 것입니다. 축이 하나 더 있습니다. 조건을 어디까지 손으로 적느냐입니다.

양쪽이 같은 이름의 열로 짝을 지을 때는 USING 에 열 이름만 적어 조건을 줄일 수 있습니다. NATURAL 은 거기서 한 걸음 더 줄인 형태로, 양쪽 표에 함께 나오는 열 이름 전부로 USING 목록을 만듭니다. 공통된 열 이름이 하나도 없으면 NATURAL JOIN 은 교차 조인처럼 동작합니다. 위의 세 갈래를 대신하는 것은 아닙니다. NATURAL 도 내부 조인이나 외부 조인과 함께 씁니다.

예시

PostgreSQL

PostgreSQL 공식 문서는 표 둘을 놓고 조인 종류별 결과를 나란히 보입니다. 왼쪽 표 t1 입니다.

 num | name
-----+------
 1 | a
 2 | b
 3 | c

오른쪽 표 t2 입니다. num 이 1과 3에서만 겹치고, t1 의 2와 t2 의 5는 짝이 없습니다.

 num | value
-----+-------
 1 | xxx
 3 | yyy
 5 | zzz

내부 조인은 짝이 있는 둘만 남깁니다.

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
 1 | a | 1 | xxx
 3 | c | 3 | yyy
(2 rows)

왼쪽 외부 조인은 짝이 없는 2 | b 를 되살리고 오른쪽 열을 비워 둡니다.

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
 1 | a | 1 | xxx
 2 | b | |
 3 | c | 3 | yyy
(3 rows)

완전 외부 조인은 양쪽에서 짝 없는 행을 모두 되살립니다.

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
 1 | a | 1 | xxx
 2 | b | |
 3 | c | 3 | yyy
 | | 5 | zzz
(4 rows)

같은 두 표에 교차 조인을 걸면 3 곱하기 3 으로 9행이 나옵니다.

조인 조건에는 조인과 직접 관계없는 제약도 넣을 수 있습니다. ON 에 넣은 제약은 조인 앞에서 처리되고, WHERE 에 넣은 제약은 조인 뒤에서 처리됩니다. 내부 조인에서는 이 차이가 상관없지만, 외부 조인에서는 크게 상관있습니다. 외부 조인은 짝 없는 행도 남기고 반대편 표의 열은 널 값으로 채웁니다. 그 열에 거는 제약을 WHERE 에 적으면 널 값이 조건을 만족하지 못해 방금 되살린 행이 결과에서 다시 빠지고, 남는 결과는 내부 조인과 같아집니다. 같은 제약을 ON 에 적으면 조인이 일어나기 전에 걸러지므로 그 행은 그저 짝을 못 찾은 것으로 처리되어 외부 조인의 규칙대로 남습니다.

pandas

데이터프레임에도 같은 조인이 있습니다. pandas 공식 문서의 merge() 는 how 인자로 어느 키를 결과 표에 넣을지 고릅니다. 그리고 그 선택지를 SQL 조인 이름과 하나씩 짝지어 적어 둡니다.

키 조합이 왼쪽 표에도 오른쪽 표에도 나오지 않으면 결과 표의 값은 NA 가 된다고 적혀 있습니다. 관계형 데이터베이스에서 널 값으로 채우던 자리를 여기서는 NA 가 받습니다.

Apache Solr

검색엔진에도 있습니다. Solr 의 join 질의 파서는 문서 사이의 관계를 다루는 질의를 돌린다고 적습니다. 사용자가 준 하위 질의를 실행해 그 결과 문서들의 from 필드 값을 모으고, 그 값을 to 필드에 담은 문서를 돌려줍니다.

/solr/techproducts/select?q={!join from=manu_id_s to=id}title:ipod

제목에 ipod 이 든 제품을 만든 제조사 문서를 돌려주는 질의입니다. 공식 문서는 이 질의가 아래 SQL 과 의미가 같다고 적고, 이런 의미가 SQL 엔진의 서브쿼리와 상당히 비슷하다고 적습니다.

SELECT *
FROM techproducts
WHERE id IN (
 SELECT manu_id_s
 FROM techproducts
 WHERE title='ipod'
 )

검색엔진은 색인에 넣은 값을 텀이라는 낱개 단위로 쪼개 둡니다. 조인이 이 텀 단위로 이뤄지므로 from 과 to 는 서로 호환되는 필드 타입이어야 합니다. 문자열 필드와 정수 필드를 조인하면 동작하지 않습니다.

관련 항목

조인을 실행하는 알고리즘

중첩 루프 조인 · 병합 조인 · 해시 조인 · 순차 스캔 · 인덱스 스캔 · 정렬 · 질의 계획 · 질의 옵티마이저 · 조인 순서

조인이 짝을 짓는 데 쓰는 키

기본 키 · 외래 키 · 조인 키 · 인덱스

조인이 다루는 SQL·자료 모델 개념

관계형 모델 · 릴레이션 · 스키마 · 정규화 · 널 값 · 카티션 곱 · 뷰 · 서브쿼리

조인이 나타나는 도메인

관계형 데이터베이스 · SQL · 데이터프레임 · 검색엔진

조인을 구현한 제품

PostgreSQL · MySQL · pandas · Solr