[MSSQL] OUTER JOIN

 

1. 외부 조인(Outer Join)의 기본개념

 

외부 조인은 ANSI SQL-92에서 소개되었으며, 

 

내부 조인이나 크로스 조인과는 달리 단 하나의 표준 문법만을 가지고 있다. 

 

즉 테이블들 간에 JOIN 키워드를 사용하는 방식으로, 

 

조인 조건이 ON절에 지정되는 방식이다.

 

 

 

외부 조인은 내부 조인에서 처리되는 

 

두 개의 논리적 프로세싱 단계(카티전 곱과 ON 필터) 

 

외에도 외부 조인에서만 존재하는 세 번째 단계인 

 

외부 행을 추가하는 단계를 수행하게 된다.

 

 

외부 조인에서는 테이블 이름 사이에 

 

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 키워드를 사용해서 

 

테이블들 중 하나를 "보존되는" 테이블로 지정해야 한다. 

 

OUTER 키워드는 선택적으로 사용 가능하다.

 

 

 

LEFT 키워드는 왼쪽 테이블의 행들이 보존된 다는 것을 의미하며 

 

RIGHT 키워드는 오른쪽 테이블의 행들이 보존된다는 것을 말한다. 

 

FULL 키워드는 양쪽 테이블의 모든 행이 보존된다는 것을 말한다. 

 

 

 

외부 조인의 세 번째 논리적 쿼리 프로세싱 단계는

 

ON 절의 술어를 기반으로 해서 다른 테이블과 매핑되지 않는

 

보존된 테이블의 행들도 표시하는 것이다.

 

 

이 단계에서는 조인의 처음 두 단계를 통해 만들어진 결과 테이블에 

 

매핑이 안 된 행(외부 행)들을 추가한 다음, 

 

외부 행들의 보존되지 않는 측면의 조인으로부터 오는 특성들의 

 

값 부분을 NULL로 처리하게 된다.

 

아래의 테이블을 예시로 외부 조인을 적용해보자.

 

-- 고객테이블
CREATE TABLE dbo.TB_CUST( 
	cust_no INT NOT NULL, -- 고객번호 
	cust_name NVARCHAR(100) NOT NULL -- 고객이름 
	CONSTRAINT PK__TB_CUST__CUST_NO PRIMARY KEY(cust_no) 
);

INSERT INTO TB_CUST(cust_no, cust_name) VALUES(1, 'MIKE'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(2, 'OBAMA'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(3, 'TRUMP'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(4, 'MESSI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(5, 'RONALDO'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(6, 'MALDINI'); -- 주문을 하지 않은 고객
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(7, 'SANCHEZ'); -- 주문을 하지 않은 고객

--제품 테이블
CREATE TABLE dbo.TB_PRODUCT( 
	product_no INT NOT NULL, -- 제품 번호   
	product_name NCHAR(100) NOT NULL -- 제품 이름 
	CONSTRAINT PK__TB_PRODUCT__PRODUCT_NO PRIMARY KEY(product_no) 
);

INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(701, 'MacBook'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(702, 'MagicMouse'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(703, 'IPad'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(704, 'IPhone'); 

-- 주문 테이블
CREATE TABLE dbo.TB_ORDER( 
	order_no INT NOT NULL, --주문 번호 
	cust_no INT NOT NULL, -- 주문을 한 고객 번호 (TB_CUST 의 기본키를 참조로 함) 
	product_no INT NOT NULL -- 주문한 고객이 산 제품 번호(TB_PRODUCT 의 기본키를 참조로 함) 
	CONSTRAINT PK__TB_ORDER__ORDER_NO PRIMARY KEY(order_no) 
);

INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201901, 1, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201902, 1, 702); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201904, 2, 704); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201905, 3, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201906, 4, 703); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201907, 5, 701);

 

어떤 고객이 어떤 주문을 했는지 알고 싶을 때가 있다. 

 

하지만, 주문을 한 고객뿐만 아니라 주문을 하지 않은 고객에 대한 정보도 알고 싶을 때가 있다. 

 

이런 경우에 OUTER JOIN을 쓰는 것이 바람직하다. (TB_CUST 테이블을 보존)

 

SELECT  
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
FROM dbo.TB_CUST c WITH(NOLOCK) 
LEFT OUTER JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no

위와 같은 형식으로 써준다면 LEFT OUTER JOIN 

 

즉 왼쪽에 있는 테이블인 TB_CUST 테이블을 보존하겠다는 말이 된다. 

 

즉 무조건 TB_CUST의 모든 데이터를 가져온다는 뜻이 되고,

 

TB_ORDER 과 매핑되지 않은 정보들은 모두 NULL 값으로 처리가 된다. 

 

 

첫 번째 그림을 보면 6,7번 고객들은 아무런 주문을 하지 않았다. 

 

쿼리의 결과에서 살펴보면 이 두 고객은 TB_ORDER 테이블로부터 오는 

 

특성(LEFT OUTER JOIN 기준으로 오른쪽에 있는 테이블)

 

들에는 NULL값으로 표시되고 있다.

 

 

 

논리적으로, 주문을 하지 않은 두 고객의 행들은

 

조인의 두 번째 단계에서 걸러졌다(ON 절을 기반으로 하는 필터를 통해). 

 

 

하지만, 세 번째 단계에서 이러한 행들이 외부 행으로 추가되었다.(*보존)

 

조인 방식이 내부 조인이었다면, 이 두 행은 반환되지 않았을 것이다. 

 

이러한 두 행은 왼쪽 테이블인 TB_CUST의 모든 행을 보존하기 위해 추가된 것이다.

 

 

외부 조인의 결과는 내부 행과 보존되는 쪽의 외부 행으로 구성된다고 생각하는 것이 이해하는데 도움이 된다. 

 

내부 행은 ON 술어를 기반으로 하는 다른 쪽의 테이블과 매칭 되는 행이며, 

 

외부 행은 매칭이 안 되는 행들이다.

내부 조인에서는 단지 내부 행만을 반환하는 데 비해, 

 

외부 조인에서는 내부 및 외부 행들을 모두 반환하게 된다. 

 

 

 

혼란을 야기하는 외부 조인에 대한 질문 중 하나는,

 

술어를 ON 절에 지정해야 하는지 아니면 쿼리의 WHERE 절에 지정해야 하는지에 대한 것이다.  

 

외부 조인의 보존된 쪽에 있는 행을 생각해보면,

 

ON 술어를 기반으로 하는 필터는 최종적인 것이 아니다. 

 

단지 다른 쪽에 있는 행들과 매칭 되는지 결정할 뿐이다. 

 

 

 

따라서 최종적이지 않은 술어(어떤 행이 보존되지 않는 쪽의 테이블에 있는 행들과 매핑되는지만 결정하는 술어)

 

역할로써 사용해야 한다면, 술어를 ON 절에 지정해야 한다. 

 

 

외부 행들이 만들어진 다음에 필터 해야 하고 필터가 최종적인 것이라면, 

 

WHERE 절에 술어를 지정해야 한다.

 

(WHERE 절은 FROM 절 다음에 처리되기 때문이다.) 

 

정확히 말해, WHERE 절은 모든 테이블 연산자가 처리되어(이 경우에는 외부 조인) 

 

모든 외부 행까지도 만들어지고 난 다음에 처리된다. 

 

또한, ON 절과는 달리 WHERE 절은 행을 마지막으로 필터링하는 작업을 수행한다.

 

즉, 술어를 ON 절에 쓰는 것과 WHERE 절에 쓰는 것과 다른 결과가 나올 수 있음을 시사한다.

 

 

아래의 쿼리를 보며 이해해보자.

SELECT  
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
FROM dbo.TB_CUST c WITH(NOLOCK) 
LEFT OUTER JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no and c.cust_name = 'MIKE'

join 필터링 조건을 보면 cust_no 가 일치하는지 검사하고 cust_name 이 'MIKE'인지 확인하고 있다.

 

만약에 이러한 조건을 만족하지 않는 행들은 모두 NULL 처리가 된다.

 

그다음 LEFT JOIN 이기 때문에 조인을 기점으로 왼쪽에 위치한 테이블 TB_CUST 가 보존되어야 하므로

 

해당 테이블의 모든 행을 가져오면 된다.

 

(대신 JOIN 조건에 만족하지 않은 TB_ORDER 테이블의 데이터는 NULL 이 된다.)

 

그럼 이번에는 WHERE 절에 cust_name = 'MIKE' 를 적어보자.

SELECT  
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
FROM dbo.TB_CUST c WITH(NOLOCK) 
LEFT OUTER JOIN dbo.TB_ORDER o WITH(NOLOCK) ON c.cust_no = o.cust_no  
WHERE c.cust_name = 'MIKE'

위와 같은 결과가 나오게 된다.

 

논리적으로 왜 위와 같은 결과가 나오는지 생각해보자.

 

아래의 결과는 두 테이블에 외부 조인을 걸어준 결과이다.

 

WHERE 절은 행을 마지막으로 '필터링' 하는 작업이기 때문에

 

WHERE 조건에 만족하지 않는 행들은 모두 제거해준다.

 

그러므로 3행에서 8행까지는 모두 제거되어 최종적으로 2행만 남게 된다.

 

 

 

 

 

 

 

2. 외부 조인의 보존되지 않는 테이블에 있는 특성을 필터링 하기

 

논리적인 버그를 찾기 위해 외부 조인을 포함하는 코드를 리뷰할 때 

 

검사해야 하는 부분 중 하나가 바로 WHERE 절이다. 

 

WHERE 절의 술어가 <특성><연산자><값> 과 같은 

 

형태의 표현식을 사용하는 조인의 보존되지 않는 쪽에 있는

특성을 참조한다면, 대개 이 경우가 버그를 의미한다. 

 

 

 

조인의 보존되지 않는 쪽으로부터의 특성은 외부 열인 경우에는 NULL 값을 가지며, 

 

NULL <연산자><값> 형태의 표현식은 UNKNOWN을 출력하기 때문이다. 

 

 

 

WHERE 절에서는 UNKNOWN 값을 결과에서 제외하므로 

 

WHERE 절에 있는 이러한 술부들은 모든 외부 행을 제거하게 되어, 

 

결과적으로 내부 조인과 동일한 방식으로 처리되도록 만들어버린다. 

 

 

 

 

개발자들은 조인 형태를 선택할 때 대개 실수하거나 술어 부분에서 실수하게 된다. 

 

예제를 통해 이해를 위해서 위에서 만들어준 TB_ORDER 테이블을 제거하고 다시 만들어주자.

 

DROP TABLE dbo.TB_ORDER

-- 주문 테이블
CREATE TABLE dbo.TB_ORDER( 
	order_no INT IDENTITY(1,1) NOT NULL, 
	cust_no INT NOT NULL, 
	product_no INT NOT NULL, 
	order_date SMALLDATETIME NOT NULL, 
	CONSTRAINT PK_TB_ORDER PRIMARY KEY(ORDER_NO) 
); 
INSERT INTO TB_ORDER VALUES(1,701,'2021-04-02 13:14:33'); 
INSERT INTO TB_ORDER VALUES(1, 702,'2021-04-02 13:14:33'); 
INSERT INTO TB_ORDER VALUES(2, 704,'2021-04-03 14:34:13'); 
INSERT INTO TB_ORDER VALUES(3, 701,'2021-04-04 15:14:41'); 
INSERT INTO TB_ORDER VALUES(4, 703,'2021-04-06 21:00:10'); 
INSERT INTO TB_ORDER VALUES(5, 701,'2021-04-09 08:50:55');

 

아래의 쿼리는 제품을 산 고객과 사지 않은 고객 모두의 정보를 가져오는 쿼리이다. (보존테이블이 고객 테이블)

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c WITH(NOLOCK)
LEFT JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no

 

하지만, 위의 쿼리를 기준으로 where 절에 보존되지 않는쪽의 테이블(TB_ORDER)에 

 

조건을 걸어준다면 아래처럼 작동하게 된다.

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c WITH(NOLOCK)
LEFT JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no 
WHERE o.order_date >= '20210405'

위의 쿼리의 결과의 실행계획을 살펴보고 옵티마이저가 어떤 방식으로 해당 쿼리를 풀어나가는지 확인해보자.

실행계획을 보면 의문이 들것이다.

 

분명히 LEFT JOIN을 통해서 조인절을 작성했는데 INNER JOIN으로 풀리고 있는 것이다.

 

옵티마이저가 저렇게 판단한 이유는 아래와 같다.

 

 

 

 

TABLE A , TABLE B 를 외부 조인한다고 했을 때, A 가 보존되려는 테이블이라고 가정해보자.

 

외부 조인의 조건을 만족시키는 행과 추가로 보존 테이블의 행을 가져와야 한다.(조인 조건을 만족하지 않는)

 

 

 

 

그리고 여기서 TABLE A 를 대상으로 하는 WHERE 조건이 아닌

 

TABLE B 를 대상으로 WHERE 조건을 건다면,

 

조인 조건을 만족하지 않는 보존테이블의 행에 딸려 나온 행들은 모두 사라지게 된다.

 

조인 조건을 만족하지 않는 비 보존 테이블의 행은 모두 NULL 로 표시되기 때문이다.

 

 

 

 

즉, 이런경우에는 굳이 OUTER JOIN이 될 필요가 없고

 

INNER JOIN 이 오히려 합리적이라는 판단이 서게 된다.

 

 

 

동일한 논리적 쿼리 프로세싱 단계에 있는 모든 표현식은 논리적으로 동일한 시점에 평가된다. 

 

그러나, 이 개념은 FROM 절의 테이블 연산자에 대한 처리 부분에서는 유효하지 않다. 

 

테이블 연산자는 논리적으로 왼쪽에서 오른쪽으로 평가된다. 

 

외부 조인이 처리되는 순서를 변경하게 되면 다른 형태의 결과가 출력될 수도 있다. 

 

따라서 임의로 이 순서를 변경해서는 안된다.

 

 

외부 조인이 처리되는 순서와 관련해서 몇 가지 논리적 버그가 있다. 

 

두 개의 테이블 간의 외부 조인을 먼저 수행한 다음 

 

세 번째 테이블과 내부 조인을 하는 멀티 조인 쿼리를 작성한다고 생각해보자. 

 

 

 

 

내부 조인의 ON 절에 있는 술어 부분에서 외부 조인의 보존되지 않는 쪽에 있는 특성들을 

 

세 번째 테이블과 비교하게 되면 모든 외부 행은 제거된다.  

 

외부 행들은 조인에서 보존되지 않는 쪽의 특성 값이 NULL이 될 수 있으며, 

 

NULL과 어떤 값을 비교하더라도 UNKNOWN 으로 처리되기 때문이다. 

 

UNKNOWN은 ON필터에 의해서 모두 필터링된다. 

 

다시 말해, 이러한 형태의 술부 또한 외부 조인을 무의미하게 만들어버리고 

 

논리적으로 내부 조인을 지정한 것처럼 처리하게 된다.

 

아래의 예시를 살펴보자

 

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c WITH(NOLOCK) 
LEFT JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no




SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c WITH(NOLOCK)
LEFT JOIN dbo.TB_ORDER o WITH(NOLOCK) 
	ON c.cust_no = o.cust_no 
INNER JOIN dbo.TB_PRODUCT p WITH(NOLOCK) 
	ON p.product_no = o.product_no

 

첫 번째 쿼리는 주문을 한 고객들의 정보와 주문을 하지 않은 고객들의 정보를 모두 출력시켜준다.

 

(LEFT OUTER JOIN에 의해)

 

 

 

하지만, 두 번째 쿼리는 INNER JOIN 조건에서  

 

p.product_no = o.product_no 라는 술어를 사용하여 TB_PRODUCT 테이블에

있는 제품 정보를 첫 번째 조인의 결과로부터 만들어지는 행들과 매칭 하게 된다.

 

(TB_CUST 테이블과 TB_ORDER테이블의 조인)

 

 

 

그러나, 아무런 주문을 하지 않은 고객을 나타내는 행에서는

 

product_no 가 null 이므로 unknown 처리가 되어

 

필터링 조건에 의해서 걸러지게 되며,

 

결국, 아무런 주문을 하지 않은 고객들의 정보가 통째로 누락되게 된다.

 

 

일반적으로 외부 조인 뒤에 내부 조인이 결합되면 외부 행은 제거된다.

 

물론, 조인 조건이 왼쪽에 있는 null값과

 

오른쪽에 있는 어떠한 값을 비교할 때만 그러하다.

 

 

결과에 주문을 하지 않은 고객들까지 포함해서 출력하고 싶은 경우에는 

 

이문제를 해결할 수 있는 몇 가지의 방법이 존재한다. 

 

이러한 방법 중 하나는 두 번째 조인 역시 left outer join 으로 설정하는 것이다.

아래의 방식을 사용하면, 첫 번째 조인으로부터 만들어진 외부 행들은 필터링되지 않는다.

 

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c 
LEFT JOIN dbo.TB_ORDER o ON c.cust_no = o.cust_no 
LEFT JOIN dbo.TB_PRODUCT p ON p.product_no = o.product_no

두 번째 옵션은 TB_ORDER 테이블과 TB_PRODUCT 테이블을 내부 조인으로 처리한 다음에 

 

TB_CUST 테이블을 RIGHT JOIN 으로 사용해서 조인하는 것이다. 

 

RIGHT JOIN 이므로 해당 조인을 기준으로 오른쪽 테이블이 보존 테이블로 지정된다.

아래의 방법을 사용하면, 외부 행들은 마지막 조인에 의해 만들어지며 필터링되지 않는다.

 

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM TB_ORDER o 
INNER JOIN dbo.TB_PRODUCT p ON p.product_no = o.product_no 
RIGHT JOIN dbo.TB_CUST c ON c.cust_no = o.cust_no

 

세 번째 옵션은, 괄호를 사용해서 TB_ORDER 테이블과 TB_PRODUCT 테이블 간의 내부 조인을 

 

독립적인 논리적 단계로 변경하는 방법이다. 

 

이 방법을 사용하면 TB_CUST 테이블을 TB_ORDER 와 TB_PRODUCT 간의

 

내부 조인을 한 결과에 대해 LEFT JOIN을 수행하게 된다.

 

SELECT 
	c.cust_no 
,	c.cust_name 
,	o.order_no 
,	o.cust_no 
,	o.product_no 
,	o.order_date 
FROM dbo.TB_CUST c 
LEFT JOIN (dbo.TB_ORDER o  
	INNER JOIN dbo.TB_PRODUCT p ON p.product_no = o.product_no 
) ON c.cust_no = o.cust_no