PostgreSQL NOT IN 조회가 비었다면 제외 목록의 NULL을 확인한다

PostgreSQL에서 NOT IN (하위 조회)로 제외 대상을 걸렀는데 결과가 전부 비었다면, 먼저 하위 조회가 반환하는 열에 NULL이 있는지 확인한다. 제외 목록에 2와 NULL이 있으면 후보 1과 3도 조회에서 사라진다. 두 값이 제외 대상으로 바뀐 것은 아니다. 조건의 결과가 true가 아니라 NULL, 즉 알 수 없음이 되기 때문이다.

제외 목록의 NULL을 무시해도 된다면 하위 조회에 IS NOT NULL을 넣을 수 있다. 같은 ID가 있는 행만 제외하려면 상관 NOT EXISTS로 표현할 수 있다. 다만 조회 대상 ID도 NULL일 수 있다면 두 쿼리의 결과는 달라진다. 어떤 행을 남길지 먼저 정해야 한다.

제외 목록에는 2가 있는데 1과 3도 안 나온다

아래는 실제 장애 기록이 아니라 이 차이를 확인하기 위한 합성 자료다. 후보는 1, 2, 3, NULL, 제외 목록은 2, NULL이다. 전용 임시 PostgreSQL 18.6에서 실행했다.

직접 비교하려면 별도 실습 DB에 연결된 psql 세션에서 다음을 실행한다. WITH 안의 VALUES가 이번 조회에만 쓸 자료를 만들므로 테이블을 준비할 필요가 없다. 앞의 두 줄은 출력을 |로 구분하고, 빈칸으로 보이기 쉬운 NULL을 <NULL>로 표시하는 psql 설정이다. 다른 SQL 편집기에서는 두 줄을 빼고 실행한다. psql 출력 설정↗ (새 탭에서 열림)

\pset format unaligned
\pset null '<NULL>'
WITH candidates(id) AS (VALUES (1), (2), (3), (NULL::integer)),
excluded(id) AS (VALUES (2), (NULL::integer))
SELECT c.id
FROM candidates c
WHERE c.id NOT IN (SELECT e.id FROM excluded e);

실행 결과의 조회 부분은 다음과 같았다. SQL 오류 없이 0행을 반환했다.

id
(0 rows)

1 NOT IN (2, NULL)은 의미상 1 <> 2 AND 1 <> NULL로 풀어 볼 수 있다. 앞 비교는 참이지만 뒤 비교는 알 수 없다. 따라서 전체 결과도 NULL이다. 2는 제외 목록의 2와 같으므로 조건이 거짓이다. NOT IN의 비교 규칙↗ (새 탭에서 열림)과 하위 조회의 NULL 규칙↗ (새 탭에서 열림)에 명시된 동작이다.

WHERE는 조건이 참인 행만 남기며 거짓과 NULL은 모두 걸러낸다. 그래서 반환 행만 보면 “모두 제외 대상”인지 “비교 결과를 알 수 없음”인지 구분하기 어렵다. WHERE의 행 선택 규칙↗ (새 탭에서 열림)

WHERE에서 빼고 조건의 값을 본다

같은 자료에서 조건을 SELECT 목록으로 옮기면 차이가 보인다. 앞서 정한 psql 출력 설정을 유지한 채 다음을 실행한다.

WITH candidates(id) AS (VALUES (1), (2), (3), (NULL::integer)),
excluded(id) AS (VALUES (2), (NULL::integer))
SELECT c.id,
       c.id NOT IN (SELECT e.id FROM excluded e) AS raw,
       c.id NOT IN (SELECT e.id FROM excluded e
                    WHERE e.id IS NOT NULL) AS filtered,
       NOT EXISTS (SELECT 1 FROM excluded e
                   WHERE e.id = c.id) AS anti
FROM candidates c
ORDER BY c.id NULLS LAST;

실제 출력이다. t는 참, f는 거짓이고 <NULL>은 알 수 없음이다.

id|raw|filtered|anti
1|<NULL>|t|t
2|f|f|f
3|<NULL>|t|t
<NULL>|<NULL>|<NULL>|t
(4 rows)

filtered는 제외 목록에서 NULL을 빼고 2만 비교한다. 이를 WHERE 조건으로 사용한 별도 조회에서는 1, 3 두 행이 남았다. 제외 목록이 처음부터 2 하나인 대조 조건에서도 같은 두 값이 참이었다. 따라서 이 자료에서 비NULL 후보까지 사라진 차이는 제외 목록의 NULL로 설명된다.

anti의 NOT EXISTS는 현재 후보와 e.id = c.id가 참인 제외 행이 있는지 본다. 1과 3에는 그런 행이 없으므로 참이다. 후보가 NULL이어도 NULL = NULL은 참이 아니어서 일치 행을 찾지 못한다. 이를 WHERE에 넣은 조회에서는 1, 3, NULL 세 행이 남았다. EXISTS가 하위 조회의 행 존재 여부를 판단한다는 공식 설명↗ (새 탭에서 열림)과 일치한다.

여기서 WHERE e.id = c.id는 바깥 후보와 안쪽 제외 목록을 연결하는 조건이다. 이 조건을 빼면 후보별 비교가 사라진다. 이번처럼 제외 목록에 행이 하나라도 있으면 모든 후보에서 NOT EXISTS가 거짓이 된다.

NULL 후보를 남길지까지 정한다

위 출력에서 1과 3만 보면 두 수정은 같아 보이지만 마지막 행은 다르다. 실제 쿼리를 바꾸기 전에는 다음 의미 중 무엇이 필요한지 확인한다.

• 제외 목록의 NULL은 비교할 ID가 없다는 뜻이고, ID가 있는 후보끼리 비교하려면 NOT IN 하위 조회에 WHERE e.id IS NOT NULL을 둔다. 후보 ID도 항상 비NULL인 조건에서 결과를 비교한다.
• 비NULL ID가 같은 제외 행만 찾고 후보의 NULL은 남겨도 된다면 NOT EXISTS (... WHERE e.id = c.id)를 쓴다. 후보의 NULL을 항상 버려야 한다면 바깥 조건에 c.id IS NOT NULL AND를 명시한다.
• NULL도 하나의 같은 분류로 취급해 제외하려면, NOT EXISTS 안의 비교를 e.id IS NOT DISTINCT FROM c.id로 바꾼다.

마지막 선택은 NULL끼리도 일치한다고 정의하는 것이다. 다음 전체 쿼리를 같은 자료에서 실행하면 1, 3만 남는다.

WITH candidates(id) AS (VALUES (1), (2), (3), (NULL::integer)),
excluded(id) AS (VALUES (2), (NULL::integer))
SELECT c.id
FROM candidates c
WHERE NOT EXISTS (
  SELECT 1 FROM excluded e
  WHERE e.id IS NOT DISTINCT FROM c.id
)
ORDER BY c.id NULLS LAST;

실제로 반환한 값은 다음과 같았다. 제외 목록의 NULL과 후보의 NULL이 일치했으므로 그 후보는 빠졌다. IS NOT DISTINCT FROM은 양쪽이 모두 NULL이면 참을 반환한다. NULL을 포함하는 비교 술어↗ (새 탭에서 열림)

id
1
3
(2 rows)

주의할 경계도 있다. 제외 목록이 NULL 한 행뿐이면 IS NOT NULL 필터 뒤에는 빈 목록이 된다. 실제로 이 조건에서는 filtered가 후보 NULL에도 참이었다. 빈 하위 조회에 대한 NOT IN은 참이므로, 오른쪽에 NULL 필터를 넣었다는 이유만으로 왼쪽 NULL까지 항상 걸러진다고 생각하면 안 된다. 후보 NULL을 버리는 정책은 바깥쪽에 별도로 표현한다.

자기 쿼리에서는 NOT IN 오른쪽의 하위 조회를 따로 실행해 최종 반환 열을 먼저 확인한다. NULL이 없다면 이 재현만으로 빈 결과의 원인을 설명할 수 없다. NULL이 있다면 그것이 잘못 들어온 데이터인지, 아직 ID가 정해지지 않은 정상 상태인지 확인한 뒤 비교 정책을 고른다. 단순히 행 수가 늘어났다는 이유로 수정이 맞다고 판단하지 않는다.

이번 실험은 정수 한 열과 작은 합성 자료에서 반환값을 비교했다. 운영 데이터, 여러 열을 묶은 비교, 실행 계획이나 성능은 검증하지 않았다. NOT EXISTS를 고르는 근거는 여기서는 원하는 NULL 처리 의미이며 속도 측정 결과가 아니다.