SQL NOT IN과 NOT EXISTS 차이: NULL 때문에 사라지는 행

NOT IN으로 제외 목록을 걸었는데 결과가 한 건도 나오지 않는다면, 하위 쿼리에 NULL이 들어 있는지 먼저 확인하면 된다. SQL의 NULL 비교는 단순한 참·거짓과 달라서, 일치하지 않은 값이 있어도 결과를 참으로 확정할 수 없는 경우가 생긴다.

반면 NOT EXISTS는 조건을 만족하는 행이 있는지를 검사한다. 두 표현의 차이는 속도보다 어떤 행을 남길 것인지에서 먼저 드러난다. 바깥쪽 값도 NULL일 수 있다면 그대로 치환하기 전에 처리 기준을 정해야 한다.

재현할 데이터 준비

아래 예제는 PostgreSQL 17.10에서 실행했다. 새 실습 세션에서 순서대로 실행하고 마지막의 ROLLBACK까지 진행한다. 임시 테이블을 사용하므로 기존 업무 테이블을 바꾸지 않는다.

BEGIN;
CREATE TEMP TABLE sample_users (id integer);
CREATE TEMP TABLE sample_blocked (user_id integer);
INSERT INTO sample_users VALUES (1), (2), (3), (NULL);
INSERT INTO sample_blocked VALUES (2), (NULL);

사용자는 1, 2, 3, NULL, 제외 목록은 2, NULL이다. 기대하는 결과를 “번호가 확인된 사용자 중 제외 목록에 없는 1번과 3번”으로 정해 보자.

NOT IN이 아무 행도 남기지 않는 이유

SELECT id FROM sample_users
WHERE id NOT IN (SELECT user_id FROM sample_blocked)
ORDER BY id;

실행 결과는 0행이다. 1번을 비교하면 1 <> 2는 참이지만 1 <> NULL은 알 수 없음이다. 두 조건을 모두 만족해야 하는데 참으로 확정할 수 없어 WHERE를 통과하지 못한다. 2번은 목록에 실제로 있으므로 거짓이고, 3번도 1번과 같은 이유로 제외된다.

필터를 빼고 판정값을 직접 조회하면 차이가 보인다.

SELECT id,
       id NOT IN (SELECT user_id FROM sample_blocked) AS allowed
FROM sample_users ORDER BY id NULLS LAST;

id	allowed
1	NULL
2	false
3	NULL
NULL	NULL

여기서 NULL은 출력에서 비교 결과를 확인하기 위한 표기다. WHERE는 참인 행만 남기므로 거짓과 알 수 없음 모두 결과에서 빠진다. “NULL을 만나면 항상 전체 결과가 0행”으로 외우기보다는 각 행의 비교 결과를 보는 편이 정확하다.

제외 목록의 NULL을 제거하는 방법

바깥쪽 NULL도 제외한다는 요구라면 다음 쿼리로 1번과 3번이 나온다.

SELECT id FROM sample_users
WHERE id NOT IN (
  SELECT user_id FROM sample_blocked WHERE user_id IS NOT NULL
)
ORDER BY id;

이번 데이터에서는 제외 목록에 2번이 남으므로 바깥쪽 NULL의 비교는 여전히 알 수 없음이다. 다만 하위 쿼리가 완전히 비면 결과가 달라진다. 빈 집합에 대한 NOT IN은 왼쪽 값이 NULL이어도 참이 될 수 있다. 바깥쪽 NULL을 항상 제외해야 한다면 id IS NOT NULL을 명시하는 것이 요구를 더 분명하게 표현한다.

NOT EXISTS로 바꾸면 바깥쪽 NULL도 남는다

SELECT u.id FROM sample_users u
WHERE NOT EXISTS (
  SELECT 1 FROM sample_blocked b WHERE b.user_id = u.id
)
ORDER BY u.id NULLS LAST;

이 쿼리의 결과는 1, 3, NULL이다. 바깥쪽 NULL에 대해 b.user_id = u.id가 참인 행을 찾을 수 없어서 NOT EXISTS를 통과하기 때문이다. 제외 목록에 NULL이 하나 있다고 해서 NULL끼리 =로 같아지는 것은 아니다.

번호가 없는 사용자를 결과에서 빼려면 다음처럼 요구를 명시한다.

SELECT u.id FROM sample_users u
WHERE u.id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM sample_blocked b WHERE b.user_id = u.id
  )
ORDER BY u.id;

이제 결과는 1번과 3번이다. 실제 서비스에서는 사용자의 기본 키가 NOT NULL일 수 있지만, 외부 조인이나 계산식으로 만든 비교값은 NULL이 될 수 있으므로 쿼리의 입력까지 확인해야 한다.

NULL끼리도 일치로 취급해야 한다면

PostgreSQL의 IS NOT DISTINCT FROM은 NULL끼리도 같다고 비교할 수 있다. 제외 목록에 NULL이 있으면 바깥쪽 NULL도 제외한다는 규칙을 다음처럼 표현한다.

SELECT u.id FROM sample_users u
WHERE NOT EXISTS (
  SELECT 1 FROM sample_blocked b
  WHERE b.user_id IS NOT DISTINCT FROM u.id
)
ORDER BY u.id;

현재 데이터의 결과는 1번과 3번이다. 제외 목록에서 NULL을 제거하면 바깥쪽 NULL은 다시 남는다. 앞의 u.id IS NOT NULL은 제외 목록과 무관하게 NULL을 빼므로 두 방식은 같은 요구가 아니다. DBMS별 지원 문법도 확인해야 한다.

빈 제외 목록의 동작은 별도로 확인할 수 있다.

SELECT NULL::integer NOT IN (
  SELECT user_id FROM sample_blocked WHERE false
) AS empty_set_result;
ROLLBACK;

empty_set_result는 참이다. ROLLBACK으로 실습용 임시 테이블 생성과 입력을 되돌리고 세션을 정리한다.

선택 기준

필요한 결과	표현할 조건
양쪽 값이 NULL이 아님을 보장	NOT IN 또는 NOT EXISTS의 결과 의미 확인
제외 목록의 NULL 때문에 정상 번호가 사라짐	하위 쿼리의 NULL 제외 또는 NOT EXISTS
바깥쪽 NULL을 항상 제외	바깥쪽 IS NOT NULL 추가
제외 목록에 NULL이 있을 때만 바깥쪽 NULL 제외	NULL을 같게 비교하는 조건 사용

NOT EXISTS가 언제나 빠르다는 결론은 이 예제에서 내릴 수 없다. 데이터 크기·인덱스·통계와 실행 계획에 따라 달라진다. 먼저 같은 행을 반환하는 쿼리를 정하고 실제 데이터로 성능을 비교해야 한다.

NULL 처리 규칙은 PostgreSQL 하위 쿼리 문서에서 더 확인할 수 있다. 행을 남기는 기준을 정한 뒤, 제외 목록과 바깥쪽 값에 NULL이 각각 있는 경우와 없는 경우, 목록 자체가 빈 경우를 나누어 검증하면 된다.