조인과 집계 - 여러 테이블을 묶어 묻기

이 장에서 배우는 것

앞 장에서는 book 한 테이블만 놓고 SELECT와 WHERE, ORDER BY로 원하는 행을 골라내는 법을 다뤘다. 그런데 실제 질문은 한 테이블 안에서 끝나지 않는다. "이 도서를 쓴 저자는 누구인가", "한 번도 팔리지 않은 책은 무엇인가", "대분류와 소분류를 함께 보여 달라", "등급별로 매출이 얼마나 다른가" 같은 질문에 답하려면 여러 테이블을 엮고, 엮은 결과를 묶어서 세고 더해야 한다. 이 장은 그 두 가지 도구, 조인(join)과 집계(aggregation)를 다룬다.

• 내부 조인(INNER JOIN)과 외부 조인(LEFT OUTER JOIN)의 차이를 행 단위로 설명할 수 있다
• LEFT JOIN과 IS NULL을 조합해 "한 번도 팔리지 않은 도서"처럼 없는 것을 찾을 수 있다
• 같은 테이블을 두 번 참조하는 셀프 조인으로 category의 2단계 계층을 한 행에 펼칠 수 있다
• GROUP BY와 HAVING으로 그룹별 집계와 그 집계에 대한 조건을 구분해서 쓸 수 있다
• COUNT(*)와 COUNT(컬럼)의 차이, 집계 함수가 NULL을 다루는 방식을 설명할 수 있다

문제 상황

SQL 연구소의 온라인 서점 운영팀에서 세 가지 요청이 들어왔다고 하자. 첫째, 재고 담당자가 "지금까지 한 번도 주문된 적 없는 도서 목록"을 요청했다. book 테이블만 봐서는 어떤 책이 팔렸는지 알 수 없다. 주문 내역은 order_item에 있고, book과 order_item을 엮어야 하는데 심지어 "엮이지 않는" 책을 찾아야 한다. 둘째, MD(상품기획자)가 "도서 목록에 대분류와 소분류 이름을 나란히" 보여 달라고 했다. category 테이블은 parent_id로 자기 자신을 가리키는 2단계 계층이라, category 하나만 조회해서는 상위 분류 이름이 안 보인다. 셋째, 마케팅팀이 "회원 등급(grade)별로 이번 분기 매출과 주문 건수"를 알고 싶어 한다. 이건 member, orders, order_item 세 테이블을 엮은 뒤 등급별로 묶어서 더해야 하는 일이다.

세 요청 모두 앞 장에서 배운 단일 테이블 SELECT로는 풀리지 않는다. 이 장에서 다루는 조인과 GROUP BY가 이 세 가지 요청에 정확히 대응한다.

내부 조인과 외부 조인 - 팔린 적 없는 도서 찾기

조인은 두 테이블의 행을 공통 키로 짝지어 하나의 결과 행으로 합치는 연산이다. 온라인 서점 스키마에서는 book.id와 order_item.book_id처럼 한쪽 테이블의 값이 다른 쪽 테이블의 값을 가리키는 관계가 곳곳에 있고, 조인은 그 관계를 따라간다.

내부 조인 - 짝이 있는 행만 남긴다

INNER JOIN은 두 테이블에서 조인 조건을 만족하는 행만 결과에 남긴다. book과 order_item을 INNER JOIN하면 "실제로 한 번이라도 주문에 포함된 도서"만 남고, 주문 내역이 하나도 없는 도서는 결과에서 통째로 빠진다.

LEFT JOIN - 짝이 없어도 남긴다

LEFT OUTER JOIN(줄여서 LEFT JOIN)은 왼쪽 테이블(FROM 뒤에 먼저 쓴 테이블)의 행을 하나도 빠뜨리지 않는다. 오른쪽 테이블에서 짝이 없으면 오른쪽 테이블의 모든 열을 NULL로 채워서라도 왼쪽 행을 살려 둔다. 그래서 "book은 있는데 order_item 쪽 book_id가 NULL인 행"이 바로 한 번도 팔리지 않은 도서다. WHERE 절에서 그 NULL을 걸러내면 답이 나온다.

INNER JOIN과 LEFT JOIN이 짝 없는 행을 다루는 방식

조인 종류	표기	짝이 없는 행 처리	이 장에서 쓰는 예
내부 조인	INNER JOIN	결과에서 제외한다	실제로 팔린 도서와 저자 정보를 함께 조회
왼쪽 외부 조인	LEFT JOIN	왼쪽 행을 남기고 오른쪽 열을 NULL로 채운다	한 번도 팔리지 않은 도서 찾기

SQLite는 3.39 버전부터 RIGHT JOIN과 FULL JOIN도 지원하지만, "왼쪽 테이블 기준으로 다 살린다"는 LEFT JOIN 하나만으로도 이 장의 질문은 모두 풀린다. 굳이 오른쪽 기준이 필요하면 두 테이블의 순서를 바꿔 쓰면 된다.

셀프 조인 - 분류 계층을 한 행에 펼치기

category 테이블은 parent_id 열로 자기 자신을 가리킨다. 대분류(예: 소설, 경제경영)는 parent_id가 NULL이고, 소분류(예: 한국소설, 재테크)는 parent_id에 대분류의 id를 담는다. 이 계층을 한 행으로 펼치려면 category 테이블을 서로 다른 별칭으로 두 번 조인해야 한다. 자식 역할을 하는 별칭(c)의 parent_id를, 부모 역할을 하는 별칭(p)의 id와 맞추는 방식이다.

이때 INNER JOIN을 쓰면 parent_id가 NULL인 대분류 행 자체가 통째로 빠진다. 대분류도 목록에 남기려면 LEFT JOIN을 써서 "부모가 없으면 parent_name을 NULL로 두고 자식 행은 살린다"는 규칙을 적용해야 한다.

GROUP BY, HAVING, 집계와 NULL

조인으로 필요한 열을 다 모았다면, 이제 그 행들을 그룹으로 묶어 세거나 더할 차례다. GROUP BY 뒤에 쓴 열의 값이 같은 행끼리 한 그룹이 되고, SELECT에는 그 그룹을 요약하는 집계 함수(COUNT, SUM, AVG, MAX, MIN)만 쓸 수 있다. 그룹 자체를 거르는 조건은 WHERE가 아니라 HAVING에 쓴다. WHERE는 그룹으로 묶기 전에 행을 거르고, HAVING은 그룹으로 묶은 뒤 집계 결과를 거른다는 차이가 있다.

집계 함수는 NULL을 셀 때와 세지 않을 때가 갈린다. COUNT(*)는 그 그룹에 속한 행의 개수를 그대로 센다. 반면 COUNT(컬럼명)은 그 컬럼 값이 NULL이 아닌 행만 센다. LEFT JOIN 뒤에 오른쪽 테이블 열이 NULL로 채워진 행이 섞여 있으면 두 함수의 결과가 달라진다. SUM과 AVG도 NULL인 값은 계산에서 아예 빼고 나머지만으로 더하거나 평균 낸다는 점은 같다. member.region처럼 값이 없을 수 있는 열을 GROUP BY에 쓰면 region이 NULL인 회원끼리 별도의 한 그룹으로 묶인다는 점도 기억해 둘 만하다.

표준 SQL과 SQLite의 GROUP BY·HAVING 문법은 동일하다. 세부 규칙은 SQLite SELECT 문법 문서에서 확인할 수 있다.

완성 코드

-- 1) 한 번도 주문된 적 없는 도서 찾기 (LEFT JOIN + IS NULL)
SELECT b.id, b.title, b.published_on
FROM book AS b
LEFT JOIN order_item AS oi ON oi.book_id = b.id
WHERE oi.book_id IS NULL
ORDER BY b.id;

-- 2) 분류 계층을 한 행으로 펼치기 (셀프 조인)
SELECT c.name AS category_name, p.name AS parent_name
FROM category AS c
LEFT JOIN category AS p ON c.parent_id = p.id
ORDER BY p.name, c.name;

-- 3) 분류별 판매 건수, 5건 이상인 분류만 (GROUP BY + HAVING)
SELECT c.name AS category_name, COUNT(*) AS sold_count
FROM order_item AS oi
JOIN book AS b ON b.id = oi.book_id
JOIN category AS c ON c.id = b.category_id
JOIN orders AS o ON o.id = oi.order_id
WHERE o.status != 'CANCELLED' AND o.status != 'REFUNDED'
GROUP BY c.name
HAVING COUNT(*) >= 5
ORDER BY sold_count DESC;

-- 4) 회원 등급별 매출과 주문 건수
SELECT m.grade,
       SUM(oi.qty * oi.unit_price) AS revenue,
       COUNT(DISTINCT o.id) AS order_count
FROM member AS m
JOIN orders AS o ON o.member_id = m.id
JOIN order_item AS oi ON oi.order_id = o.id
WHERE o.status != 'CANCELLED' AND o.status != 'REFUNDED'
GROUP BY m.grade
ORDER BY revenue DESC;

줄별 해설

1번 쿼리는 LEFT JOIN이 남긴 book 행 중에서 oi.book_id가 NULL인 행만 WHERE로 걸러낸다. book.id를 기준으로 조인했기 때문에, order_item 쪽에 짝이 없는 book 행은 oi의 모든 열이 NULL로 채워진 채 살아남는다. 조인 조건에 쓴 book.id 자체가 아니라 짝짓기에 실제로 쓰인 oi.book_id로 NULL을 검사해야 안전하다.

2번 쿼리는 category를 c와 p라는 두 별칭으로 두 번 등장시킨다. ON 절의 c.parent_id = p.id가 자식의 parent_id 값과 부모의 id 값을 맞춘다. LEFT JOIN을 썼기 때문에 parent_id가 NULL인 대분류 행도 parent_name이 NULL인 채로 결과에 남는다.

3번 쿼리는 order_item에서 시작해 book, category, orders까지 세 번 INNER JOIN한 뒤 category_name으로 묶는다. WHERE는 취소·환불된 주문을 판매 집계에서 미리 빼는 역할이고, GROUP BY 이후에 적용되는 HAVING은 그렇게 묶인 그룹 중 판매 건수가 5건 이상인 것만 남긴다. WHERE 자리에 COUNT(*) 조건을 쓰면 오류가 나는데, WHERE가 실행되는 시점에는 아직 그룹도, 집계값도 존재하지 않기 때문이다.

4번 쿼리는 member, orders, order_item을 순서대로 조인한 뒤 grade로 묶는다. SUM(oi.qty * oi.unit_price)는 주문 항목 단위의 금액을 등급별로 모두 더한 값이고, COUNT(DISTINCT o.id)는 order_item이 여러 줄로 갈라진 주문이라도 주문 건수는 한 번만 세도록 DISTINCT를 붙였다.

실행 결과

아래 결과는 SQL 연구소 데이터의 값에 따라 달라질 수 있는 예시 결과다.

sqlite> -- 1) 한 번도 주문된 적 없는 도서
id  | title          | published_on
87  | 오래된 이야기   | 2019-03-11
145 | 통계의 첫걸음   | 2021-07-02

sqlite> -- 2) 분류 계층
category_name | parent_name
경제경영       | NULL
소설           | NULL
재테크         | 경제경영
외국소설       | 소설
한국소설       | 소설

sqlite> -- 3) 분류별 판매 건수 (5건 이상)
category_name | sold_count
한국소설       | 32
IT전문서       | 18
경제경영       | 5

sqlite> -- 4) 등급별 매출
grade  | revenue  | order_count
VIP    | 12450000 | 340
GOLD   | 9820000  | 305
SILVER | 6150000  | 210
BASIC  | 3220000  | 150

실무에서 자주 틀리는 것

LEFT JOIN인데 WHERE 때문에 다시 INNER JOIN이 되는 경우

LEFT JOIN 뒤에 오른쪽 테이블 열을 WHERE에 그대로 쓰면, 짝이 없어서 NULL로 채워진 행은 그 조건을 통과하지 못해 결국 사라진다. 오른쪽 테이블에 대한 조건은 ON 절에 넣어야 LEFT JOIN의 의미가 유지된다.

-- 틀린 코드: 결과적으로 INNER JOIN과 같아진다
SELECT b.title
FROM book AS b
LEFT JOIN order_item AS oi ON oi.book_id = b.id
WHERE oi.qty > 0;

-- 고친 코드: 오른쪽 테이블 조건은 ON에 둔다
SELECT b.title
FROM book AS b
LEFT JOIN order_item AS oi ON oi.book_id = b.id AND oi.qty > 0;

GROUP BY에 없는 열을 SELECT에 넣는 경우

GROUP BY로 묶은 열이 아닌 일반 열을 SELECT에 그대로 쓰면, 그룹 안에 값이 여러 개일 때 어느 행의 값이 나올지 정해져 있지 않다. SQLite는 오류 없이 실행되지만 결과가 실행할 때마다 달라질 수 있다.

-- 틀린 코드: b.title이 그룹당 여러 개일 수 있다
SELECT c.name, b.title, COUNT(*) AS sold_count
FROM order_item AS oi
JOIN book AS b ON b.id = oi.book_id
JOIN category AS c ON c.id = b.category_id
GROUP BY c.name;

-- 고친 코드: 제목까지 보려면 제목도 그룹 기준에 넣는다
SELECT c.name, b.title, COUNT(*) AS sold_count
FROM order_item AS oi
JOIN book AS b ON b.id = oi.book_id
JOIN category AS c ON c.id = b.category_id
GROUP BY c.name, b.title;

COUNT(*)와 COUNT(컬럼)을 구분하지 않는 경우

LEFT JOIN으로 리뷰가 없는 도서까지 살려 둔 상태에서 COUNT(*)를 쓰면, 리뷰가 하나도 없는 도서도 review 쪽 열이 전부 NULL인 행 하나가 이미 존재하므로 리뷰 개수가 1로 잘못 집계된다. NULL이 아닌 값만 세는 COUNT(컬럼)을 써야 실제 리뷰 개수와 일치한다.

-- 틀린 코드: 리뷰 없는 도서도 1건으로 잡힌다
SELECT b.title, COUNT(*) AS review_count
FROM book AS b
LEFT JOIN review AS r ON r.book_id = b.id
GROUP BY b.title;

-- 고친 코드: r.id가 NULL인 행은 세지 않는다
SELECT b.title, COUNT(r.id) AS review_count
FROM book AS b
LEFT JOIN review AS r ON r.book_id = b.id
GROUP BY b.title;

WHERE 자리에 집계 조건을 쓰는 경우

WHERE는 행 단위 조건, HAVING은 그룹 단위 집계 조건이다. WHERE에 COUNT나 SUM 같은 집계 함수를 쓰면 오류가 난다.

-- 틀린 코드: WHERE 단계에는 아직 집계값이 없다
SELECT c.name, COUNT(*) AS sold_count
FROM order_item AS oi
JOIN book AS b ON b.id = oi.book_id
JOIN category AS c ON c.id = b.category_id
WHERE COUNT(*) >= 5
GROUP BY c.name;

-- 고친 코드: 집계 후 조건은 HAVING
SELECT c.name, COUNT(*) AS sold_count
FROM order_item AS oi
JOIN book AS b ON b.id = oi.book_id
JOIN category AS c ON c.id = b.category_id
GROUP BY c.name
HAVING COUNT(*) >= 5;

한눈에 보기

이 장에서 쓴 구문과 주의할 점

구문	의미	주의할 점
INNER JOIN	양쪽 다 짝이 있는 행만 남긴다	짝 없는 행은 소리 없이 사라진다
LEFT JOIN	왼쪽 행은 다 남기고 짝 없으면 NULL로 채운다	WHERE에 오른쪽 열 조건을 쓰면 도로 INNER JOIN이 된다
셀프 조인	같은 테이블을 다른 별칭으로 두 번 참조한다	부모 행까지 살리려면 LEFT JOIN이 필요하다
GROUP BY / HAVING	그룹으로 묶고, 묶인 뒤의 집계값으로 다시 거른다	WHERE는 묶기 전, HAVING은 묶은 뒤라는 순서를 지킨다
COUNT(*) vs COUNT(컬럼)	행 개수 전체 vs NULL이 아닌 값의 개수	LEFT JOIN 뒤에는 둘의 결과가 달라질 수 있다

SQL 연구소에서 실습하기

다음 과제를 SQL 연구소의 '온라인 서점' 데이터로 직접 실행해 본다.

1. publisher와 book을 조인해서, 출판사별로 낸 도서 중 pages 값이 있는 도서의 평균 쪽수를 구해 본다.
2. book_author와 author를 조인해서, 한 도서에 저자(AUTHOR)와 번역자(TRANSLATOR)가 모두 등록된 도서의 제목만 골라 본다.
3. inventory와 book을 LEFT JOIN해서, 재고(stock)가 0이거나 아예 inventory 행이 없는 도서를 함께 찾아본다.

연습 문제

1. book_author와 author를 내부 조인해서, country가 'KR'이 아닌 저자가 쓴 도서의 제목, 저자 이름, country를 조회하는 쿼리를 작성하라.
2. category를 셀프 조인해서, 상위 분류가 없는(대분류인) 카테고리의 이름만 나열하는 쿼리를 작성하라.
3. orders, order_item, book, publisher를 조인하고 GROUP BY로 출판사별 총 판매 수량(qty의 합)을 구하되, 취소·환불 주문은 빼고 합이 100 미만인 출판사는 제외하는 쿼리를 작성하라.
4. 회원 등급별 평균 주문 금액(주문 1건당 금액)을 구하는 쿼리를 작성하고, order_item을 그대로 GROUP BY에 넣어 AVG를 구하면 왜 틀린 값이 나오는지 한 문장으로 설명하라.

정답과 해설

1. SELECT b.title, a.name AS author_name, a.country
   FROM book AS b
   JOIN book_author AS ba ON ba.book_id = b.id
   JOIN author AS a ON a.id = ba.author_id
   WHERE a.country != 'KR';
   

   세 테이블을 book_author를 다리 삼아 내부 조인한다. 국내 저자만 제외하면 되므로 country 조건은 WHERE에 둔다.

2. SELECT c.name
   FROM category AS c
   LEFT JOIN category AS p ON c.parent_id = p.id
   WHERE p.id IS NULL;
   

   자기 자신을 셀프 조인한 뒤, 부모 쪽 짝이 없는(p.id가 NULL인) 행만 남기면 parent_id가 NULL인 대분류만 남는다. c.parent_id IS NULL로 직접 걸러도 같은 결과지만, 셀프 조인 결과로 확인하는 연습이라는 점에서 이 방식을 썼다.

3. SELECT p.name AS publisher_name, SUM(oi.qty) AS total_qty
   FROM order_item AS oi
   JOIN orders AS o ON o.id = oi.order_id
   JOIN book AS b ON b.id = oi.book_id
   JOIN publisher AS p ON p.id = b.publisher_id
   WHERE o.status != 'CANCELLED' AND o.status != 'REFUNDED'
   GROUP BY p.name
   HAVING SUM(oi.qty) >= 100
   ORDER BY total_qty DESC;
   

   취소·환불 여부는 묶기 전에 걸러야 하므로 WHERE에, 합계 100 이상이라는 조건은 묶은 뒤의 값이므로 HAVING에 둔다.

4. SELECT m.grade, AVG(order_amount) AS avg_order_amount
   FROM member AS m
   JOIN orders AS o ON o.member_id = m.id
   JOIN (
       SELECT order_id, SUM(qty * unit_price) AS order_amount
       FROM order_item
       GROUP BY order_id
   ) AS oa ON oa.order_id = o.id
   WHERE o.status != 'CANCELLED' AND o.status != 'REFUNDED'
   GROUP BY m.grade;
   

   order_item은 한 주문이 여러 줄로 나뉘어 있어서, order_item 행을 그대로 AVG에 넣으면 주문 한 건이 아니라 주문 항목(줄) 한 개를 기준으로 평균을 내게 되어 실제 "주문 1건당 금액"과 다른 값이 나온다. 먼저 order_id 단위로 합산해 주문별 금액을 만든 뒤 그 값을 등급별로 평균 내야 한다.