PostgreSQL LEFT JOIN에 WHERE 조건을 넣자 부모 행이 사라졌다

PostgreSQL에서 부모 목록에 자식 정보를 붙이려고 LEFT JOIN했는데, WHERE c.status = 'active'를 추가하자 부모 일부가 빠질 수 있다. LEFT JOIN은 조인 결과에 부모를 남기지만, 그 결과를 WHERE가 다시 걸러낸다. 모든 부모를 보여 주면서 active 자식만 붙이려면 상태 조건을 ON에 둔다. active 자식이 있는 부모만 필요하다면 WHERE로 거르는 결과가 의도에 맞다.

자식이 없는 부모와 inactive 자식만 있는 부모를 나눠 본다

실제 장애 기록이 아니라 조건 위치의 차이를 확인하기 위한 합성 자료다. 새 임시 PostgreSQL 18.6에서 부모 세 개와 자식 두 개를 만들었다. 1번 부모에는 자식이 없고, 2번에는 active 자식, 3번에는 inactive 자식이 있다.

직접 비교하려면 PostgreSQL과 psql, 폐기 가능한 전용 실습 DB가 필요하다. 그 DB에 접속해 아래 입력과 뒤의 쿼리를 같은 psql 세션에서 실행한다. parents와 children은 이번 연결에서만 쓸 임시 테이블이다. 실습을 마치면 마지막에 안내하는 ROLLBACK;을 실행한다.

앞의 두 줄은 출력 구분자를 |로 하고 NULL을 [NULL]로 표시한다. SQL 편집기를 쓴다면 이 두 줄을 빼고, NULL 표시 방식을 확인한다. psql 출력 설정↗ (새 탭에서 열림)

\pset format unaligned
\pset null '[NULL]'
BEGIN;
CREATE TEMP TABLE parents (id integer PRIMARY KEY);
CREATE TEMP TABLE children (
    id integer PRIMARY KEY,
    parent_id integer NOT NULL,
    status text NOT NULL
);
INSERT INTO parents VALUES (1), (2), (3);
INSERT INTO children VALUES (201, 2, 'active'), (301, 3, 'inactive');

SELECT p.id AS parent_id, c.id AS child_id, c.status,
       c.status = 'active' AS is_active
FROM parents p
LEFT JOIN children c ON c.parent_id = p.id
ORDER BY p.id, c.id;

마지막 SELECT의 실제 출력이다. t는 참, f는 거짓이다.

parent_id|child_id|status|is_active
1|[NULL]|[NULL]|[NULL]
2|201|active|t
3|301|inactive|f
(3 rows)

1번은 붙일 자식이 없어 오른쪽 열이 NULL로 채워졌다. children.status를 NOT NULL로 선언했어도 조인이 만든 이 NULL은 나타난다. 그 NULL과 'active'를 비교한 값도 NULL, 즉 알 수 없음이다. 일반 비교 연산의 NULL 규칙은 PostgreSQL 비교 문서↗ (새 탭에서 열림)에 설명돼 있다.

여기에 상태를 거르는 WHERE를 넣어 본다.

SELECT p.id AS parent_id, c.id AS child_id, c.status
FROM parents p
LEFT JOIN children c ON c.parent_id = p.id
WHERE c.status = 'active'
ORDER BY p.id, c.id;

parent_id|child_id|status
2|201|active
(1 row)

1번은 조건 값이 NULL이라 빠지고, 3번은 거짓이라 빠졌다. WHERE는 참인 행만 남긴다. 따라서 LEFT JOIN이라는 이름만으로 최종 결과에 모든 부모가 있다고 판단할 수 없다. WHERE의 행 선택 규칙↗ (새 탭에서 열림)

모든 부모를 남기려면 상태를 ON에 둔다

부모 목록이 목적이고 자식은 active일 때만 보여 주려면, 상태 조건을 조인의 일치 조건에 포함한다.

SELECT p.id AS parent_id, c.id AS child_id, c.status
FROM parents p
LEFT JOIN children c
  ON c.parent_id = p.id AND c.status = 'active'
ORDER BY p.id, c.id;

실제로는 세 부모가 모두 나왔다.

parent_id|child_id|status
1|[NULL]|[NULL]
2|201|active
3|[NULL]|[NULL]
(3 rows)

달라진 행은 3번이다. 자식 301번은 존재하지만 전체 ON 조건을 만족하지 않는다. 이 부모에 일치하는 자식이 없으므로 LEFT JOIN이 오른쪽을 NULL로 채운 행을 만든다. 이 결과의 NULL은 “자식이 전혀 없다”가 아니라 “ON 조건에 맞는 자식이 없다”는 뜻이다. 자식 301번의 저장 값이 바뀐 것은 아니다.

PostgreSQL 18 공식 문서도 같은 오른쪽 조건을 ON과 WHERE에 둔 외부 조인 결과가 다름을 보여 준다. ON은 무엇을 일치로 볼지 정하고, WHERE는 만들어진 결과에서 무엇을 남길지 정한다. LEFT JOIN과 ON·WHERE 비교↗ (새 탭에서 열림)

WHERE 쿼리의 조건을 WHERE c.status = 'active' OR c.id IS NULL로 바꿔도 ON 쿼리와 같아지지 않는다. 실제 결과는 1번과 2번 두 행이었다. 3번은 ID 조건으로 자식 301번과 이미 일치해 c.id가 301이다. inactive라 앞 조건이 거짓이고, ID도 NULL이 아니어서 뒤 조건도 거짓이다. WHERE에서 그 행을 버린 뒤 NULL 행을 새로 보충하지 않는다.

필요한 것이 부모 전체인지, active 자식이 있는 부모인지 정한다

이번 자료에서 모든 부모를 보여 주려면 ON에 상태 조건을 둔 결과가 맞다. active 자식이 있는 부모만 보여 주려면 WHERE 쿼리처럼 2번만 남아야 한다. 자식 열도 필요하다면 INNER JOIN children c ON c.parent_id = p.id AND c.status = 'active'로 의도를 표현할 수 있다. 이 형태도 같은 자료에서 2번 한 행을 반환했다.

다만 “모든 부모를 남긴다”와 “부모마다 정확히 한 행이다”는 다른 조건이다. 별도 비교에서 INSERT INTO children VALUES (202, 2, 'active');로 2번의 active 자식을 하나 더 넣고 ON 쿼리를 다시 실행했다. 세 부모는 유지됐지만 2번이 두 번 나와 총 4행이 됐다. WHERE 쿼리도 2번을 두 행 반환했다.

자식 열 없이 조건에 맞는 부모 목록만 한 번씩 필요하다면 EXISTS로 존재 여부를 표현할 수 있다. 아래는 부모의 기본 키를 조회하므로 자식 수만큼 행이 늘어나지 않는다. EXISTS의 행 존재 판단↗ (새 탭에서 열림)

SELECT p.id AS parent_id
FROM parents p
WHERE EXISTS (
    SELECT 1 FROM children c
    WHERE c.parent_id = p.id AND c.status = 'active'
)
ORDER BY p.id;

parent_id
2
(1 row)

active 자식이 하나일 때와 두 개일 때 모두 이 결과였다. 비교를 마쳤으면 같은 세션에서 ROLLBACK;을 실행해 실습 테이블과 행을 되돌린다.

자기 쿼리를 확인할 때는 누락된 부모 하나의 ID로 범위를 좁혀 상태 조건을 WHERE에서 빼고 SELECT 목록에 표시해 본다. 위의 첫 조회처럼 참·거짓·NULL을 보면 조인에서 일치하지 않았는지, WHERE에서 걸러졌는지 구분할 수 있다. ON으로 옮겨도 부모가 빠진다면 다른 WHERE 조건이나 뒤에 이어지는 INNER JOIN이 그 행을 제거하는지 확인한다.

이번에 실행한 범위는 PostgreSQL 18.6의 작은 합성 자료와 반환 행 비교다. 실제 애플리케이션의 ORM 생성 SQL, 다른 조인과 결합된 쿼리, 실행 계획과 성능은 검증하지 않았다. 조건 위치는 행 수가 늘어나는 쪽이 아니라 조회하려는 부모의 범위에 맞춰 고른다.