[데이터 모델과 SQL] 8. JOIN 연산 - OUTER JOIN

 

OUTER JOIN은 기준 테이블의 행과 참조 테이블의 행을 연결하되 참조 테이블에 매칭되는 행이 없더라도 기준 테이블의 행을 버리지 않는다. 즉, '기준 테이블 행 - 참조 테이블 데이터 없음(null)'과 같은 매칭이 가능하다. OUTER JOIN 구문은 LEFT/RIGHT 키워드와 함께 쓰이는데 기준이 되는 테이블을 OUTER JOIN 연산의 어느 쪽(왼쪽 또는 오른쪽)에 둘 것인지를 지정한다.

 

예) 모든 음식 종류와 관련된 조리법 이름들을 알고 싶다.

SELECT recipe_classes.RecipeClassDescription, recipes.RecipeTitle 
FROM recipe_classes LEFT OUTER JOIN recipes ON recipe_classes.RecipeClassID=recipes.RecipeClassID;

 

셋 이상의 테이블을 결합할 때는 OUTER JOIN 연산을 반복하면 된다.
예) (A LEFT OUTER JOIN B ON A.aID=B.aID) LEFT OUTER JOIN C ON B.bID=C.bID

 

 

● OUTER JOIN과 INNER JOIN

기준 테이블과 참조 테이블 사이의 관계를 아래와 같이 정리해보자(기준 테이블:참조 테이블).

ⓐ 1:다(one to many)
ⓑ 1:1(one to one)
ⓒ 다:1(many to one)

 

이 중 ⓑ, ⓒ의 경우는 기준 테이블에 데이터가 존재하면 참조 테이블에도 반드시 데이터가 존재한다. '다:1' 관계의 예로 '주문들(기준 테이블):고객(참조 테이블)'을 살펴볼 수 있다. 주문(기준)행들에는 반드시 매칭되는 고객(참조)행이 있음을 알 수 있다. 따라서 OUTER JOIN과 INNER JOIN의 결과가 같다.

 

하지만 ⓐ와 같이 '1:다' 관계의 경우 '고객-주문'을 예로 생각해보면 참조 테이블에 데이터가 없을 수도 있다(즉, 고객 정보는 있지만 고객은 한 번도 주문을 하지 않은 경우). 따라서 '1:다' 관계에 있는 테이블들에서 OUTER JOIN의 결과가 INNER JOIN의 결과와 다르며 이들을 JOIN 할 때는 특별한 경우가 아니면 OUTER JOIN을 사용해야 한다. 

 

*참고로 '1:다' 관계를 parent-children 관계라고 부르기도 한다.

 

 

● null matching 문제

아래와 같은 두 테이블이 있다고 하자.

테이블 1: 키 - 이름 - 식별코드 순
A1 - Sam - C1
A2 - Mike - C2
A3 - Dosen - Null

테이블 2: 키 - 식별코드 - 고향(state) 순
B1 - C1 - VA
B2 - C2 - WA
B3 - Null - TX

 

위 두 테이블을 테이블1.식별코드=테이블2.식별코드 조건으로 연결할 수 있겠는가? 매칭 조건의 값에 Null이 포함되어 있는 경우 연결해서는 안된다. 값이 사라지거나 잘못된 매칭이 일어날 수 있다. 

일반적으로 Null 값이 포함된 컬럼은 가능하면 매칭 키로 사용하지 않고 부득이 사용할 때는 JOIN 결과에 오류가 포함되어 있을 수 있음을 기억하고 있어야 한다.

 

 

● FULL OUTER JOIN

FULL OUTER JOIN은 JOIN  대상이 되는 테이블들 사이에 매칭이 일어나지 않더라도 모든 데이터를 출력한다. FULL OUTER JOIN은 UNION 연산과 동일하다. 예를 들어 위의 null matching 예를 FULL OUTER JOIN을 사용하여 다시 구해보면 아래와 같은 결과 셋을 얻을 수 있다.

예) SELECT 테이블 2 키, 이름, 테이블 1키 FROM 테이블 1 FULL OUTER JOIN 테이블 2 ON 테이블 1.식별코드=테이

블 2.식별코드;

테이블 2키 - 이름 - 테이블 1키 순
B1 - Sam - A1
B2 - Mike - A2
B3 - Null - Null
Null - Dosen - A3

 

일반적으로 FULL OUTER JOIN을 사용해야 하는 경우는 거의 보지 못했다. 그리고 DBMS에 따라 FULL OUTER JOIN을 지원하지 않는 경우도 있다. FULL OUTER JOIN을 사용해야 하는 경우라면 데이터 모델이 잘못 정의되었을 수 있는지 의심해봐야 한다. 위의 예에서 테이블 1을 정의할 때 식별코드는 Null이 되어서는 안되는 조건(NOT NULL)이 붙어있었어야 했다.

 

 

● OUTER JOIN 연산의 활용 예
테이블 사이에 서로 매칭되지 않는 행이 무엇인지 알아볼 때 OUTER JOIN 연산이 유용하게 쓰인다. 예를 들면 기준 테이블에는 아이템(행)이 존재하지만 참조 테이블에는 대응되는 행이 존재하지 않는 경우까지 포함한 리포트를 수집할 수 있다.

예) 한번도 주문된 적이 없는 상품들

SELECT products.ProductNumber, order_details.OrderNumber FROM 
(products LEFT OUTER JOIN order_details ON products.ProductNumber=order_details.ProductNumber) 
WHERE order_details.OrderNumber IS NULL;

 

위의 OUTER JOIN을 이용한 검색식은 product 테이블에는 데이터가 있지만(즉, 상품은 있지만) 주문(order_details) 테이블에는 매칭되는 데이터가 없는, 즉 주문된 적이 없는 상품을 보여준다.

 

OUTER JOIN은 테이블 사이의 관계(다:1, 1:1, 1:다)에 따라 결과가 다르다. 따라서 A OUTER JOIN B와 B OUTER JOIN A 결과가 다를 수 있다. 따라서 JOIN 결과 데이터가 사라질 수 있는 테이블을 기준 테이블로 두고 다른 테이블을 참조 테이블로하여 JOIN 연산을 수행해야 한다.

 

특히 1:다 관계에 있는 테이블들을 JOIN할 때는 반드시 OUTER JOIN을 사용해야 한다.

 

■