SQL로 조회·JOIN·집계하기

결론부터

조회할 행의 단위와 관계를 먼저 정하면 JOIN과 집계 결과가 왜 늘거나 줄었는지 설명할 수 있다.

“미완료 작업 목록”과 “프로젝트별 작업 수”는 같은 데이터를 읽어도 결과 행의 단위가 다르다. 전자는 작업 한 개, 후자는 프로젝트 한 개가 한 행이다. 공통 예제를 준비하고 다음 SQL을 psql에서 실행한다.

이 장에서 처음 나오는 말2개

JOIN

관계를 기준으로 두 입력의 행들을 결합한다.

집계Aggregation

여러 행을 개수·합계 같은 값으로 요약한다.

행과 컬럼 고르기

SELECT id, title
FROM public.tasks
WHERE done = false
ORDER BY id;

결과는 ID 1의 백업 확인, ID 3의 SQL 복습이다. WHERE가 행을 고르고 SELECT가 출력할 컬럼을 정한다. ORDER BY가 없으면 결과 순서를 가정하지 않는다. (조회 기초)

미배정 작업은 assignee = NULL 대신 다음처럼 찾는다. NULL과의 일반 비교 결과는 참이 아니기 때문이다.

SELECT id, title FROM public.tasks WHERE assignee IS NULL;

ID 3만 나온다. 앱에서 사용자 입력을 받으면 SQL 문자열에 직접 붙이지 않고 드라이버의 매개변수 바인딩을 쓴다. 값과 SQL 구조를 분리해야 따옴표 처리와 SQL 삽입 문제를 피할 수 있다.

JOIN에서 행이 늘어나는 이유

SELECT p.name, t.title
FROM public.projects AS p
JOIN public.tasks AS t ON t.project_id = p.id
ORDER BY p.id, t.id;

플랫폼은 작업이 두 개라 두 행, 스터디는 한 행이다. 빈 프로젝트는 대응하는 작업이 없어 사라진다. LEFT JOIN으로 바꾸면 작업이 없는 프로젝트도 남고 작업 쪽 컬럼이 NULL인 행이 생긴다. (JOIN)

여러 일대다 관계를 동시에 JOIN하면 행이 곱해질 수 있다. 예를 들어 작업 2개와 프로젝트 멤버 3명을 같이 결합하면 6행이 될 수 있다. 그 결과를 바로 COUNT하면 작업 수가 부풀어 오른다. 먼저 각 관계를 집계하거나 정말 세고 싶은 고유 키를 기준으로 계산한다.

작업이 없는 프로젝트까지 세기

SELECT p.name,
       count(t.id) AS task_count,
       count(t.id) FILTER (WHERE NOT t.done) AS open_count
FROM public.projects AS p
LEFT JOIN public.tasks AS t ON t.project_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

name	task_count	open_count
플랫폼	2	1
스터디	1	1
빈 프로젝트	0	0

count(*)는 LEFT JOIN이 남긴 빈 작업 행도 세므로 마지막 프로젝트가 1이 된다. count(t.id)는 NULL을 제외해 실제 작업만 센다. WHERE NOT t.done을 JOIN 뒤에 두면 빈 프로젝트가 탈락할 수 있으므로 여기서는 집계의 FILTER로 미완료 수만 제한한다. (집계)

변경 결과 확인하기

BEGIN;
UPDATE public.tasks SET done = true WHERE id = 1
RETURNING id, title, done;
DELETE FROM public.tasks WHERE id = 3 RETURNING id;
ROLLBACK;

UPDATE는 ID 1의 변경된 행을, DELETE는 ID 3을 반환한다. ROLLBACK해서 원래 데이터로 돌아간다. RETURNING은 영향을 받은 행을 확인하는 도구다. 수정 대상이 0행인 상황도 앱에서 처리해야 한다. (RETURNING)

이해 확인: 프로젝트 이름을 한 번만 출력하고 싶다고 무조건 DISTINCT를 붙이면 될까? 조회 의도가 작업 목록이라면 서로 다른 작업을 숨길 수 있다. 먼저 결과 한 행이 무엇을 의미하는지 정한다.