[SQLite] JOIN#3(FULL OUTER JOIN): 두 테이블 데이터 모두 출력하기!

안녕하세요, 오늘은 SQL에서 두 테이블 내 데이터를 모두 출력해 주는 OUTER JOIN 기능에 대해 소개해 드립니다. 이번 포스팅에서도 SQLite Studio를 기반으로 연습용 데이터셋을 활용하여 설명해 드리고자 합니다. SQLite Studio 및 연습용 데이터셋 설치는 아래 포스팅을 참고해 주세요.

heytech.tistory.com/11?category=453619

[SQL] SQLite 및 연습용 데이터셋 설치하기!

그럼 바로 시작하죠!

목차

1.  JOIN 기능이란?
2.  LEFT JOIN, RIGHT JOIN 이란?
3.  두 테이블 전체 출력
4.  두 테이블의 교집합을 제외한 전체 출력
5.  교집합 포함/미포함 행의 개수 비교
    5.1.  교집합을 포함했을 때 행의 개수
    5.2.  교집합을 제외했을 때 행의 개수

1.  JOIN 기능이란?

JOIN은 하나의 데이터베이스 내의 여러 테이블의 데이터를 조합하여 하나의 칼럼으로 표현해 주는 기능을 말합니다. 아래 예제를 통해 더욱 직관적으로 이해하실 수 있도록 설명하겠습니다.

JOIN 종류

 

그림 1. SQL JOINS (출처: https://commons.wikimedia.org/wiki/File:SQL_Joins.svg)

위의 그림 1에서 보실 수 있듯이 SQL JOIN에는 여러 종류가 있으며 크게는 아래와 같이 4가지로 구분할 수 있습니다.

 

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

---

오늘은 위 4가지 중 4번째인 FULL OUTER JOIN에 대해 알아보고자 합니다.

 

2.  FULL OUTTER JOIN 이란?

그림 2. SQL FULL OUTER JOIN (출처: https://commons.wikimedia.org/wiki/File:SQL_Joins.svg)

FULL OUTTER JOIN의 종류는 그림 2 와 같이 2종류가 있습니다.

• 두 테이블 전체 (합집합, AUB)
• 두 테이블의 교집합을 제외한 전체 (AUB)-(AnB)

3.  두 테이블 전체 출력

SQLite의 OUTER JOIN 구문 미지원

연습용 데이터셋에서 People 테이블과 AllstarFull 테이블을 FULL OUTER JOIN 하는 코드를 작성해 보겠습니다. 이전 포스팅에서는 INNER JOIN, LEFT JOIN 구문을 그대로 입력해도 쿼리문 동작에 문제가 없었습니다.

소스코드

-- FULL OUTER JOIN 구문을 바로 사용하는 경우
SELECT
    *
FROM 
    People t1
FULL OUTER JOIN
    AllstarFull t2
    ON t1.playerID = t2.playerID
LIMIT
    20;

하지만, SQLite에서 위와 같이 FULL OUTER JOIN 구문을 그대로 작성하면 아래 그림 3 과 같이 에러가 발생합니다. 즉, SQLite에서는 RIGHT JOIN과 FULL OUTER JOIN 구문을 그대로 작성하면 안 됩니다.

 

그림 3. SQLite의 RIGHT JOIN 및 FULL OUTER JOIN 미지원

LEFT JOIN을 활용한 FULL OUTER JOIN 표현

FULL OUTER JOIN은 그림 4 와 같이 LEFT JOIN을 2번 작성함으로써 그림 5 와 같이 각 테이블을 합치는 형태로 작성할 수 있습니다.

 

그림 4. LEFT JOIN(좌) 및 RIGHT JOIN(우)

그림 5. FULL OUTER JOIN (교집합 포함)

소스코드

-- 1) 두 테이블 전체 데이터 출력하기
SELECT
    *
FROM
    People t1    
-- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
LEFT JOIN
    AllstarFull t2
    -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
    -- ON 키워드 대신에 USING 사용가능.
    USING(playerID)
-- 쿼리문을 중복 제거한 후 합치기
UNION
SELECT 
    *
FROM 
    AllstarFull t2 
-- People 테이블(t1)을 t2에 LEFT JOIN 할 것을 지정
LEFT JOIN 
    People t1
    -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
    USING(playerID)
-- 상위 20개의 데이터만 출력
LIMIT 20;

 

위 소스코드는 People 테이블과 AllstarFull 테이블 각각을 1차례씩 기준 테이블로 선정하고 LEFT JOIN 한 쿼리문을 합침으로써 FULL OUTER JOUN 구문을 작성한 예시 코드입니다.

11, 22째줄

USING 키워드: LEFT JOIN에서 기준으로 삼을 칼럼을 등록할 때 ON 키워드가 아닌 USING 키워드를 사용하였습니다.

13째줄

UNION 연산자: 각 LEFT JOIN 쿼리문을 합치기 위해 UNION 연산자를 활용하였으며 중복을 제거하기 위해 ALL 키워드를 작성하지 않았습니다. 중복을 허용하고 싶으시다면 UNION ALL으로 작성하시면 됩니다.

출력 결과

그림 6. FULL OUTER JOIN 쿼리문 결과(중복 제거)

4.  두 테이블의 교집합을 제외한 전체 출력

이번에는 아래 그림 7 과 같이 두 테이블의 교집합을 제외한 나머지 전체를 출력하는 쿼리문을 작성해 보겠습니다.

그림 7. FULL OUTER JOIN (교집합 미포함)

소스코드

-- 1) 두 테이블 전체 데이터 출력하기
SELECT
    *
FROM
    People t1    
-- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
LEFT JOIN
    AllstarFull t2
    -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
    -- ON 키워드 대신에 USING 사용가능.
    USING(playerID)
-- 교집합 제외
WHERE
	t2.playerID is NULL
-- 쿼리문을 중복 제거한 후 합치기
UNION
SELECT 
    *
FROM 
    AllstarFull t2 
-- People 테이블(t1)을 t2에 LEFT JOIN 할 것을 지정
LEFT JOIN 
    People t1
    -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
    USING(playerID)
-- 교집합 제외
WHERE
	t1.playerID is NULL
-- 상위 20개의 데이터만 출력
LIMIT 20;

앞서 살펴본 교집합을 포함한 FULL OUTER JOIN 작성 코드에서 각 LEFT JOIN 구문 내 두 테이블 간의 교집합을 제외하기 위해 WHERE 구문을 추가하였습니다.

5.  교집합 포함/미포함 행의 개수 비교

실제로 교집합을 제외하기 전과 후에 행의 개수에는 얼마나 차이가 날까요? 직접 알아보겠습니다.

5.1.  교집합을 포함했을 때 행의 개수

소스코드

-- 교집합을 포함한 FULL OUTER JOIN 행 개수
SELECT
	COUNT(*)
FROM
  (SELECT
      *
  FROM
      People t1    
  -- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
  LEFT JOIN
      AllstarFull t2
      -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
      -- ON 키워드 대신에 USING 사용가능.
      USING(playerID)
  -- 쿼리문을 중복 제거한 후 합치기
  UNION
  SELECT 
      *
  FROM 
      AllstarFull t2 
  -- People 테이블(t1)을 t2에 LEFT JOIN 할 것을 지정
  LEFT JOIN 
      People t1
      -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
      USING(playerID)
      )

출력 결과

그림 8. 교집합 포함 행 개수

5.2.  교집합을 제외했을 때 행의 개수

소스코드

-- 교집합을 제외한 FULL OUTER JOIN 행 개수
SELECT
	COUNT(*)
FROM
  (SELECT
      *
  FROM
      People t1    
  -- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
  LEFT JOIN
      AllstarFull t2
      -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
      -- ON 키워드 대신에 USING 사용가능.
      USING(playerID)
  -- 교집합 제외
  WHERE
      t2.playerID is NULL
  -- 쿼리문을 중복 제거한 후 합치기
  UNION
  SELECT 
      *
  FROM 
      AllstarFull t2 
  -- People 테이블(t1)을 t2에 LEFT JOIN 할 것을 지정
  LEFT JOIN 
      People t1
      -- playerID 칼럼을 기준으로 두 개의 테이블을 JOIN
      USING(playerID)
  -- 교집합 제외
  WHERE
      t1.playerID is NULL)

출력 결과

그림 9. 교집합 제외 행 개수

그림 8과 그림 9와 같이 교집합인 행의 개수가 10,582개인 것을 확인할 수 있었습니다.

📚 참고할 만한 포스팅

1.  [SQL] JOIN#1(INNER JOIN): 두 테이블의 교집합 출력하기!
2.  [SQL] JOIN#2(LEFT/RIGHT JOIN): 교집합+ 좌/우 테이블 출력하기!
3.  [SQL] JOIN#3(FULL OUTER JOIN): 두 테이블 데이터 모두 출력하기!

---

포스팅 내용에 오류가 있을 경우 댓글 남겨주시면 대단히 감사드리겠습니다.

그럼 오늘도 건강한 하루 보내시길 바랍니다 :)

고맙습니다.