[SQL] JOIN이란? - OUTER JOIN

 이전 시간에는 INNER JOIN에 대해서 이야기했었습니다. 오늘은 OUTER JOIN에 대해 이야기해 볼까 합니다.

 

2020/09/15 - [개발 창고/데이터베이스 개발] - [SQL] JOIN이란? - INNER JOIN

[SQL] JOIN이란? - INNER JOIN

 

 먼저 이전 글에서와 같이 아래와 같은 테이블이 있다고 가정하고 진행하도록 하겠습니다. 이전 게시글과 차이는 설명을 위해 TB_BOARD_COMMENT에 TB_BOARD에 BBS_SEQ가 없는 BBS_SEQ가 4번인 데이터를 추가했다는 점입니다.

 

  TB_BOARD

TB_BOARD 내용

 

  TB_BOARD_COMMENT

TB_BOARD_COMMNET 내용

---

  OUTER JOIN (외부 결합)

 INNER JOIN과 OUTER JOIN의 차이는 A와 B테이블 사이에 일치하지 않는 데이터도 보여줄 것인지에 대한 결정입니다.

 

 게시글을 예시로 들면,

 

 - INNER JOIN : 코멘트가 있는 게시글만 보여주세요.

 - OUTER JOIN : 코멘트가 없는 게시글도 보여주세요.

 

이는 두 테이블을 결합할 때, 매칭 되지 않는 데이터도 보여줄 것인지를 결정하는 기준이라고 볼 수 있습니다.

 

OUTER JOIN에는 FULL OUTER JOIN과 LEFT OUTER JOIN, RIGHT OUTER JOIN 총 3가지의 JOIN방식이 있는데, 설명을 위해 빈번하게 사용되는 순(지극히 개인적인 기준)으로 LEFT OUTER JOIN → RIGHT OUTER JOIN → FULL OUTER JOIN 순으로 설명을 드리도록 하겠습니다.

 

※ 참고: 보통 SELECT문을 아래와 같이 사용하시는 분들은 어디가 LEFT이고 어디가 RIGHT인지 헷갈릴 수도 있는데,

/* 명시적 표현 */
SELECT *
  FROM 테이블1
 INNER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1

/* 암시적 표현 */
SELECT *
  FROM 테이블1
     , 테이블2
 WHERE 테이블1.컬럼1 = 테이블2.컬럼1

이 테이블을 한줄로 나열하면 다음과 같이 보일 것입니다.

/* 명시적 표현 */
SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.컬럼1 = 테이블2.컬럼1

/* 암시적 표현 */
SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.컬럼1 = 테이블2.컬럼1

이 기준에서 테이블1이 LEFT이고 테이블2가 RIGHT라고 보시면 됩니다. (테이블은 1, 2만 존재하는 것은 아니며 이어서 계속 표현 가능하십니다.

---

  LEFT OUTER JOIN (왼쪽 외부 결합)

 LEFT OUTER JOIN은 왼쪽 기준을 오른쪽에 매칭 되는 데이터가 없어도 표현해주는 방식으로 문법은 아래와 같습니다.

/* 명시적(explicit) LEFT OUTER JOIN 표현 */
SELECT *
  FROM 테이블1
  LEFT OUTER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]

/* 암시적(IMPLICIT) LEFT OUTER JOIN 표현 : 오라클 */
SELECT *
  FROM 테이블1
     , 테이블2
 WHERE 테이블1.컬럼1 = 테이블2.컬럼1(+) [AND 테이블1.컬럼2 = 테이블2.컬럼2(+) ... ]
 
/* 암시적(IMPLICIT) LEFT OUTER JOIN 표현 : 사이베이스 */
SELECT *
  FROM 테이블1
     , 테이블2
 WHERE 테이블1.컬럼1 *= 테이블2.컬럼1 [AND 테이블1.컬럼2 *= 테이블2.컬럼2 ... ]

 

 

테이블 1 기준으로 데이터 조회

 

 집합 관계 테이블로 표현하면 다음과 같이 테이블1 기준으로 보임을 의미합니다.

SELECT *
  FROM TB_BOARD
  LEFT OUTER JOIN TB_BOARD_COMMENT
    ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ

 위의 예시 테이블을 다음과 같이 LEFT OUTER JOIN 하면 다음과 같은 결과를 볼 수 있는데,

 

LEFT JOIN 결과

 

 INNER JOIN에서는 출력되지 않았던 BBS_SEQ가 3번인 데이터가 CMNT_SEQ 등은 NULL인 채로 출력됨을 확인할 수 있습니다.

 

LEFT JOIN 작동 방식

위의 그림을 해석하면,

 

1) TB_BOARD의 1번 줄과 BBS_SEQ는 TB_BOARD_COMMENT의 1번 줄과 2번 줄의 BBS_SEQ와 일치

2) TB_BOARD의 2번 줄과 BBS_SEQ는 TB_BOARD_COMMENT의 3번 줄의 BBS_SEQ와 일치

3) TB_BOARD의 3번 줄과 BBS_SEQ는 TB_BOARD_COMMENT에 일치하는 데이터가 없으므로 TB_BOARD_COMMENT 값들을 NULL로 표현

4) TB_BOARD_COMMENT의 4번 줄은 TB_BOARD에 포함되지 않으므로 제외됩니다.

 

이라고 보실 수 있습니다.

 

이는 현업에서 다음과 같은 멘트를 들었다 하면

 

"게시글에 코멘트도 포함해서 보여주세요"

 

라고 한다면, 다음과 같이 표현해줄 수 있을 겁니다. 물론, 중복 등에 대해 보여줄지 (GROUP BY), 아니면 코멘트가 있는 것만 보여줄지 (INNER JOIN) 등의 디테일한 판단이 필요하긴 합니다. 보통 LEFT OUTER JOIN은 GROUP BY와 함께 쓰이는 경우가 많은데, 그 이유는 집계(통계) 데이터를 보여주어야 하는 경우에 주로 쓰입니다.

 

 ex) 게시글 목록에 포함된 코멘트 개수

 

GROUP 함수에 대해서는 추후에 다뤄보도록 하겠습니다.

---

  RIGHT OUTER JOIN (오른쪽 외부 결합)

RIGHT OUTER JOIN은 오른쪽 기준을 왼쪽에 매칭 되는 데이터가 없어도 표현해주는 방식으로 문법은 아래와 같습니다.

/* 명시적(explicit) RIGHT OUTER JOIN 표현 */
SELECT *
  FROM 테이블1
  RIGHT OUTER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]

 

테이블2 기준으로 데이터 조회

 

 집합 관계 테이블로 표현하면 다음과 같이 테이블2 기준으로 보임을 의미합니다.

SELECT *
  FROM TB_BOARD
  RIGHT OUTER JOIN TB_BOARD_COMMENT
    ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ

 위의 예시 테이블을 다음과 같이 RIGHT OUTER JOIN 하면 다음과 같은 결과를 볼 수 있는데,

 

RIGHT JOIN 결과

 LEFT OUTER JOIN에서 출력되던 BBS_SEQ 3번이 제외되고 TB_BOARD_COMMENT가 BBS_SEQ 4번인 데이터가 BBS_TITLE 등은 NULL인 채로 출력됨을 확인할 수 있습니다.

 

RIGHT JOIN 작동 방식

 

위의 그림을 해석하면,

 

1) TB_BOARD_COMMENT의 1번 줄과 BBS_SEQ는 TB_BOARD의 1번 줄의 BBS_SEQ와 일치

1) TB_BOARD_COMMENT의 2번 줄과 BBS_SEQ는 TB_BOARD의 1번 줄의 BBS_SEQ와 일치

3) TB_BOARD_COMMENT의 3번 줄과 BBS_SEQ는 TB_BOARD의 2번 줄의 BBS_SEQ와 일치

3) TB_BOARD_COMMENT의 4번 줄과 BBS_SEQ는 TB_BOARD에 일치하는 데이터가 없으므로 TB_BOARD 값들을 NULL로 표현

4) TB_BOARD의 4번 줄은 TB_BOARD_COMMENT에 포함되지 않으므로 제외됩니다.

 

이라고 보실 수 있습니다.

---

  FULL OUTER JOIN (완전 외부 결합)

FULL OUTER JOIN은 왼쪽 오른쪽 모두 매칭 되는 데이터가 없어도 표현해 주는 방식으로 문법은 아래와 같습니다.

/* 명시적(explicit) FULL OUTER JOIN 표현 */
SELECT *
  FROM 테이블1
  FULL OUTER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]

 

※ 참고. MySQL의 경우 FULL OUTER JOIN을 지원하지 않기 때문에 다음과 같이 풀어서 사용해야 합니다.

/* 명시적(explicit) FULL OUTER JOIN 표현 */
/* 일치하는 것 */
SELECT *
  FROM 테이블1
 INNER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]
/* 테이블2의 값이 비어있는 것 */
 UNION ALL
SELECT *
  FROM 테이블1
  LEFT OUTER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]
 WHERE 테이블2.컬럼2 IS NULL
/* 테이블1의 값이 비어있는 것 */
 UNINO ALL
SELECT *
  FROM 테이블1
  RIGHT OUTER JOIN 테이블2
    ON 테이블1.컬럼1 = 테이블2.컬럼1 [AND 테이블1.컬럼2 = 테이블2.컬럼2 ... ]
 WHERE 테이블1.컬럼1 IS NULL

 

테이블1과 테이블2 전체를 기준으로 조회

 

 집합 관계 테이블로 표현하면 다음과 같이 테이블1과 테이블2 모두를 기준으로 보임을 의미합니다.

/* 일반적으로 */
SELECT *
  FROM TB_BOARD
  FULL OUTER JOIN TB_BOARD_COMMENT
    ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ

/* MySQL에서 */
/* 일치하는 것 */
SELECT *
  FROM TB_BOARD
  INNER JOIN TB_BOARD_COMMENT
    ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ
 /* TB_BOARD_COMMENT의 값이 비어있는 것 */
 UNION ALL
SELECT *
  FROM TB_BOARD
  LEFT OUTER JOIN TB_BOARD_COMMENT ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ
 WHERE TB_BOARD_COMMENT.BBS_SEQ IS NULL
 /* TB_BOARD의 값이 비어있는 것 */
 UNION ALL
SELECT *
  FROM TB_BOARD
  RIGHT OUTER JOIN TB_BOARD_COMMENT ON TB_BOARD.BBS_SEQ = TB_BOARD_COMMENT.BBS_SEQ
 WHERE TB_BOARD.BBS_SEQ IS NULL

 위의 예시 테이블을 다음과 같이 RIGHT OUTER JOIN 하면 다음과 같은 결과를 볼 수 있는데,

 

FULL OUTER JOIN 결과

 

 LEFT OUTER JOIN에서 출력되던 BBS_SEQ 3번과 RIGHT OUTER JOIN에서 출력되던 TB_BOARD_COMMENT의 BBS_SEQ 4번인 데이터가 모두 각각 상대 테이블에 대해 NULL인 채로 출력됨을 확인할 수 있습니다.

 

FULL OUTER JOIN 작동 방식

 

 위의 그림을 해석하면,

 

1) TB_BOARD의 1번 줄과 BBS_SEQ는 TB_BOARD_COMMENT의 1번 줄과 2번 줄의 BBS_SEQ와 일치

2) TB_BOARD의 2번 줄과 BBS_SEQ는 TB_BOARD_COMMENT의 3번 줄의 BBS_SEQ와 일치

3) TB_BOARD의 3번 줄과 BBS_SEQ는 TB_BOARD_COMMENT에 일치하는 데이터가 없으므로 TB_BOARD_COMMENT 값들을 NULL로 표현

4) TB_BOARD_COMMENT의 4번 줄과 BBS_SEQ는 TB_BOARD에 일치하는 데이터가 없으므로 TB_BOARD 값들을 NULL로 표현

 

이라고 보실 수 있습니다.