[SQLite] JOIN#2(LEFT/RIGHT JOIN): 교집합+ 좌/우 테이블 출력하기!

안녕하세요, 오늘은 SQL에서 좌/우 테이블과 함께 테이블 간의 교집합을 출력하는 LEFT/RIGHT JOIN 기능에 대해 소개해 드립니다.

이번 포스팅에서도 SQLite Studio를 기반으로 연습용 데이터셋을 활용하여 설명해 드리고자 합니다.

SQLite Studio 및 연습용 데이터셋 설치는 아래 포스팅을 참고해 주세요.

heytech.tistory.com/11?category=453619

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

그럼 바로 시작하죠!

목차

1.  JOIN 기능이란?
2.  LEFT JOIN, RIGHT JOIN 이란?
3.  예시 문제(Q1)
    3.1.  소스코드
    3.2.  소스코드 실행 결과
    3.3.  올스타에 선정/미선정 선수의 수
    3.4.  해석 시 주의사항
4.  예시 문제(Q2)
    4.1.  소스코드
    4.2.  소스코드 실행 결과(올스타에 선정된 적이 없는 선수의 수)
    4.3.  올스타에 1회 이상 선정된 선수의 수

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
• OUTER JOIN

---

오늘은 위 4가지 중 2, 3번째인 LEFT JOIN과 RIGHT JOIN에 대해 알아보고자 합니다.

2.  LEFT JOIN, RIGHT JOIN 이란?

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

먼저, LEFT JOIN은 그림 2 처럼 교집합을 포함한 좌측 테이블(i.e., 테이블 A, AU(AnB))만 출력하거나, 교집합을 제외한 좌측 테이블만 출력(i.e., 차집합, A-B)하는 역할을 합니다.

 

그림 3. LEFT JOIN (출처: https://commons.wikimedia.org/wiki/File:SQL_Joins.svg)

RIGHT JOIN은 그림 3 처럼 LEFT JOIN과는 역할은 같지만 항상 데이터를 포함하는 테이블이 우측 테이블(그림 1 내 Table B)이라는 점이 다릅니다. 

3.  예시 문제(Q1)

Q1.  주어진 연습용 데이터셋을 통해 선수 이름별로 올스타에 선정된 횟수를 하나의 테이블로 20행만 출력하시오. 단, 첫 번째 칼럼은 선수 이름을 출력하며 Last name과 First name이 결합된 Full name으로 출력하시오. 두 번째 칼럼은 첫 번째 칼럼의 선수 이름별 올스타에 선정된 횟수를 내림차순으로 출력하시오.

문제를 풀기에 앞서 연습용 데이터셋 내 테이블을 살펴보죠.

선수 이름(First/Last Name)은 People 테이블 (see Table 1 )에 playerID 별로 주어져 있습니다.

올스타에 선정된 선수 정보는 AllstarFull 테이블(see Table 2 )에 playerID 별로 주어져 있습니다.

따라서 하나의 테이블 형태로 선수 이름별 올스타 선정 횟수를 출력하기 위해서는 두 테이블을 playerID 를 기준으로 JOIN 해야 합니다.

 

Table 1. People 테이블의 일부

Table 2. AllstarFull 테이블의 일부

3.1.  소스코드

-- Q1) A U (A n B)
    SELECT
        -- 선수별 Full Name 구축 후 출력
        t1.nameLast || ' ' || t1.nameFirst AS playerName,
        -- 올스타에 선정된 횟수 카운팅
        COUNT(*) AS count
    -- 기준이 되는 테이블 t1: 선수 신상 정보를 담은 테이블
    FROM
        People t1
    
    -- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
    LEFT JOIN
        AllstarFull t2 
        -- 두 테이블에서 playerID가 일치하는 경우에만 데이터 출력
        ON t2.playerID = t1.playerID
    
    -- 선수 이름별로 테이블을 그룹화
    GROUP BY
        playerName
    -- 올스타 선정 횟수가 높은 순으로 나열
    ORDER BY
        count DESC
    -- 상위 20개의 데이터만 출력
    LIMIT 20;
    

위 전략을 바탕으로 소스코드를 작성해 보겠습니다.

먼저 People 테이블을 t1으로, AllstarFull 테이블을 t2로 치환하였으며 각 구문별 설명은 다음과 같습니다.

 

1) SELECT

- 선수 이름(Full name)과 올스타 선정 횟수를 각각 출력합니다.

- 선수이름은 Full Name으로 출력하기 위해 First name과 Last name을 nameFirst 및 nameLast 칼럼 데이터를 띄어쓰기 한 칸을 사이로 병합하였습니다(병합 방법은 아래 포스팅 참고).

heytech.tistory.com/28

[SQL] 서로 다른 칼럼의 데이터 병합하기!

2) FROM

- People 테이블(t1)을 기준 테이블로 설정하였습니다.

 

3) LEFT JOIN

- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정합니다.

- ON 키워드를 통해 어떤 공통 칼럼인 playerID 를 기준으로 JOIN 할 것인지 지정합니다.

 

4) GROUP BY

- 선수 ID별로 올스타 선정 횟수를 출력하기 위해 playerID 칼럼별로 테이블을 그룹핑해줍니다.

 

5) ORDER BY

- 올스타 선정 횟수가 많은 순으로(내림차순) 테이블을 정렬합니다.

- ORDER BY 구문의 자세한 내용은 아래 포스팅을 참고해 주세요.

heytech.tistory.com/22

[SQL] ORDER BY: 데이터 정렬하기(내림차순/오름차순)

6) LIMIT

- 상위 20개의 데이터만 출력할 것을 지정합니다.

3.2.  소스코드 실행 결과

Q1 출력 결과(데이터)

People(t1) 테이블에서 선수의 last name 또는 first name 값이 NULL 값인 행이 총 37개가 있었습니다.

이에 위와 같이 선수 이름의 Full name 칼럼에서 NULL 값으로 표기된 행이 37개라는 것을 확인할 수 있습니다.

Q1 출력 결과(도식화). 출처: (출처: https://commons.wikimedia.org/wiki/File:SQL_Joins.svg)

Q1 문제는 위 그림과 같이 좌측 테이블이 기준 테이블로 People(t1)이며 우측 테이블이 AllstarFull입니다.

즉, 올스타에 1번도 선정되지 않은 선수(교집합을 제외한 t1)와 올스타에 1번 이상 선정된 선수(두 테이블의 교집합)의 올스타 선정 횟수를 출력한 셈입니다.

3.3.  올스타에 선정/미선정 선수의 수

-- Q1 출력 결과(선수 명수)
    SELECT
        -- 올스타에 선정된 횟수 카운팅
        -- 동일인물의 중복 카운팅 방지
        COUNT(DISTINCT(t1.playerID)) AS cntLeftJoin_Allstar
    -- 기준이 되는 테이블 t1: 선수 신상 정보를 담은 테이블
    FROM
        People t1    
    -- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
    LEFT JOIN
        AllstarFull t2 
        -- 두 테이블에서 playerID가 일치하는 경우에만 데이터 출력
        ON t2.playerID = t1.playerID;

이러한 경우에 해당하는 선수를 구해보면 소스코드 및 결과는 아래와 같습니다.

Q1 출력결과(선수 명수)

3.4.  해석 시 주의사항

Q1에서는 올스타에 선정된 선수와 선정되지 않은 선수가 모두 포함되어 있다고 했습니다. 여기서 주의할 점은 올스타 테이블에 정보가 없는 선수의 올스타 선정 횟수가 1로 출력된다는 점입니다. 왜냐하면 해당 선수는 올스타 테이블과 JOIN 하면서 올스타 테이블 내 모든 칼럼에서 NULL 값을 부여받으며 하나의 행을 갖습니다. 결과적으로, 이를 따로 처리하지 않고 카운팅 하기 때문에 마치 1회라도 올스타에 선정된 것으로 해석될 수 있습니다. 특히, 실제로 올스타에 1회 선정된 선수 역시 선정 횟수가 1로 카운팅 됩니다. 따라서 이처럼 올스타에 선정된 적이 없는 선수는 올스타 선정 횟수를 임의적으로 0으로 초기화할 필요가 있습니다.

 

4.  예시 문제(Q2)

Q2.  주어진 연습용 데이터셋을 통해 올스타에 1번도 선정된 적이 없는 선수는 몇 명인지 출력하시오.

4.1.  소스코드

-- Q2) 차집합 (A-B)
    SELECT
        -- 올스타에 선정되지 않은 선수 카운팅
        -- 동일인물의 중복 카운팅 방지
        COUNT(*) AS count
    -- 기준이 되는 테이블 t1: 선수 신상 정보를 담은 테이블
    FROM
        People t1
    -- AllstarFull 테이블(t2)을 t1에 LEFT JOIN 할 것을 지정
    LEFT JOIN
        AllstarFull t2 
        -- 두 테이블에서 playerID가 일치하는 경우에만 데이터 출력
        ON t2.playerID = t1.playerID
	-- 올스타 선수 정보가 담긴 테이블(t2)에 기록이 없는 선수만 출력
    WHERE
    	t2.playerID is NULL;    

 

올스타에 선정된 적이 없는 선수를 출력하기 위해서는 Q1에서 t1과 t2를 t1을 기준으로 LEFT JOIN 한 것에서 t1과 t2 간의 교집합을 제외해야 합니다. 즉, 차집합(t1 - t2)을 구하면 됩니다. 아래와 같이 18,044명의 선수는 1번이라도 올스타에 선정된 적이 없는 것을 알 수 있습니다.

4.2.  소스코드 실행 결과(올스타에 선정된 적이 없는 선수의 수)

Q2 출력 결과(선수 명수)

4.3.  올스타에 1회 이상 선정된 선수의 수

만일 이전 포스팅에서 다룬 INNER JOIN으로 수행했다면 두 테이블의 교집합 중 올스타에 1회 이상 선정된 선수 정보만 출력될 것입니다.

이 점이 INNER JOIN과 LEFT(RIGHT) JOIN과의 차이점입니다. 실제로 INNER JOIN을 통해 올스타에 선정된 적이 있는 선수의 수를 구해보겠습니다.

-- JOIN
    SELECT
    -- 동일인물의 중복 카운팅 방지
        COUNT(DISTINCT(t1.playerID))AS cntInnerJoin
    FROM
        People t1
    JOIN
        AllstarFull t2 ON t2.playerID = t1.playerID;

아래와 같이 1,834명이 올스타에 선정된 적이 있는 것을 알 수 있습니다.

올스타에 1회 이상 선정된 선수의 수

 

이를 통해 올스타에 1회 이상 선정된 선수(1,834명)와 올스타에 1번도 선정되지 못 한 선수(18,044명)를 합하면 Q1 문제에서 구한 선수의  수(19,878명)와 같은 것을 확인할 수 있습니다. 이처럼 저희가 의도했던 대로 JOIN이 정상적으로 이루어진 것을 확인할 수 있었습니다.

마치며...

오늘은 JOIN의 두 번째 포스팅으로 LEFT JOIN에 대해 다루었습니다.

RIGHT JOIN은 기준이 되는 테이블이 달라질 뿐 사용방법이나 역할은 LEFT JOIN과 대동소이합니다.

특히 SQLite에서는 RIGHT JOIN 구문을 지원하지 않기 때문에 LEFT JOIN과 WHERE 구문을 사용해야 하는 번거로움이 있습니다.

SQLite에서는 RIGHT JOIN 구문 미지원

LEFT JOIN을 RIGHT JOIN 보다 자주 쓰는 경향이 있다는 점에서도 LEFT JOIN을 다루는 법을 선행적으로 익히시는 것을 추천드립니다.

그럼 다음 포스팅에서는 JOIN의 세 번째 포스팅으로 OUTTER JOIN에 대해 다루도록 하겠습니다.

참고할 만한 포스팅

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

---

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

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

고맙습니다.