회원 테이블과 구매 테이블, 똑같은 두 테이블을 조인해도 결과는 12건이 되기도 하고 18건이나 120건이 되기도 합니다. 결과 행 수가 왜 달라지는지 이해하면 내부 조인, 외부 조인, 상호 조인의 차이가 한 번에 정리됩니다. 이 글에서는 회원 10명과 구매 기록 12건이 담긴 쇼핑몰 데이터를 예로, 조인 방식에 따라 어떤 행이 남고 어떤 행이 빠지는지 살펴보려고 합니다. 회원 10명 중 실제로 물건을 산 회원은 4명이고, 나머지 6명은 가입만 하고 한 번도 구매하지 않았습니다.

조인의 전제 조건: 일대다 관계와 PK-FK

조인(join)은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말합니다. 회원 테이블에는 회원의 이름과 연락처가 있고, 구매 테이블에는 회원이 구매한 물건이 있습니다. 물건을 배송하려면 두 테이블의 정보가 함께 필요하므로, 두 테이블을 엮어 하나의 배송 정보를 추출하는 것이 대표적인 조인입니다.

두 테이블을 조인하려면 테이블이 일대다(one to many) 관계로 연결되어야 합니다. 일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러 개의 값이 존재할 수 있는 관계를 말합니다. 회원 테이블에서 한 회원의 아이디는 한 번만 등장하므로 기본 키(Primary Key, PK)로 지정합니다. 반면 같은 회원이 여러 번 구매하면 구매 테이블에는 그 아이디가 여러 번 나타납니다. 회원은 1명이지만 구매는 여러 번 할 수 있기 때문에, 구매 테이블의 아이디는 외래 키(Foreign Key, FK)로 설정합니다. 이처럼 일대다 관계는 주로 기본 키와 외래 키로 맺어지므로 'PK-FK 관계'라고도 부릅니다.

내부 조인과 외부 조인: 결과 행 수가 달라지는 이유

내부 조인은 두 테이블에 모두 있는 행만 남깁니다

일반적으로 조인이라고 부르는 것은 내부 조인(inner join)이며, 조인 중에서 가장 많이 사용됩니다. INNER JOIN을 그냥 JOIN이라고만 써도 내부 조인으로 인식합니다. 구매 테이블의 아이디를 회원 테이블에서 찾고, 같은 아이디를 찾으면 두 테이블의 행을 결합하는 방식으로 동작합니다.

예제 쿼리에서 회원 테이블은 member, 구매 테이블은 buy입니다. 회원 테이블에는 아이디(mem_id), 이름(mem_name), 주소(addr)가 있고, 구매 테이블에는 구매한 회원의 아이디(mem_id)와 제품명(prod_name)이 있습니다. 쿼리 안의 M과 B는 각 테이블을 짧게 부르는 별칭입니다.

전체 회원의 아이디, 이름, 구매한 제품, 주소를 출력하려고 다음과 같이 내부 조인을 실행하면 결과는 12건입니다.

구매 테이블의 12건이 모두 결합되었으니 결과에는 이상이 없습니다. 하지만 이 결과는 '전체 회원'이 아니라 '구매한 기록이 있는 회원들'의 목록입니다. 한 번도 구매하지 않은 회원의 정보는 나오지 않습니다. 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이기 때문입니다.

이 특징이 오히려 유용한 경우도 있습니다. 한 번이라도 구매한 기록이 있는 회원에게만 감사 안내문을 보내려면, 내부 조인 결과에 DISTINCT를 붙여 중복된 이름을 하나로 줄이면 됩니다. 이렇게 하면 구매 기록이 있는 회원 4명이 추출됩니다.

참고로 두 테이블에 같은 이름의 열이 있을 때 SELECT 절에 mem_id만 쓰면 Error Code: 1052. Column 'mem_id' in field list is ambiguous 오류가 발생합니다. 회원 테이블과 구매 테이블 중 어느 쪽의 mem_id인지 불확실하다는 의미입니다. 이때는 테이블_이름.열_이름 형식으로 쓰고, 코드가 길어지면 FROM 절의 테이블 이름 뒤에 별칭(alias)을 붙여 간결하게 표현합니다. 앞의 쿼리에서 쓴 M과 B가 바로 이 별칭입니다.

외부 조인은 한쪽에만 있는 행도 NULL과 함께 남깁니다

구매 기록이 없는 회원까지 포함한 '전체 회원'의 목록을 얻으려면 외부 조인(outer join)을 사용해야 합니다. 외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출합니다.

LEFT OUTER JOIN은 '왼쪽 테이블(member)의 내용은 모두 출력되어야 한다'로 해석하면 기억하기 쉽습니다. 줄여서 LEFT JOIN이라고만 써도 됩니다. 실행하면 구매 기록 12건에 더해, 한 번도 구매하지 않은 회원 6명이 구매 제품(prod_name)이 NULL인 상태로 함께 출력되어 결과는 18건이 됩니다. 같은 결과를 RIGHT OUTER JOIN으로 얻으려면 왼쪽과 오른쪽 테이블의 위치만 바꾸면 됩니다.

외부 조인은 한 번도 구매하지 않은 회원을 찾을 때도 활용됩니다. 구매하지 않은 회원은 조인 결과의 물건 이름이 비어 있으므로, NULL 값인지 비교하는 IS NULL 구문으로 걸러 낼 수 있습니다.

실행 결과로 한 번도 구매하지 않은 회원 6명이 나옵니다. 내부 조인으로 찾은 구매 회원 4명과 외부 조인으로 찾은 미구매 회원 6명을 합치면 전체 회원 10명이 됩니다. 한편 FULL OUTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것으로, 왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력합니다.

상호 조인: 모든 행을 곱하는 조인

상호 조인(cross join)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다. 회원 테이블의 첫 행이 구매 테이블의 12개 행과 결합되고, 두 번째 행도 12개 행과 결합되는 식으로 이어집니다. 그래서 결과의 전체 행 개수는 두 테이블 행 개수를 곱한 값이 되며, 회원 10행과 구매 12행을 상호 조인하면 120건이 생성됩니다. 상호 조인을 카티션 곱(cartesian product)이라고도 부릅니다.

상호 조인은 ON 구문을 사용할 수 없습니다. 또 랜덤으로 조인하기 때문에 결과의 내용에는 의미가 없습니다. 예를 들어 한 회원의 구매 기록이 그 물건을 사지 않은 다른 회원들과도 모두 결합됩니다. 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성하는 것입니다. 4,581건인 테이블과 4,079건인 테이블을 상호 조인하면 18,685,899건의 데이터를 만들 수 있고, 이를 실제 테이블로 만들고 싶다면 CREATE TABLE ~ SELECT 문을 함께 사용합니다.

정리하면 조인 결과의 행 수는 조인 방식이 어떤 행을 남기는지에 따라 결정됩니다. 내부 조인은 두 테이블에 모두 있는 행만, 외부 조인은 기준 테이블의 모든 행을, 상호 조인은 가능한 모든 조합을 남깁니다.

조인 쿼리를 실행하기 전에 '몇 행이 나올까?'를 먼저 예상해 보면, 결과가 의도와 다를 때 원인을 훨씬 빨리 찾을 수 있습니다. 『혼자 공부하는 SQL with AI』에서는 내부·외부·상호 조인과 함께 자체 조인까지 하나의 예제 데이터베이스로 차근차근 다루며, 데이터베이스 개념과 MySQL 설치부터 인덱스, 스토어드 프로시저, 파이썬 연동까지 이어집니다.

위 컨텐츠는 우재남 저자의
『혼자 공부하는 SQL with AI』를 재구성하여 작성되었습니다.