13. [SQL기초] 테이블 결합 쿼리 - 테이블 조인과 유니온

지금까지 하나의 테이블을 사용해 결과를 얻는 쿼리문을 배웠지만, 앞으로 배울 쿼리 기능은 두 개 이상의 테이블을 하나로 합친 결과를 얻게 됩니다.

서브쿼리도 2개 이상의 테이블을 사용하기는 하지만, 쿼리 결과 데이터는 결국 한 개의 테이블에서 얻기 때문에 테이블 한 개를 사용하는 방식의 확장이라고 봐야 합니다.

테이블을 합치는 것은 두 개 이상의 테이블에서 필요한 행, 또는 열을 가져와서 하나의 테이블 결과로 만드는 것으로 중복 없는 데이터를 테이블에 나누어 저장하는 구조인 관계형 데이터베이스에서는 필수입니다.

쇼핑몰 주문정보를 가져올 때 주문정보 테이블에는 회원 아이디와 상품 코드로 주문 정보를 저장합니다. 회원 정보 테이블에 회원에 대한 상세 정보가 들어있기 때문에 주문 정보 테이블에는 중복으로 회원 정보를 또 저장할 필요가 없습니다.

주문정보 테이블만 쿼리 해서 결과를 가져오면 회원 이름과 상품 이름을 알 수 없습니다. 하나의 결과 테이블에 주문정보와 함께 회원 이름도 추가의 컬럼으로 표시되고, 상품 이름도 표시될 수 있으면 훨씬 더 보기 좋은 결과가 될 것입니다.

이런 것을 가능하게 해주는 것이 테이블 합치기 기능입니다.

가로와 세로 방향 결합

테이블을 조인하는 방향에 대한 이해는 아주 중요합니다. 꼭 이해를 하고 다음 내용으로 넘어가야 합니다.

두 개 이상의 테이블을 합치는 방식은 두 가지로 구분됩니다. 쉽게 생각해서 두 개의 테이블을 가로 방향으로 붙이는 방법과 세로 방향으로 붙이는 방법이 있습니다.

가로 방향으로 두 개의 테이블을 붙이면 결과 테이블에는 두 테이블의 컬럼들이 하나의 테이블로 합쳐져 표시됩니다. 열은 두 테이블에서 가져와 이어 붙이므로 제약이 없지만, 행 데이터는 두 테이블 중 어느 한 테이블을 기준으로 해서 맞춰야 합니다. 어떻게 두 테이블의 행을 맞추는지는 뒤에서 자세히 배웁니다.

세로 방향으로 두 테이블을 이어 붙여서 합치려면 두 테이블의 컬럼이 같아야 합니다. 두 테이블을 쿼리 해서 결과를 합칠 때 열 개수와 열 이름을 맞춰 같게 해야 세로로 테이블을 이어 붙일 수 있습니다. 세로 방향으로 맞추므로 행에는 별다른 제약이 없습니다.

테이블 합치기는 가로 방향, 또는 세로 방향 중 한 방향을 선택해 합치기를 합니다. 가로 방향으로 테이블을 합치는 방법을 조인(JOIN), 세로 방향으로 테이블을 합치는 방법을 유니온(UNION)이라고 합니다.

테이블을 합치기 위해 사용하는 연산 키워드도 이름 그대로 JOIN, UNION을 사용합니다.

테이블을 합칠 때는 어느 방향으로 합칠 것인지를 먼저 생각한 후에 쿼리문을 작성해야 합니다. 실전 쿼리 세상에서는 조인(JOIN)을 훨씬 더 많이 사용하고, 조인이 더 중요합니다.

가로 방향으로 테이블을 합치는 것은 열 방향 합치기라고 하기도 하고, 세로 방향으로 테이블을 합치는 것은 행 방향 합치기라고 하기도 합니다. 부르는 용어상의 사이이고 배우는 과정과 실무에서는 조인과 유니언으로 더 많이 사용합니다.

집합과 테이블의 이해

테이블 조인과 유니언을 하려면 수학시간에 배웠던 집합에 대해서 조금 더 알아 둘 필요가 있습니다. 합집합, 교집합 정도는 잘 알겠지만, 차집합, 곱집합은 집합의 어떤 부분이 되는지 헷갈리기 쉽습니다.

관계형 데이터베이스의 설계 기초 개념은 수학의 집합 개념을 기반으로 만들어졌습니다. 테이블 한 개를 한 개의 집합으로 생각하고, 수학적인 집합 연산 방식을 그대로 적용해서 테이블과 테이블이 집합 연산을 통해 새로운 하나의 테이블 결과가 만들어집니다.

테이블을 하나의 집합이라고 생각하고 집합의 A, B, C, D를 알아보겠습니다. 조인과 유니온에 필요한 5가지 집합에 대해서 그림으로 집합 연산의 결과가 어떤 구성요소를 만드는지 알아보겠습니다.

A와 B는 집합1의 구성요소가 되고 B와 C는 집합2의 구성요소가 됩니다. D는 집합1과 집합2에 속하지 않은 요소입니다.

합집합은 {A, B, C}가 됩니다. 집합1과 집합2를 합치면서 중복이 되는 구성요소 B는 1개로 줄여서 중복을 없앱니다. 집합1과 집합2에 모두 있는 중복 없는 구성요소가 됩니다.

교집합은 {B}가 됩니다. 집합1과 집합2에 모두 있는 구성요소가 됩니다. 마찬가지로 중복은 없습니다.

곱집합은 두 집합의 구성요소의 순서쌍 구성요소를 생성하는 교차 곱하기 집합입니다. 집합1 개수 X 집합2 개수만큼의 구성 요소 쌍이 생성됩니다. 곱집합은 {(A, B), (A, C), (B, B), (B, C)}의 집합을 생성합니다. 곱집합은 테이블 조인을 할 때 사용하는 집합의 개념으로 조인하는 테이블들의 행으로 곱집합 순서쌍을 만드는 방식입니다.

곱집합은 뒤에서 테이블 조인을 배울 때 확실하게 알게 됩니다.

차집합은 집합1에서 집합2를 뺀 나머지 집합이 됩니다. 순수하게 집합1에만 속한 구성요소를 구할 때 사용합니다. 집합1에서 집합2를 빼면 {A}가 됩니다.

여집합은 집합1에 속하지 않은 모든 구성요소가 됩니다. 집합1의 여집합은 {C, D}가 됩니다.

조금 더 이해하기 쉽게 숫자 구성요소를 가진 집합 다이어그램으로 표시해서 각 집합이 어떤 구성요소를 가지는지 알아보겠습니다.

전체집합 = {1, 2, 3, 4, 5, 6, 7, 8, 9}

집합 A = {1, 4, 6}

집합 B = {4, 5, 6, 7}

행 방향 합치기

행방향으로 테이블을 합치는 집합 연산은 합집합, 교집합, 차집합 3가지가 있습니다. 두 개 이상의 테이블을 결합할 때 컬럼 개수는 무조건 같아야 합니다. 각각의 쿼리문으로 선택하는 같은 위치 순서의 열은 데이터 형도 같아야 합니다. 또는, 캐스팅으로 데이터 형 변환을 해서 같은 데이터 형으로 변환할 수 있어야 합니다. 형 변환 캐스팅이 가능한 경우 자동으로 캐스팅됩니다.

합집합을 구하는 유니온(UNION)

두 테이블의 합집합을 구합니다. 두 테이블에서 선택하는 열은 같은 열 개수로 된 같은 데이터 형인 열을 가지고 있어야 합니다. 쉽게 말해 각각의 테이블에서 결과를 얻는 쿼리문 두 개를 단순히 합치는 것입니다.

같은 데이터 형이어야 하는 점을 주의해야 합니다. 같은 열 이름일 필요는 없지만, 데이터 형은 같아야 하고, 데이터 형이 달라도 캐스팅이 가능한 데이터 형이면 캐스팅을 해서 같은 데이터 형으로 자동 변경됩니다. 예를 들어 왼쪽과 오른쪽 쿼리 문의 같은 순서에 있는 열이 숫자형과 문자열형으로 서로 다를 경우 쿼리 결과 테이블의 열 데이터 형은 문자열형으로 자동 설정됩니다.

두 테이블의 같은 순서에 있는 열의 이름이 다르면 앞쪽 쿼리문의 열 이름을 따라갑니다.

기초 구문

쿼리문1 UNION 쿼리문2;

장바구니와 찜해놓기 테이블에 회원들이 넣어 놓은 상품 목록을 뽑아서 관심 상품 리스트를 만들고 싶으면 다음의 쿼리문으로 만들 수 있습니다.

select p_code, p_sell_price as price, p_option from basket union select p_code, p_sell_price, p_option from wishlist;

오른쪽 쿼리문의 옵션 열(p_option)과 상품 가격(p_sell_price) 열 순서를 바꾸면 왼쪽 테이블과 열이 나오는 순서가 달라집니다. 유니온 된 결과 테이블은 상품 가격(price)과 상품 옵션이 함께 표시됩니다. 데이터 형의 자동 캐스팅이 가능해서 자동 형 변환으로 테이블 열이 합쳐진 것입니다.

select p_code, p_sell_price as price, p_option from basket union select p_code, p_option, p_sell_price from wishlist;

왼쪽과 오른쪽 테이블의 선택 컬럼 개수가 같지 않으면 에러가 발생합니다. 컬럼 개수는 반드시 같게 맞춰야 합니다.

select userid, p_code, p_sell_price as price, p_option from basket union select userid, p_code, p_sell_price from wishlist;

유니온으로 합치는 왼쪽과 오른쪽의 쿼리문은 독립 실행할 수 있는 쿼리문입니다. 유니온 연산자는 두 쿼리문을 실행해 세로 방향으로 결과를 이어 붙여주는 기능을 합니다.

유니온(UNION) 결과 정렬

유니온으로 테이블 결합을 할 때 정렬은 최종적으로 합쳐진 결과 테이블에만 정렬을 할 수 있습니다. 각각의 테이블에 대해서는 정렬을 할 수 없습니다.

조금 이해하기 쉽게 괄호로 감싸서 구분을 하면 다음과 같이 됩니다.

(쿼리문1 UNION 쿼리문2) ORDER BY 열 이름;

다음처럼 유니온 쿼리문 오른쪽에 ORDER BY 절을 한 개 추가해 전체 합쳐진 결과를 정렬할 수 있습니다.

select p_code, p_sell_price as price, p_option from basket union select p_code, p_sell_price, p_option from wishlist order by p_code asc;

ORDER BY 절로 정렬하지 않은 유니온 결과 테이블의 데이터 행들은 쿼리문이 나오는 순서대로 표시됩니다. 정렬 없이 앞쪽에 표시하고 싶은 데이터를 가져오는 쿼리문은 유니온 쿼리문 왼쪽에 배치해야 합니다.

유니온 쿼리문을 정렬할 때는 최종 결과 테이블에서 사용하는 열 이름으로 정렬을 해야 합니다. 앞의 쿼리문에서 판매가를 표시하는 열 이름을 왼쪽 쿼리문에서는 별칭으로 “price”로 변경을 했습니다. 따라서 “price”로 정렬을 할 수는 있지만 오른쪽 쿼리문에 있는 “p_sell_price” 이름으로는 정렬을 할 수 없습니다. 다음 쿼리문은 에러를 발생시킵니다.

select p_code, p_sell_price as price, p_option from basket union select p_code, p_sell_price, p_option from wishlist order by p_sell_price asc;

중복 값 제거

유니온 쿼리 결과는 중복을 자동으로 제거한 결과행을 반환합니다. 단순히 서브쿼리의 결과를 이어 붙인 것이 아니라 전체 결과에서 중복되는 행은 제거를 한 결과를 반환합니다.

앞서 작성한 쿼리문을 다시 살펴보면 왼쪽 쿼리문에서 7개 오른쪽 쿼리문에서 6개를 합해서 13개의 행을 반환합니다. 단순이 두 결과를 붙인 결과를 반환하면 13개의 행을 가진 결과 테이블이 되어야 합니다.

 하지만 실제 반환된 결과는 8개의 행을 가지고 있고 중복되는 행은 모두 제거되어 있습니다. 중복된다는 것은 행의 모든 컬럼 값이 같은 것을 말합니다.

select p_code, p_sell_price as price, p_option from basket union select p_code, p_option, p_sell_price from wishlist;

조금 더 이해하기 쉽게 두 테이블에서 상품 코드만을 가져와 유니온으로 합친 결과를 얻어보겠습니다.

select p_code from basket union select p_code from wishlist order by p_code asc;

상품 코드 중복은 모두 제거되고, 유일한 상품 코드만 나열된 결과 테이블을 얻게 됩니다. 이 쿼리문은 UNION 키워드 뒤에 “DISTINCT”가 생략된 것입니다. “DISTINCT” 키워드는 테이블 유니온을 한 결과에서 중복을 제거하는 유니온 연산자에 따라오는 키워드입니다. 따라 다음의 쿼리문과 같습니다.

select p_code from basket union distinct select p_code from wishlist order by p_code asc;

중복 값 허용

기본 유니온 연산자로 중복이 없는 결과 테이블을 얻을 수 있지만, 경우에 따라서는 중복이 있는 테이블이 필요할 때도 있습니다. 이럴 때는 “DISTINCT” 대신 “ALL” 키워드로 중복을 허용한다는 표시를 해야 합니다. 중복 없는 상품 코드 목록을 가져오는 앞서의 테이블 유니온 쿼리문을 다음과 같이 바꿀 수 있습니다.

select p_code from basket union all select p_code from wishlist order by p_code asc;

결합된 최종 테이블은 왼쪽과 오른쪽 쿼리문의 쿼리 결과를 합쳐 13개의 행을 가지게 됩니다.

교집합 구하기

교집합 연산자는 INTERSECT를 사용합니다. 사용 방법은 UNION과 같습니다. 장바구니 테이블과 찜해놓기 테이블에 모두 들어있는 상품 목록을 얻으려면 다음과 같이 쿼리문을 작성합니다.

select p_code from basket intersect select p_code from wishlist order by p_code asc;

MYSQL에서는 INTERSECT 교집합 연산자를 지원하지 않습니다. MySQL에서 교집합을 구하려면 앞서 배운 IN과 EXISTS를 사용한 서브쿼리를 활용해야 합니다. IN과 EXISTS로도 교집합의 기능을 충분히 사용할 수 있습니다. INTERSECT를 사용한 앞의 쿼리문을 IN을 사용한 서브쿼리로 대체하면 다음과 같이 만들 수 있습니다.

select distinct p_code from basket where p_code in (select p_code from wishlist) order by p_code asc;

구현하는 방식의 차이가 있을 뿐 차집합을 구하는 결과는 동일합니다.

차집합 결과 구하기

두 테이블의 차집합을 구하는 연산자는 EXCEPT와 MINUS입니다. MINUS는 오라클의 차집합 연산자입니다. 찜해놓기 테이블에 있는 상품 코드를 제외하고 장바구니 테이블에만 들어있는 상품 코드 목록을 가져오는 쿼리문은 다음과 같이 작성합니다.

select p_code from basket except select p_code from wishlist order by p_code asc;

MySQL은 EXCEPT와 MINUS 연산자를 지원하지 않습니다. 차집합을 구하려면 IN 키워드와 서브쿼리를 이용해서 대체 구현을 해야 합니다. 차집합은 교집합과 반대의 개념이기 때문에 IN과 서브쿼리를 사용하면 간편하게 결과를 구할 수 있습니다.

select distinct p_code from basket where p_code not in (select p_code from wishlist) order by p_code asc;

테이블 조인(JOIN)

테이블을 열 방향으로 결합하는 3가지 방법인 교차 조인, 내부 조인, 외부 조인 3가지 방법을 배웁니다. 열 방향으로 테이블을 결합하는 방식을 조인(JOIN)이라고 하며, 단어 그대로 “JOIN”을 쿼리문 키워드로 사용합니다.

열 방향으로 결합을 하는 “테이블 조인”도 어떤 방식으로 결합하는지에 따라 3가지로 나누어집니다. 외부 조인을 가장 많이 사용하고, 가장 중요하므로 외부 조인은 확실히 개념을 익혀 둘 필요가 있습니다.

열 방향 테이블 합치기

테이블 조인은 열 방향으로 테이블을 합치는 집합 연산입니다. 또는 가로 방향 테이블 결합이라고도 합니다. 하나의 결과 테이블에 다른 결과 테이블을 오른쪽에 붙여서 열 수를 증가시키기 때문에 열 방향, 또는 가로 방향이라고 합니다.

쉽게 프로스포츠 시즌 리그 대진표와 같다고 생각하면 됩니다. 한 팀이 다른 팀들과 하게 될 전체 시즌 경기를 표로 나타낸 것이 곱집합입니다.

실무에서는 한글화 된 표현인 “테이블 결합”보다는 “테이블 조인”으로 주로 사용합니다.

앞서 집합의 개념을 배우면서 곱집합의 기본 개념을 익혔지만, 결합하는 양쪽 테이블의 행 수를 곱한 숫자만큼의 행이 만들어지는 쿼리 방법입니다. 두 개의 테이블을 조인하면 X축에는 첫 번째 테이블의 행들이 Y축에는 두 번째 테이블의 행들이 배치되고 X축과 Y축이 만나는 셀에는 X축과 Y축 행을 쌍으로 하는 요소가 만들어지게 됩니다.

테이블 조인에서 하나의 테이블은 하나의 집합이고, 행 한 개가 집합의 요소 한 개가 됩니다.

그림으로는 이해가 쉽도록 가로 세로 방향으로 나누어져 있지만 실제로는 세로 방향으로 길게 늘어선 요소 쌍이 생겨나는 것입니다.

테이블을 조인하는 방법은 여러 가지가 있으며, 교차 조인, 내부 조인, 외부 조인으로 구분합니다.

내부 조인에는 같은 테이블을 조인하는 셀프 조인이 추가로 있으며, 외부 조인은 기준이 되는 테이블이 왼쪽 테이블인지 오른쪽 테이블인지에 따라 왼쪽 조인과 오른쪽 조인으로 구분합니다.

교차 조인

두 테이블을 곱집합 한 결과를 얻습니다. 테이블 행이 많은 경우 테이블 행을 곱한 숫자만큼의 행이 생깁니다.

기본 구문

Select * from 테이블1, 테이블2;

교차 조인은 SELECT 기본 쿼리문과 같습니다. 조인할 추가 테이블 이름을 쉼표로 구분해 FROM절에 추가로 붙이기만 하면 됩니다. WHERE절, GROUP BY절, ORDER BY절 사용방법도 기본 SELECT문과 같습니다.

제조사(maker)와 원산지(madein) 테이블을 교차 조인해서 제조사별 원산지 가능 조합을 만들어 보겠습니다.

Select * from maker, madein;

교차 조인한 테이블의 결과는 테이블을 표시한 순서대로 곱집합이 이루어집니다. 제조사 행 한 개에 대해 원산지 행들이 모두 매칭 되어 표시된 후 제조사의 두 번째 행이 매칭 됩니다.

일반 SELECT문과 거의 같지만 테이블에 별칭을 부여해서 테이블 단위로 필요한 열을 가져올 수 있고, SELECT문의 사용 방법과 동일하게 WHERE 절로 필요한 행만 남길 수도 있고 정렬도 할 수 있습니다.

Select ta.name, tb.* from maker ta, madein tb where ta.name = '카카오' order by tb.country asc;

“ta”와 “tb”는 교차 조인을 하는 테이블의 별칭입니다. 테이블 별칭은 SELECT문이 모든 절에서 사용할 수 있습니다. 조인하는 테이블 단위로 테이블 열 전체를 가져오려면 “별칭.*”로 표시하면 됩니다.

테이블 조인은 테이블 두 개만이 아니라 여러 개의 테이블을 조인할 수도 있습니다. 예제에 사용한 테이블들은 행수가 작기 때문에 결과 테이블도 행 수가 적습니다. 테이블 3개를 교차 조인하는데 행 수가 10개씩 있다면 최종 결과 테이블은 1,000개의 행을 가진 테이블이 됩니다.

따라서 교차 조인은 매칭 되는 전체 경우의 수를 만드는 제한적인 경우에만 사용할 수 있습니다.

내부 조인

내부 조인은 테이블 조인 중에서 가장 많이 사용하는 조인입니다. 중복 없는 데이터를 테이블에 나누어 저장하고 테이블 사이의 관계를 정의해 데이터를 가져오는 관계형 데이터베이스의 설계 개념을 가장 충실하게 보여주는 쿼리 기법이기도 합니다.

각 상품별로 재고가 몇 개 있는지 알고 싶으면 상품정보(product) 테이블과 상품 재고(inventory) 테이블을 조인해서 하나의 테이블로 된 결과를 만들어야 합니다. 상품코드만으로는 어떤 상품인지 알 수 없기 때문에 상품 정보 테이블에서 상품명을 가져와야 하고, 재고 테이블에서는 재고 개수를 가져와야 합니다.

재고 테이블에 재고 개수를 기록하는 기준은 상품 코드입니다. 어떤 상품코드에 재고가 몇 개 있는지를 기록합니다. 그리고 상품 코드는 상품정보 테이블의 기본키를 가져와서 사용합니다. 상품정보에 없는 상품 코드는 사용할 수 없어야 하기 때문에 재고 테이블의 상품 코드는 외래 키로 정해서 재고 테이블에 사용합니다.

따라서 상품 정보 테이블과 재고 테이블은 상품 코드로 서로 연관 관계를 가지고 연결되어 있습니다. 상품 코드를 사용하면 두 테이블을 조인할 때 상품 정보와 재고를 1:N로 연결할 수 있습니다.

Select p_code, name, stock from product, inventory where product.id = inventory.p_code;

WHERE 절을 제외하면 교차 조인과 완전히 같습니다. 내부 조인은 이렇게 교차 조인에 WHERE절을 추가해 행을 매칭하는 조건식을 추가해줌으로써 비교식이 참이 되는 행만 남기고 나머지 행들은 제거하는 조인 방식입니다. 이렇게 WHERE절로 매칭을 하면 두 테이블의 교집합 요소들만 남게 됩니다.

앞의 내부조인 쿼리문은 열 중복을 피할 수 있도록 상품 코드를 재고 테이블의 p_code 열 이름으로 표시했습니다. 상품정보 테이블의 상품 코드는 “id”열이고 “id” 열 이름으로 상품 코드를 가져오면 재고 테이블의 “ID”와 중복이 발생해 에러가 표시됩니다. “id”를 사용해 상품 코드를 가져오려면 다음과 같이 쿼리문을 수정해야 합니다. 그리고 WHERE절의 긴 테이블 이름 표시도 테이블 별칭을 사용해 짧게 줄여서 표시를 하겠습니다.

Select a.id, name, stock from product a, inventory b where a.id = b.p_code;

조금 더 간결하고 보기 좋은 쿼리문이 되었습니다. 내부 조인을 하는 테이블의 행들이 1:1로 매칭이 되려면 비교식으로 비교하는 컬럼이 기본키와 외래 키로 연결되어 있고 외래키 컬럼의 값에 중복이 없어야 합니다.

1:1 매칭 조건을 만족하면 기본키를 가진 테이블의 행 수를 가진 결과 테이블이 반환됩니다. 기본키에 매칭 되는 외래 키를 가진 행이 없으면 해당 행은 결과에서 제외됩니다. 비교한 열의 값이 양쪽 테이블에 모두 있어야 합니다.

상품정보 테이블과 재고 테이블을 내부 조인한 앞의 쿼리문에서 상품코드 “4”의 재고 정보는 표시되지 않고 쿼리 결과에서 배제됩니다.

외래 키가 열 안에서 유일하지 않으면 1:N으로 매칭이 됩니다. 앞의 쿼리문에서 상품 코드 “3”의 재고 정보는 2개가 표시됩니다. 재고 테이블에 같은 상품코드에 대한 재고 정보가 2개 등록되어 있기 때문입니다.

다중 테이블 내부 조인

내부 조인은 2개 이상의 테이블을 조인할 수 있습니다. 조인하는 테이블 개수가 2개일 수도 3개, 또는 4개 이상일 수도 있습니다. WHERE 절로 비교할 수 있는 같은 데이터 형인 열이 있으면 여러 개의 테이블도 내부 조인으로 연결해 원하는 결과를 가져올 수 있습니다.

코드로 저장된 상품정보 테이블의 항목 정보들을 다른 테이블에서 가져와 하나의 테이블에 표시해보겠습니다. 4개의 테이블을 내부 조인으로 결합해 상품명, 원산지명, 제조사명, 재고 수량 정보를 하나의 테이블에 묶어서 표시합니다.

Select a.id, a.name, b.country, c.name, stock from product a, madein b, maker c, inventory d where a.madein = b.id and a.maker = c.id and a.id = d.p_code;

이제 테이블의 내용이 코드로 된 상품 정보가 아니라 알아볼 수 있는 정보를 담고 있게 됩니다.

표준 INNER JOIN 키워드로 내부조인

앞서 만들었던 내부 조인은 교차 조인에 WHERE절을 추가해서 구현하는 방법입니다. 간단하지만, 약간 구형인 방법이며, 내부 조인을 위한 표준 키워드인 “INNER JOIN”을 사용하는 내부조인 방법이 주로 사용됩니다. 내부조인을 한다고 하면 앞으로는 “INNER JOIN” 키워드를 사용한 내부조인을 한다고 생각해야 합니다.

기본 구문

SELECT * FROM 테이블 이름1 INNER JOIN 테이블 이름2 ON 조건식;

구문을 보면 “INNER JOIN” 키워드를 사이에 두고 테이블 이름1과 테이블 이름2를 표시합니다. 그리고 교차 조인으로 구현한 내부 조인에서 WHERE 절로 열 매칭을 한 조건식을 ON절 뒤에 표시합니다.

앞서 설명한 대로 ON절 뒤의 조건식으로 컬럼 값이 같은 양쪽 테이블의 행들만 남기기 때문에 교집합이 됩니다.

“INNER JOIN” 키워드와 ON 절을 사용해 형태가 달라졌을 뿐 교차 조인으로 만들었던 내부 조인과 같음을 알 수 있습니다.

앞서 상품명과 상품별 재고 개수를 가져왔던 내부조인 쿼리문을 “INNER JOIN” 키워드로 구현하면 다음과 같습니다.

Select a.id, name, stock from product a inner join inventory b on a.id = b.p_code;

테이블 별칭은 “a”, “b”로 최대한 단순하게 정해서 쿼리문을 축약했습니다.

외부 조인

내부 조인은 조인하는 양쪽 테이블 중 어느 한쪽의 행이 누락되었으면 값 일치가 없는 나머지 행도 조인 결과에서 제외됩니다. 만약 조인하는 양쪽 테이블 중 어느 한쪽의 테이블 행은 일치하는 다른 테이블 행이 없어도 결과에서 누락되지 않고 유지를 하고 싶으면 어떻게 해야 할까? 이럴 때 외부 조인을 사용합니다.

외부 조인은 기준으로 정한 테이블 행과 비교 일치하는 다른 테이블의 행이 없어도 조인 결과에서 제외하지 않고 행을 유지합니다.

외부 조인은 기준으로 삼는 테이블이 왼쪽에 있는 테이블인지 오른쪽에 있는 테이블인지 선택할 수 있도록 “LEFT JOIN”, “RIGHT JOIN” 두가지 키워드를 제공합니다. 조인하는 양쪽 테이블 중에서 왼쪽 테이블을 기준 테이블로 삼고 싶으면 “LEFT JOIN”을, 반대로 오른쪽 테이블을 기준 테이블로 삼고 싶으면 “RIGHT JOIN” 키워드를 사용해서 외부조인을 하면 됩니다.

외부조인 키워드는 “LEFT OUTER JOIN”, “RIGHT OUTER JOIN” 과 같이 “OUTER”를 포함해 전체 이름으로 표시할 수도 있습니다. 잘 사용하지 않는 추세이므로 줄임 표현을 사용하면 됩니다.

내부 조인과 외부 조인 차이

앞서 상품정보 테이블과 상품 재고 테이블을 내부 조인을 하면서 재고 정보가 없는 상품은 조인한 결과 테이블에서 제외가 된다는 것을 배웠습니다. 내부조인은 비교식과 일치하지 하는 양쪽 행이 없으면 결과에서 제외하는 특징이 있습니다.

그런데, 상품정보 테이블의 행은 모두 표시를 하고 재고 정보가 없으면 그냥 비워 둔 채로 조인 결과 테이블을 만들고 싶을 수 있습니다. 실무에서는 내부조인 결과보다는 이쪽이 훨씬 현실적이고 원하는 결과 테이블일 것입니다.

재고정보가 없다고 상품정보까지 누락되는 것은 아무도 원하지 않는 상황일 것입니다.

외부 조인은 이렇게 모든 행을 남기고 싶은 테이블을 기준 테이블로 정하고, 조인하는 다른 테이블들은 이 기준 테이블의 비교하는 컬럼 값에 맞춰 일치하는 행을 옆에 붙이는 교차 조인 방식입니다.

앞서 내부 조인에서 상품정보와 재고, 원산지, 제조사 테이블들을 조인해서 가져오는 결과 테이블에는 재고 정보가 없는 상품 정보가 누락됩니다.

앞서의 상품정보를 조합해 가져오는 내부조인 쿼리문을 외부 조인으로 변경하면 다음과 같이 됩니다.

Select a.id, a.name, b.country, c.name, stock from product a left join madein b on a.madein = b.id left join maker c on a.maker = c.id left join inventory d on a.id = d.p_code;

4개나 되는 테이블을 외부 조인을 하지만 테이블 2개를 외부 조인하는 것과 다를 것은 없습니다. 테이블 이름 뒤에 LEFT JOIN 키워드를 추가하고 조인할 다른 테이블 이름 하나를 넣은 후 ON절에 기준 테이블과의 비교식을 넣으면 됩니다.

앞서의 쿼리문은 조인하는 다른 테이블들이 모두 기준 테이블의 컬럼과 비교를 했지만, 조인하는 비교식의 비교 대상 열이 반드시 기준 테이블의 열일 필요는 없습니다.

조인하는 다른 테이블들의 비교식에서 비교하는 대상 열은 기준 테이블, 또는 앞쪽에 조인한 다른 테이블의 열이면 됩니다. 현재 조인하는 자신보다 뒤에 나오는 조인 테이블의 컬럼과 비교를 할 수는 없습니다.

외부 조인과 결합 방향

외부 조인은 기준 테이블을 어느 테이블로 할 건인지를 선택할 수 있습니다. 앞서 이미 “LEFT JOIN” 키워드를 사용하면서 배웠습니다. “LEFT JOIN” 키워드는 외부 조인의 기준이 되는 테이블이 왼쪽에 있는 테이블이라는 뜻입니다. 왼쪽에 있는 어떤 테이블인지는 ON절의 비교식에서 정합니다.

테이블 두 개만 외부 조인을 하면 왼쪽에 있는 테이블은 당연히 정해져 있지만, 3개 이상의 테이블을 외부조인을 하면 뒤쪽에 오는 테이블들은 왼쪽에 있는 기준 테이블이 어떤 건지 정해야 합니다. ON절의 비교식은 대상이 되는 기준 테이블이 왼쪽의 어느 테이블인지 정해서 비교를 하는 기능을 하게 됩니다.

테이블이 배치된 순서를 변경하지 않고 오른쪽 테이블을 기준 테이블로 사용할 수 있도록 “RIGHT JOIN” 키워드도 제공됩니다.

다만 많은 경우에 “LEFT JOIN”을 사용해 외부 조인을 할 때는 혼동을 줄이기 위해 외부조인 쿼리문을 작성할 때는 한가지 방향만 사용하는 것이 좋습니다.

상품 정보 테이블을 기준으로 재고 정보를 가져오는 외부조인을 “RIGHT JOIN”으로 작성하면 다음과 같습니다.

Select b.id, stock from inventory a right join product b on a.p_code = b.id;

전체 외부조인(Full Outer Join)

FULL OUTER JOIN 키워드로 전체 외부 조인을 합니다. (합집합 – 교집합)을 구하는 조인으로, 두 테이블에서 비교식으로 비교하는 컬럼의 값이 어느 한쪽에만 있는 행을 가져와 합칩니다.

장바구니 테이블과 찜해놓기 테이블 어느 한쪽에만 들어있는 상품코드를 가져오는 쿼리문을 FULL OUTER JOIN으로 작성하면 다음과 같습니다.

select a.p_code from basket a full outer join wishlist b on a.p_code = b.p_code WHERE A.p_code IS NULL OR B.p_code IS NULL;

MySQL은 전체 외부조인을 지원하지 않기 때문에 외부조인 쿼리문 2개를 유니온 하는 방식으로 우회 구현을 해야 합니다.

장바구니 테이블과 찜해놓기 테이블을 유니온 하되, 양쪽에 모두 있는 상품코드를 가진 행은 제외하는 조인 방식입니다.

MySQL에서는 다음과 같이 우회 구현을 합니다.

(select a.p_code from basket a left join wishlist b on a.p_code = b.p_code where b.p_code is null) union all (select a.p_code from basket a right join wishlist b on a.p_code = b.p_code where a.p_code is null);

쿼리 구조가 복잡해 보이지만 (장바구니 – 찜해놓기) 합집합 (찜해놓기 - 장바구니)와 같은 개념의 쿼리문입니다.

자기 조인(Self Join)

자기 조인은 내부 조인의 특별한 형태입니다. 테이블 두 개를 조인하는 내부 조인과 같은 구조지만 두 개의 테이블이 같은 테이블인 특수한 형태입니다. 테이블 조인을 하다 보면 자기 자신을 조인해서 데이터를 가져와야 하는 경우가 있는데 그럴 때 사용할 수 있습니다.

자기 조인을 할 때는 테이블 별칭 기능을 사용해 반드시 다른 별칭을 부여해야 같은 테이블로 내부 조인을 할 수 있습니다.

상품정보(product) 테이블을 자기 조인을 해서 연관 상품명을 가져오는 쿼리문을 작성해보겠습니다. 상품정보 테이블의 LINK 컬럼은 연관이 있는 다른 상품 코드를 저장하는 열입니다.

Select a.id, a.name as '상품명', b.name as '관련 상품명' from product a inner join product b on a.id = b.link;

상품별로 연관 상품명이 무언인지 자기 조인을 해서 확인할 수 있습니다. 대부분의 경우는 이렇게 자기 조인을 할 일이 없으며 예외적인 경우에만 사용합니다.