조인과 집계 - 여러 테이블을 묶어 묻기
이 장에서 배우는 것
앞 장에서는 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 | 왼쪽 행을 남기고 오른쪽 열을 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 연구소의 '온라인 서점' 데이터로 직접 실행해 본다.
- publisher와 book을 조인해서, 출판사별로 낸 도서 중 pages 값이 있는 도서의 평균 쪽수를 구해 본다.
- book_author와 author를 조인해서, 한 도서에 저자(AUTHOR)와 번역자(TRANSLATOR)가 모두 등록된 도서의 제목만 골라 본다.
- inventory와 book을 LEFT JOIN해서, 재고(stock)가 0이거나 아예 inventory 행이 없는 도서를 함께 찾아본다.
연습 문제
- book_author와 author를 내부 조인해서, country가 'KR'이 아닌 저자가 쓴 도서의 제목, 저자 이름, country를 조회하는 쿼리를 작성하라.
- category를 셀프 조인해서, 상위 분류가 없는(대분류인) 카테고리의 이름만 나열하는 쿼리를 작성하라.
- orders, order_item, book, publisher를 조인하고 GROUP BY로 출판사별 총 판매 수량(qty의 합)을 구하되, 취소·환불 주문은 빼고 합이 100 미만인 출판사는 제외하는 쿼리를 작성하라.
- 회원 등급별 평균 주문 금액(주문 1건당 금액)을 구하는 쿼리를 작성하고, order_item을 그대로 GROUP BY에 넣어 AVG를 구하면 왜 틀린 값이 나오는지 한 문장으로 설명하라.
정답과 해설
-
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에 둔다.
-
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로 직접 걸러도 같은 결과지만, 셀프 조인 결과로 확인하는 연습이라는 점에서 이 방식을 썼다.
-
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에 둔다.
-
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 단위로 합산해 주문별 금액을 만든 뒤 그 값을 등급별로 평균 내야 한다.