조인과 집계
85분 안팎
학습 목표
조인 전후 행 수와 NULL을 고려해 대여 목록을 조회합니다.
개념
왜 회원을 기준으로 조회하나요
담당자는 모든 회원의 현재 대여 수를 보고 싶어 합니다. 한 번도 빌리지 않은 회원과 모두 반납한 회원도 0으로 보여야 합니다. loans만 읽으면 이 회원들이 존재하지 않는 것처럼 보입니다. 이번 조회의 기준 집합은 members 전체이고 대여는 선택적으로 붙는 정보입니다. “회원당 한 행”이라는 결과 단위를 먼저 정해야 조인 후 행 수가 맞는지 판단할 수 있습니다.
파일 API의 GET /books와 이 회원 집계는 서로 다른 계약입니다. SQL을 작성했다고 회원 HTTP 경로를 새로 만들지 않습니다. 브라우저 SQLite에서 결과표를 비교하고 미션 H2 JDBC 테스트에서 같은 기대를 확인합니다. 연결이나 컨트롤러를 바꾸는 작업은 다음 모듈입니다. 이번에는 키 관계를 이용해 사실을 읽고 0과 NULL을 구별하는 연습을 합니다.
조인의 행 수를 손으로 예상합니다
members m JOIN loans l ON l.member_id=m.id는 대여 기록과 짝이 있는 회원만 반환합니다. 회원 1이 대여 두 건이면 그 회원 정보가 두 행으로 나옵니다. 조인은 부모 한 행을 한 번만 보여 주는 기능이 아닙니다. 부모와 매칭되는 자식의 수만큼 조합 행이 생기므로 COUNT 전에 몇 행이 나올지 작은 예제로 적어 봅니다.
대여 상세에서 도서 제목을 붙이려면 loans.book_id=books.id 조건을 씁니다. member_id와 book_id는 둘 다 정수이지만 의미가 다릅니다. 숫자 값이 우연히 같다고 회원과 도서가 관계있는 것은 아닙니다. 별칭 m, l, b를 쓰고 각 ON 조건을 FK와 부모 PK로 읽어 봅니다. ON을 빠뜨려 카티션 곱이 나오면 숫자만 커지는 것이 아니라 잘못된 회원·도서 조합을 보고할 수 있습니다.
LEFT JOIN은 짝 없는 부모도 남깁니다
members를 왼쪽에 놓고 LEFT JOIN하면 대여가 없는 회원도 한 행을 남기며 l.id와 l.returned_at 등 오른쪽 값은 NULL입니다. 이를 실제 NULL 반환 시각의 대여 행과 구별할 때는 자식 PK인 l.id를 봅니다. 실제 대여는 l.id가 있고 미매칭 행은 l.id도 NULL입니다. 따라서 조회한 NULL이 모두 “미반납 사건”이라고 해석하면 틀립니다.
현재 대여만 붙이려면 ON에 l.member_id=m.id AND l.returned_at IS NULL을 둡니다. 반납 이력은 매칭 후보에서 빠지지만 회원은 남습니다. WHERE로 반납 조건을 이동하면 모두 반납한 회원에게 붙었던 과거 행들이 제거되며 0을 출력할 행 자체가 없어질 수 있습니다. 대여가 전혀 없는 회원과 반납 이력만 있는 회원을 둘 다 시험해야 이 오류를 잡습니다.
COUNT의 대상을 결과 단위에 맞춥니다
COUNT(*)는 조인 후 행을 셉니다. LEFT JOIN의 미매칭 부모도 한 행이므로 대여 0건 회원에게 1이 나올 수 있습니다. COUNT(l.id)는 NULL을 세지 않아 실제 매칭 대여만 집계합니다. returned_at을 세면 미반납 행은 NULL이어서 현재 대여 수가 0이 됩니다. 어떤 컬럼을 셀지 업무의 사건 식별자로 결정합니다.
GROUP BY m.id,m.name으로 회원별 묶음을 만듭니다. 동명이인이 있어도 ID가 달라 분리됩니다. 이름만으로 묶으면 두 민지 회원의 대여가 한 사람 것으로 합쳐집니다. SELECT하는 비집계 이름도 GROUP BY에 함께 적어 SQLite의 느슨한 동작에 기대지 않는 쿼리를 씁니다. 마지막 ORDER BY m.id로 출력 순서를 고정해 테스트 비교를 안정적으로 만듭니다.
여러 자식 테이블은 집계를 부풀릴 수 있습니다
회원별 대여와 알림을 동시에 조인한다고 생각해 봅니다. 대여 2건·알림 3건이면 두 자식을 직접 붙여 최대 6개의 조합 행을 만들 수 있습니다. 대여 수가 6으로 늘었다고 실제 책을 더 빌린 것은 아닙니다. 자식별로 회원 단위 집계한 후 붙이거나 필요한 목록을 별도로 조회하는 구조를 검토합니다. 현재 실습에는 loans 하나만 조인합니다.
DISTINCT를 붙여 결과를 줄이면 항상 해결된다고 생각하지 않습니다. 같은 제목 두 실물이 실제 다른 사건일 수 있고 동일 회원·도서의 재대여도 보존해야 합니다. 대여 사건을 셀 때는 l.id를 사용합니다. 쿼리를 바꿀 때 먼저 기대 결과의 행 단위와 업무 식별자를 다시 적고 수정 후 총 행 수와 회원별 값을 비교합니다.
실습 입력을 바꿔 가며 확인합니다
브라우저 입력은 members(id,name)와 loans(id,member_id,returned_at) 두 테이블입니다. 출력은 회원 ID, 이름, 현재 대여 수 세 칸이며 ID 오름차순입니다. 테스트에는 대여 없는 회원, 반납한 기록만 있는 회원, 미반납 두 건, 동명이인, 회원이 없는 빈 테이블이 포함됩니다. 숨은 회원 ID를 가정하지 않고 모든 회원을 조회합니다.
starter는 INNER JOIN과 WHERE 반환 조건을 사용하고 COUNT(*)를 셉니다. 일부 정상 회원은 맞게 나오지만 0건 회원이 빠집니다. LEFT JOIN으로 바꾸는 것만으로 충분한지 반환 조건과 집계 대상을 차례로 확인합니다. solution은 회원 1행당 정수 0 이상을 내며 빈 회원 테이블에서는 아무 행도 내지 않습니다. 별도 “회원 없음” 문자열은 결과 계약이 아닙니다.
오류와 틀린 결과를 다르게 읽습니다
no such column 오류는 별칭·컬럼 철자·제공된 스키마를 확인합니다. ambiguous column name은 두 테이블에 같은 이름이 있어 어느 테이블 컬럼인지 불명확하다는 뜻이므로 m.id나 l.id로 한정합니다. GROUP BY 오류는 SELECT의 비집계 컬럼을 살펴봅니다. 오류 없이 실행됐더라도 숫자가 업무 기대와 다르면 쿼리가 완성된 것은 아닙니다.
문법 오류를 고친 뒤에는 정답 SQL만 읽지 말고 집계 전 조인 행을 SELECT m.id,l.id,l.returned_at으로 출력합니다. 미매칭 NULL 행과 실제 미반납 행, 반납 이력이 어디서 사라지는지 확인합니다. 작은 표에서 예상 행을 세면 조건 위치의 차이를 설명할 수 있습니다. 실행 출력에는 현재 단계에서 직접 얻은 결과만 기록하며 다른 엔진 출력으로 바꾸지 않습니다.
저장 사실을 읽는 검증 SQL로 남깁니다
미션의 member-counts.sql은 같은 키와 NULL 정책을 H2 스키마에 적용합니다. JUnit은 같은 이름의 회원을 따로 세고 반납만 한 회원도 0으로 남기는지 검사합니다. 도서 제목을 붙인 이력 조회는 대여 ID 기준으로 보며 회원 집계와 다른 결과 단위임을 문서에 표시합니다. 집계 SQL을 API에 연결하기 전 DB 자체의 저장 결과를 확인하는 근거입니다.
완료 후 “LEFT JOIN이라 0이 나온다”에서 멈추지 않고 ON 조건이 현재 대여 후보를 정하고 COUNT(l.id)가 미매칭 행을 세지 않기 때문에 0이라고 설명합니다. 이 설명을 SQL 주석이나 제출 메모에 적습니다. 더 읽기에서 다양한 조인과 NULL 사례를 넓히고 이번에는 담당자의 전체 회원 보고서 요구를 만족하는 한 쿼리를 완성합니다.
따라하기
집계 전 조인 행을 봅니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
출력에서 실제 자식 ID와 미매칭 NULL ID를 구별합니다.
SELECT m.id,l.id,l.returned_at FROM members m LEFT JOIN loans l ON l.member_id=m.id ORDER BY m.id,l.id;실행 결과
1 1 NULL 2 2 2026-09-02T10:00:00 3 NULL NULL
조건 위치의 오류를 재현합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
반납만 한 회원 2가 사라집니다. 대여 없는 회원 3만 시험하면 놓칠 오류입니다.
SELECT m.id,COUNT(l.id) FROM members m LEFT JOIN loans l ON l.member_id=m.id WHERE l.returned_at IS NULL GROUP BY m.id ORDER BY m.id;실행 결과
1 1 3 0
모든 회원의 현재 대여 수를 출력합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
ON에서 대여 후보를 제한하고 자식 키를 셉니다.
SELECT m.id,m.name,COUNT(l.id) FROM members m LEFT JOIN loans l ON l.member_id=m.id AND l.returned_at IS NULL GROUP BY m.id,m.name ORDER BY m.id;실행 결과
1 민지 1 2 준 0 3 민지 0
COUNT 별 차이를 확인합니다
새 SQLite 콘솔 또는 브라우저 SQL 실습을 초기화하고 아래 초기화 SQL부터 실행합니다.
PRAGMA foreign_keys=ON;
CREATE TABLE books(id INTEGER PRIMARY KEY CHECK(id>0),title TEXT NOT NULL CHECK(length(trim(title))>0));
CREATE TABLE members(id INTEGER PRIMARY KEY,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE);
CREATE TABLE loans(id INTEGER PRIMARY KEY,book_id INTEGER NOT NULL REFERENCES books(id),member_id INTEGER NOT NULL REFERENCES members(id),loaned_at TEXT NOT NULL,returned_at TEXT CHECK(returned_at IS NULL OR returned_at>=loaned_at));
INSERT INTO books VALUES(7,'SQL'),(10,'Java'),(11,'Java');
INSERT INTO members VALUES(1,'민지','m@example.test'),(2,'준','j@example.test'),(3,'민지','n@example.test');
INSERT INTO loans VALUES(1,7,1,'2026-10-01T10:00:00',NULL),(2,10,2,'2026-09-01T10:00:00','2026-09-02T10:00:00');
미매칭 행의 COUNT(*)와 미반납 반환 시각의 COUNT가 왜 다른지 설명합니다.
SELECT m.id,COUNT(*),COUNT(l.id),COUNT(l.returned_at) FROM members m LEFT JOIN loans l ON l.member_id=m.id AND l.returned_at IS NULL GROUP BY m.id ORDER BY m.id;실행 결과
1 1 1 0 2 1 0 0 3 1 0 0
확인 문제
실습
members(id,name), loans(id,member_id,returned_at)에서 모든 회원의 현재 대여 수를 출력합니다. returned_at NULL이 미반납입니다. 대여 없는 회원·반납만 한 회원도 0을 표시하고 동명이인은 구별합니다. 출력은 id,name,active_count 세 칸, 회원 ID 오름차순입니다. 빈 회원 입력에는 출력 행이 없습니다.
모범 답안
SELECT m.id,m.name,COUNT(l.id) FROM members m LEFT JOIN loans l ON l.member_id=m.id AND l.returned_at IS NULL GROUP BY m.id,m.name ORDER BY m.id;
더 읽기
면접 질문
- 도서와 대여 기록의 테이블 관계를 설명합니다.