MySQL 의 LEFT OUTER JOIN - 23년 7월 수정

Author: 니용

 

 

이번 글에서 다룰 내용은 MySQL에서 TABLE JOIN 할 수 있는 쿼리 중 LEFT OUTER JOIN입니다.

 

보통 INNER JOIN을 많이 사용하는 것으로 알고 있는데, INNER JOIN의 문제점은 여러 테이블 중 단 한 개에 테이블에 ROW가 존재하지 않는다면 데이터가 출력이 되지 않습니다. 쿼리로 표현하면 이와 같은 방식이죠.

SELECT
    A.*, B.*, C.*
FROM TABLE_A A, TABLE_B B, TABLE_C C
WHERE 1 = 1
  AND A.SEQUENCE = B.SEQUENCE
  AND A.USER_ID = C.USER_ID
  AND B.ROLE IS NOT NULL
  AND C.ARTICLE_COUNT > 0

(1)

 

 

1. OUTER JOIN 종류

대개 이를 방지하기 위해 OUTER JOIN을 많이 사용합니다. OUTER JOIN은 두 가지 종류가 있습니다.

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN

 

먼저 LEFT OUTER JOIN을 쿼리를 통해서 확인해 보겠습니다.

SELECT
        A.*, 
        B.*, 
        C.*
FROM TABLE_A A 
        LEFT OUTER JOIN TABLE_B B 
          ON B.SEQUENCE = A.SEQUENCE 
          AND B.ROLE IS NOT NULL
        LEFT OUTER JOIN TABLE_C C 
          ON C.USER_ID = A.USER_ID 
          AND C.ARTICLE_COUNT> 0

(2)

 

기본적인 OUTER JOIN의 문법은 JOIN 할 두 개의 테이블을 사이에 두고 LEFT OUTER JOIN 혹은 RIGHT OUTER JOIN을 입력합니다. INNER JOIN에서 사용하는 테이블 사이에 콤마(,)를 생각하시면 됩니다.

 

또 각각의 테이블은 Alias라고 하여 A, B처럼 별칭을 둘 수 있습니다. 테이블명이 길어지면 간략히 쓸 때 유용합니다. LEFT OUTER JOIN 은 왼쪽에 있는 테이블을 기준으로 쿼리가 수행됩니다. RIGHT OUTER JOIN은 그 반대입니다. 기준이 되는 테이블에 따라 결과가 천차만별로 달라질 수 있습니다. 예시는 아래에 더 자세히 설명드리겠습니다.

 

---

 

2. LEFT OUTER JOIN 사용법

3개의 테이블을 LEFT OUTER JOIN 한 쿼리를 예시로 들었습니다. 위의 (1), (2) 두 개 쿼리에서는 모두 TABLE_B의 SEQUENCE 값과 TABLE_A의 SEQUENCE의 값을 JOIN 하고 있습니다.  그 외의 조건식도 다 마찬가지죠.

하지만 TABLE_B, TABLE_C에 조건과 일치하는 데이터가 없다면, 혹은 다른 데이터가 존재한다면 결과적으로 출력되는 ROW 수는 두 쿼리에 차이가 있을 수 있습니다.

 

적어도 (1)의 쿼리에서 나오는 결과물의 ROW 수는 (2)의 쿼리에서 나오는 결과물의 ROW 수보다 작거나 같을 것입니다. 이유는? (2)의 쿼리는 바로 OUTER JOIN을 사용했기 때문입니다. 

 

TABLE_A

SEQUENCE(PK)	USER_ID	NICKNAME
1	NIYO	니요
2	ABBO	그룹
3	BAB	밥
4	POP	팦
5	JII	지
6	AQUA	아쿠아

TABLE_B

SEQUENCE(PK)	ROLE	COMMENT
1	ADMIN	관리자
3	GUEST	게스트1
4	GUEST	게스트2

TABLE_C

USER_ID(PK)	HOMEPAGE	ARTICLE_COUNT
NIYO	abbo.tistory.com	5
ABBO	abbo.tistory.com	1
JII	abbo.tistory.com	0

 

먼저 (1)의 쿼리를 조회하면 다음과 같이 출력될 거예요.

 

A.SEQUENCE	A.USER_ID	NICKNAME	B.SEQUENCE	ROLE	COMMENT	C.USER_ID	HOMEPAGE	ARTICLE_COUNT
1	NIYO	니요	1	ADMIN	관리자	NIYO	abbo.tistory.com	5

결과는 1개의 ROW가 출력됩니다. 다른 WHERE 조건에서 많은 것이 필터 됐기 때문에 다른 ROW가 어디가 누락되었는지 보이지가 않죠.

 

(2)의 쿼리를 조회하면 결과가 다음과 같이 출력됩니다.

 

A.SEQUENCE	A.USER_ID	NICKNAME	B.SEQUENCE	ROLE	COMMENT	C.USER_ID	HOMEPAGE	ARTICLE_COUNT
1	NIYO	니요	1	ADMIN	관리자	NIYO	abbo.tistroy.com	5
2	ABBO	그룹	(NULL)	(NULL)	(NULL)	ABBO	abbo.tistory.com	1
3	BAB	밥	3	GUEST	게스트1	(NULL)	(NULL)	(NULL)
4	POP	팦	4	GUEST	게스트2	(NULL)	(NULL)	(NULL)
5	JII	지	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)
6	AQUA	아쿠아	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)	(NULL)

 

결과가 많이 차이 나는데요, LEFT OUTER JOIN을 한 경우 TABLE_A를 기준으로 ROW가 존재하면 출력을 해준 것과 결과가 같게 나왔습니다. RIGHT OUTER JOIN을 쓴다면 이렇게 되겠네요. 

SELECT 
      B.*, A.*
FROM TABLE_B B 
        RIGHT OUTER JOIN TABLE_A A 
          ON B.SEQUENCE = A.SEQUENCE
WHERE B.ROLE IS NOT NULL

 

B.SEQUENCE	B.ROLE	B.COMMENT	A.SEQUENCE	A.USER_ID	A.NICKNAME
1	ADMIN	관리자	1	NIYO	니요
(NULL)	(NULL)	(NULL)	2	ABBO	그룹
3	GUEST	게스트1	3	BAB	밥
4	GUEST	게스트2	4	POP	팝
(NULL)	(NULL)	(NULL)	5	JII	지
(NULL)	(NULL)	(NULL)	6	AUQA	아쿠아

 

보시다시피 RIGHT OUTER JOIN을 사용하면 기준이 JOIN 절 내부로 들어가기 때문에 1:1 또는 1:n의 조건을 2번 이상 사용해야 한다면 LEFT OUTER JOIN이 쿼리를 작성하는데 유리합니다.

 

---

 

3. JOIN 쿼리를 사용할 때의 주의할 점

모든 쿼리에서 JOIN을 사용한다면 좋지만 ERD나 테이블 설계에 따라 그렇지 않은 상황도 발생합니다. 조인을 사용할 때 주의 사항을 간단히 알아보겠습니다.

 

1. SQL 문장의 의미를 명확히 파악하고 작성하는 것이 좋습니다.

쿼리를 작성하는 초보자분들에게는 다소 어려울 수도 있습니다. 성능에 영향을 미치기 때문입니다. 위의 (2)와 같은 조인 쿼리는 ON 조건 내의 묶는 조건이 PK로 잡혀 있기에 쿼리가 수행될 때 Index에 영향을 받아 빠르게 수행됩니다. 하지만 다른 조건이 걸려있는 경우 불필요하게 많은 쿼리를 조회하고 Index의 영향을 받지 않기 때문에 신중하게 선택하여야 합니다. 

 

2. 조인 조건이 명확해야 합니다.

위의 내용과 유사하지만 약간 다른 것이 의도한 바와 다르게 ANSI의 CROSS JOIN(Cartesian Product)이 될 가능성도 있습니다. 카티션 프로덕트는 곱셈을 의미하는데 결과물의 개수가 2배 이상이 될 수 있어 결과물을 출력하는데 유의해야 하고, 쿼리의 결과를 내부 로직에서 정제 및 연산해야 하는 번거로움이 있을 수 있습니다. 그래서 꼭 필요하지 않다면 사용하지 않는 게 좋습니다.

 

3. 조인 적용 후 EXPLAIN을 해보자.

정성을 들여 쿼리가 완성되었다면, 쉽게 테스트를 해볼 수 있습니다. 바로 EXPLAIN인데요, 이는 MySQL에서 제공하는 함수입니다. 사용 방법은 아래와 같이 SELECT 절 앞에 작성하면 됩니다.

 

EXPLAIN
SELECT 
        A.*, 
        B.*, 
        C.*
FROM TABLE_A A 
        LEFT OUTER JOIN TABLE_B B 
          ON B.SEQUENCE = A.SEQUENCE AND B.ROLE IS NOT NULL
        LEFT OUTER JOIN TABLE_C C 
          ON C.USER_ID= A.USER_ID AND C.ARTICLE_COUNT> 0

이렇게 조회를 해보면 해당 쿼리가 인덱스에 적용을 받는지(개발자 사이에서는 보통 인덱스를 탄다라고 표현합니다.) 받지 않게 되면 테이블 설계자에게 인덱스 추가 요청을 할지 확인할 수 있게 됩니다. EXPLAIN의 설명은 추후에 또 작성해 보도록 하겠습니다.

 

모두 LEFT OUTER JOIN을 적절히 활용하여 원하는 결과를 빠르게 얻는 개발자가 되었으면 좋겠습니다!!