NOT IN 서브쿼리에 NULL 하나만 섞여도 결과가 0건이 되는 이유

한서윤 · 2026년 10월 27일 · 쿼리와 SQL

매달 초 돌아가는 배치 하나가 주문 이력이 없는 고객에게 쿠폰을 보내는 일을 맡고 있었다. 어느 날 아침 보니 발송 대상이 0명으로 찍혀 있었다. 에러 로그는 깨끗했고 쿼리는 몇 달째 그대로였다. 직접 세어 보니 지난달 가입하고 아무것도 사지 않은 고객만 수백 명이었다.

범인은 WHERE 절의 NOT IN 서브쿼리였다. 직전 배포에서 비회원 주문 기능이 붙으면서 orders.customer_id에 NULL이 들어가기 시작했고, 그 NULL 한 줄이 결과 전체를 지웠다. 고치는 데는 5분이면 됐지만 이유를 설명하기까지는 훨씬 오래 걸렸다. 그 과정과, 대안 두 가지가 PostgreSQL, MySQL, SQLite에서 어떻게 실행되는지를 적어 둔다.

0건이 나오는 상황을 다시 만들어 보기

세 데이터베이스에서 그대로 돌아가는 최소 예제다.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR(20));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);

INSERT INTO customers VALUES (1, '민지'), (2, '도윤'), (3, '서연');
INSERT INTO orders VALUES (10, 1), (11, 1), (12, NULL);

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

고객 2번과 3번은 주문이 없으니 두 줄이 나와야 하는데, 세 곳 모두 빈 결과를 돌려준다. 버그가 아니라 표준이 정한 동작이다. (12, NULL)만 빼면 기대한 두 줄이 나온다.

처음 겪었을 때는 고객 테이블에 무슨 일이 생긴 줄 알고 customers만 한참 뒤졌다. 결과가 비면 바깥 테이블부터 보게 되는데, 이 함정은 안쪽 테이블에 숨어 있다.

3값 논리로 한 단계씩 따라가기

SQL의 비교는 TRUE, FALSE 말고 UNKNOWN을 돌려줄 수 있다. NULL은 모르는 값이라 2 = NULL도 2 <> NULL도 UNKNOWN이다. WHERE 절은 TRUE인 행만 남기고 FALSE와 UNKNOWN은 똑같이 버린다.

x NOT IN (a, b, c)는 x <> a AND x <> b AND x <> c와 같다. 서브쿼리가 1, 1, NULL을 돌려주므로 고객 2번의 조건은 이렇게 풀린다.

AND는 FALSE가 하나라도 있으면 FALSE, FALSE 없이 UNKNOWN이 섞이면 UNKNOWN이다. 고객 1번은 1 <> 1이 FALSE라 원래 빠질 행이었고, 2번과 3번은 UNKNOWN 때문에 빠진다. NULL이 하나라도 있으면 어떤 행도 TRUE에 닿지 못하니 정확히 0건이 된다.

PostgreSQL 문서도 오른쪽 행 중 하나라도 NULL이고 같은 값이 없으면 NOT IN의 결과는 null이라고 적어 두었다. SQLite 문서의 결과표도 같다.

IN은 OR로 이어지고 TRUE OR UNKNOWN은 TRUE라서 멀쩡하다. 같은 서브쿼리가 IN일 때는 문제없다가 NOT을 붙이는 순간 무너지니 리뷰에서 눈에 잘 띄지 않는다.

NOT EXISTS와 LEFT JOIN … IS NULL로 바꾸기

그날 아침 바꾼 쿼리는 NOT EXISTS였다.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS는 값을 비교하지 않고 조건을 만족하는 행이 있는지만 묻는다. 고객 2번에 대해 o.customer_id = c.id는 FALSE, FALSE, UNKNOWN이고, TRUE가 없으니 NOT EXISTS는 TRUE가 된다. UNKNOWN이 일치하지 않음 쪽으로 흡수되는 셈이다.

LEFT JOIN으로 쓰면 이렇다.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

짝이 없는 고객은 o 쪽 컬럼이 모두 NULL이 된다. IS NULL은 짝이 있으면 절대 NULL일 수 없는 기본키 o.id에 건다.

서브쿼리에 WHERE customer_id IS NOT NULL을 붙여도 결과는 맞지만, 누군가 그 조건을 지우면 같은 사고가 다시 난다.

바꿀 때는 의미도 바뀐다는 점을 조심한다. 바깥 컬럼이 NULL인 행을 NOT IN은 버리고 NOT EXISTS는 남긴다. 작년에 리포트 쿼리 하나를 NOT EXISTS로 바꿨더니 숫자가 수십 건 늘어 검수 쪽 질문을 받았다. 추천인 코드가 비어 있는 회원들이 새로 잡힌 것이었다. 어느 쪽이 맞는지는 업무가 정할 문제라 기획 쪽과 집계 기준부터 다시 맞췄다.

실행 계획은 어떻게 달라지나

NULL이 들어올 수 있다는 사실은 옵티마이저가 NOT IN을 다루는 방식도 바꾼다.

PostgreSQL

PostgreSQL 위키의 「Don’t Do This」는 성능도 NOT IN을 피할 이유로 든다. 플래너가 NOT IN (SELECT ...)을 안티 조인으로 바꾸지 못하고 SubPlan으로 처리하는데, 결과가 work_mem에 들어가면 해시로, 넘치면 바깥 행마다 결과를 훑는 plain SubPlan으로 떨어진다. 위키는 다섯 자릿수 이상 느려질 수 있다고 경고한다.

스테이징에서 1초도 안 걸리던 정리 쿼리가 운영에서 끝나지 않았을 때 EXPLAIN을 떠 보니 SubPlan 앞에 hashed가 빠져 있었다. 운영 데이터에서 서브쿼리 결과가 work_mem을 넘었던 것이다.

NOT EXISTS와 LEFT JOIN … IS NULL은 Hash Anti Join 같은 안티 조인으로 잡혀서, 플래너가 조인 순서를 고를 수 있고 해시가 넘쳐도 배치로 나눠 처리한다.

PostgreSQL 19 릴리스 노트에는 NULL이 없을 때 NOT IN을 안티 조인으로 바꿀 수 있게 했다는 항목이 있다. 이 글을 쓰는 지금 그 페이지의 발표일은 아직 정해지지 않았으니, 그 전 버전에서는 기대하지 않는 편이 낫다.

MySQL

MySQL은 8.0.17부터 NOT IN (SELECT ...)과 NOT EXISTS (SELECT ...)를 안티 조인으로 바꾼다. 다만 매뉴얼에 따르면 비교 식이 nullable이면 대부분 이 변환을 적용하지 못한다. 외래키에 NOT NULL을 선언하는 일이 실행 계획에도 영향을 주는 셈이다.

SQLite

SQLite는 NOT IN 서브쿼리 결과를 임시 B-트리에 담고 바깥 행마다 찾아본다. SQLite 포럼에서 개발자가 직접 설명한 방식이라 목록 전체를 훑는 식으로 무너지지는 않는다. NOT EXISTS는 바깥 행마다 안쪽을 탐색하므로 orders.customer_id에 인덱스를 걸고 EXPLAIN QUERY PLAN으로 확인한다.

CREATE INDEX idx_orders_customer ON orders (customer_id);

세 데이터베이스를 한 표로 놓으면 이렇다.

지금은 이렇게 쓴다

• 서브쿼리를 받는 NOT IN은 쓰지 않는다. 직접 적은 상수 목록에만 쓴다.
• 없는 것을 찾을 때는 NOT EXISTS를 기본으로 쓴다. LEFT JOIN 형태는 팀 코드가 이미 그쪽일 때만 쓴다.
• 외래키 컬럼은 비어야 할 이유가 없으면 NOT NULL로 만든다.
• 기존 NOT IN을 바꿀 때는 바깥 컬럼이 NULL인 행부터 세어 본다.

리뷰에서 NOT IN을 보면 이제 스키마를 먼저 연다. 이 컬럼에 NULL이 들어올 수 있는지, 다음 기능에서 생길 수 있는지 묻는다. 쿠폰 배치가 몇 달 멀쩡했던 것도 NULL이 아직 없었을 뿐이다. NULL은 빈 값이 아니라 모르는 값이고, 모르는 값과 다르다고 단정할 수는 없다. 그 규칙을 0이라는 숫자로 배운 아침이었다.