테이블 간 결합(INNER JOIN, LEFT JOIN)과 결과 조합(Union)

들어가며

JOIN이란 공통된 필드를 갖고 있는 여러 테이블을 하나의 테이블처럼 보이도록 연결하는 것이다. 이는 엑셀에서 VLOOKUP 함수와 같은 개념이라고 할 수 있다. JOIN의 종류에는 INNER JOIN, LEFT JOIN, OUTER JOIN이 있는데, 강의에서는 INNER JOIN과 LEFT JOIN에 대해서만 다루었다(OUTER JOIN은 실제로 잘 사용하지 않는다고 한다).

INNER JOIN

INNER JOIN은 기준이 되는 필드에 데이가 없으면 아예 해당 행 자체를 보여주지 않는다. 즉, 서로 다른 테이블 간의 교집합으로 테이블을 결합하며, 가장 일반적으로 사용되는 방식이다. 예를 들어, users 테이블과 point_users 테이블을 INNER JOIN으로 결합하는 쿼리문은 다음과 같다.

SELECT * FROM users u
INNER JOIN point_users pu
    ON u.user_id = pu.user_id;

즉, JOIN 뒤에는 결합하려는 테이블명을 입력하고, 어떠한 필드를 기준으로 결합할 것인지는 ON 뒤에 입력한다.

실습

❓ 과목별 '오늘의 다짐' 수량 파악하기

SELECT c1.course_id, c2.title, COUNT(*) as cnt 
    FROM checkins c1
INNER JOIN courses c2
    ON c1.course_id = c2.course_id
GROUP BY c1.course_id;

❓ 유저의 이름을 포인트가 높은 순서대로 정렬하기

SELECT u.name, pu.point FROM point_users pu
INNER JOIN users u 
    ON pu.user_id = u.user_id
ORDER BY pu.point DESC; 

❓ 네이버 이메일을 사용하는 유저의 성씨별 주문 건수 파악하기

SELECT u.name, COUNT(*) as cnt FROM orders o
INNER JOIN users u 
    ON o.user_id = u.user_id
WHERE u.email LIKE '%naver%'
GROUP BY u.name;

위와 같이 쿼리문이 복잡하게 느껴진다면, 저번 글에 남긴 복잡한 쿼리문 작성 요령을 한 번 떠올려보자. 그 요령대로 위의 문제를 해결해보면, 1) orders 테이블과 users 테이블 결합, 2) 결합된 테이블에서 네이버 이메일을 사용하는 데이터만 필터링, 3) 가공된 테이블에서 성씨별 주문 건수 파악, 4) 최종 결과 출력 순으로 쿼리문을 수정하면서 작성할 수 있다.

❓ 결제 수단 별 유저 포인트의 평균값 파악하기

SELECT o.payment_method, ROUND(AVG(pu.point), 2) 
    FROM orders o
INNER JOIN point_users pu 
    ON o.user_id = pu.user_id
GROUP BY o.payment_method;

❓ 결제 후 강의를 시청하지 않은 유저가 몇 명인지 성씨별 많은 순으로 파악하기

SELECT u.name, COUNT(*) as cnt FROM enrolleds e
INNER JOIN users u 
    ON e.user_id = u.user_id 
WHERE e.is_registered = 0
GROUP BY u.name
ORDER BY cnt DESC;

❓ 결제 후 강의를 시청하지 않은 유저가 몇 명인지 과목별 많은 순으로 파악하기

SELECT c.course_id, c.title, COUNT(*) as cnt 
    FROM courses c
INNER JOIN enrolleds e 
    ON c.course_id = e.course_id
WHERE e.is_registered = 0
GROUP BY c.course_id
ORDER BY cnt DESC;

❓ 웹개발, 앱개발 종합반의 체크인 수를 주차별 높은 순으로 파악하기

SELECT c1.title, c2.week, COUNT(*) as cnt 
    FROM courses c1
INNER JOIN checkins c2
    ON c1.course_id = c2.course_id
GROUP BY c1.user_id, c2.week
ORDER BY c1.title ASC, c2.week ASC, cnt DESC;

위의 쿼리문에서 볼 수 있듯이 GROUP BY, ORDER BY의 기준이 여러 개인 경우 콤마로 나누어 입력하면 된다.

❓ 2021년 8월 1일 이후에 결제한 유저의 웹개발, 앱개발 종합반의 체크인 수를 주차별 높은 순으로 파악하기

SELECT c1.course_id, c1.title, COUNT(*) as cnt
    FROM couses c1
INNER JOIN checkins c2
    ON c1.course_id = c2.course_id
INNER JOIN orders o
    ON c2.user_id = o.user_id
WHERE o.created_at >= '2020-08-01'
GROUP BY c1.course_id, c2.week
ORDER BY c1.title ASC, c2.week ASC, cnt DESC;

LEFT JOIN

LEFT JOIN은 X 필드를 기준으로 A 테이블에다가 B 테이블을 결합하되, 데이터가 없는 경우 NULL로 채우는 방식이다. 즉, LEFT JOIN은 교집합이 아니라 기준이 되는 테이블에 다른 테이블을 일방적으로 붙이는 방식이다. 따라서, 어느 테이블에 어떠한 테이블을 붙일 것인지 그 순서가 매우 중요하며, 주로 결합되는 테이블이 기준이 되는 테이블의 데이터를 갖고 있지 않을 때 사용된다. LEFT JOIN의 사용 방법은 INNER JOIN과 같으며, users 테이블에 point_users 테이블을 결합하는 쿼리문을 나타내면 다음과 같다.

SELECT * FROM users u
LEFT JOIN point_users pu
    ON u.user_id = pu.user_id;

한쪽 테이블에 다른 테이블을 일방적으로 붙이는 방식이다보니, 데이터가 없으면 NULL로 채우며, NULL 값은 COUNT 등과 같이 통계 수치에 영향을 받지 않는다. 예를 들어, 두 개의 테이블을 LEFT JOIN으로 결합 후 COUNT로 수량을 파악하는 경우 NULL 값은 카운트되지 않으니 유의하도록 하자.

실습

❓ 2020년 07월 10일부터 19일 사이에 가입한 유저 중에서 포인트가 있는 유저의 수, 전체 유저의 수, 이 둘의 비율 파악하기

SELECT COUNT(pu.point_user_id) as pnt_user_cnt, 
        COUNT(u.user_id) as total_user_cnt, 
        ROUND(COUNT(pu.point_user_id) / COUNT(u.user_id), 2) as ratio
    FROM users u 
LEFT JOIN point_users pu 
    ON u.user_id = pu.user_id 
WHERE u.created_at BETWEEN "2020-07-10" AND "2020-07-20";

❓ enrolled_id별 수강완료(done=1)한 강의 갯수를 파악하고, 완료한 강의 수가 많은 순서대로 정렬하되 user_id도 같이 출력하기

SELECT e1.enrolled_id, e1.user_id, COUNT(*) as cnt 
    FROM enrolleds e1
LEFT JOIN enrolleds_detail e2
    ON e1.enrolled_id = e2.enrolled_id
WHERE e2.done = 1
GROUP BY e1.enrolled_id, e1.user_id
ORDER BY cnt DESC;

Union

Union은 필드가 같은 서로 다른 두 개의 결과를 하나의 결과로 이어서 붙여주는 기능이다. 예를 들어, 앱개발, 웹개발 종합반의 7월달, 8월달 주차별 주문 건수를 파악하는 과정을 살펴보도록 하자. 먼저, 앱개발, 웹개발 종합반의 7월달 주차별 주문 건수를 파악하는 쿼리문은 다음과 같이 작성할 수 있다.

SELECT c1.title, c2.week, COUNT(*) 
    FROM courses c1
INNER JOIN checkins c2
    ON c1.course_id = c2.course_id
INNER JOIN orders o 
    ON c2.user_id = o.user_id
WHERE o.created_at BETWEEN "2020-07-01" AND "2020-08-01"
GROUP BY c1.title, c2.week
ORDER BY c1.title, c2.week;

아래 쿼리문과 같이 작성하면 현재 구한 결과 값이 7월달 결과라는 것을 나타낼 수 있다.

SELECT "7월" as month, c1.title, c2.week, COUNT(*) 
    FROM courses c1
INNER JOIN checkins c2
    ON c1.course_id = c2.course_id
INNER JOIN orders o 
    ON c2.user_id = o.user_id
WHERE o.created_at BETWEEN "2020-07-01" AND "2020-08-01"
GROUP BY c1.title, c2.week
ORDER BY c1.title, c2.week;

마찬가지로 아래와 같이 8월달의 결과까지 이어서 작성해보자.

SELECT "8월" as month, c1.title, c2.week, COUNT(*) 
    FROM courses c1
INNER JOIN checkins c2
    ON c1.course_id = c2.course_id
INNER JOIN orders o 
    ON c2.user_id = o.user_id
WHERE o.created_at BETWEEN "2020-08-01" AND "2020-09-01"
GROUP BY c1.title, c2.week
ORDER BY c1.title, c2.week;

위에서 작성한 두 개의 쿼리문은 따로 실행이 되는데, 다음과 같이 Union을 사용하면 한 번에 출력할 수 있다.

(
    SELECT "7월" as month, c1.title, c2.week, COUNT(*) 
        FROM courses c1
    INNER JOIN checkins c2
        ON c1.course_id = c2.course_id
    INNER JOIN orders o 
        ON c2.user_id = o.user_id
    WHERE o.created_at BETWEEN "2020-07-01" AND "2020-08-01"
    GROUP BY c1.title, c2.week
    ORDER BY c1.title, c2.week
)
UNION ALL 
(
    SELECT "8월" as month, c1.title, c2.week, COUNT(*) 
        FROM courses c1
    INNER JOIN checkins c2
        ON c1.course_id = c2.course_id
    INNER JOIN orders o 
        ON c2.user_id = o.user_id
    WHERE o.created_at BETWEEN "2020-08-01" AND "2020-09-01"
    GROUP BY c1.title, c2.week
    ORDER BY c1.title, c2.week
);

결과를 보면 알 수 있겠지만, Union으로 출력한 결과에는 ORDER BY가 적용되지 않는다. 그 이유는 이미 정렬된 두 개의 테이블을 하나의 테이블로 합치는 과정에서 정렬이 풀리기 때문이다. 결과 테이블을 정렬할 때에는 각각의 쿼리문에 정렬하는 것이 아니라 Union으로 합쳐진 이후 정렬을 해주어야 한다. 그러기 위해서는 Sub Query라는 개념을 알아야 하는데, 이는 4주차에서 따로 다루기 때문에 4주차 개발일지에 기록하도록 하겠다.

마치며

OUTER INNER에 대해서는 따로 공부해야 하겠지만 학교 수업을 들었을 때(일하느라 집중해서 듣지는 못했다) 헷갈렸던 JOIN에 대해서 거의 완벽하게 이해하게 된 것 같다. 내일이면 SQL 4주차 강의는 모두 수강할 수 있을텐데, 정말 약 4일만에 굉장히 효율적으로 학습한 것 같다.