NOT IN 서브쿼리에 NULL이 하나 섞이면 왜 결과가 0건일까

주문한 적 없는 고객을 찾으려고 NOT IN 서브쿼리를 썼는데 결과가 0건으로 나올 때가 있습니다. 문법 오류도 경고도 없습니다. 서브쿼리 결과에 NULL이 한 행이라도 있으면 NOT IN은 어떤 행도 남기지 않기 때문입니다. 바깥 행을 참조하는 상관 서브쿼리라면 NULL을 받은 바깥 행에만 영향이 갑니다. 이 글을 읽고 나면 그 이유를 참·거짓·NULL로 따져 볼 수 있고, NOT EXISTS로 바꿀 때 결과가 달라지는 경우까지 구분해서 고를 수 있습니다.

예제는 2026-11-01에 macOS 27.0.1에서 실행했습니다. SQLite는 macOS에 들어 있는 3.54.0 셸이고, PostgreSQL은 서버 설치가 아니라 Node.js v24.21.0 위의 PGlite 0.5.10입니다. PGlite는 PostgreSQL을 WebAssembly로 빌드한 것이고, version()은 PostgreSQL 18.6으로 나왔습니다. 한 파일의 문장을 줄마다 그대로 양쪽에 보냈으니 아래에서 같은 문장이라고 하면 글자까지 같습니다. 고객과 주문은 가상의 예입니다. MySQL, Oracle, SQL Server, 서버로 설치한 PostgreSQL은 실행하지 않았습니다.

손님 주문 한 행이 결과를 비운다

고객 세 명과 주문 네 건입니다. 주문 102번은 회원이 아닌 손님의 주문이라 customer_id가 NULL이고, 103번은 없는 고객 999를 가리킵니다. 103번은 뒤에서 씁니다.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO customers VALUES (1, 'Mina'), (2, 'Jun'), (3, 'Sora');
INSERT INTO orders VALUES (100, 1), (101, 1), (102, NULL), (103, 999);

주문이 없는 고객은 Jun과 Sora입니다. 그런데 이 문장은 SQLite와 PostgreSQL 모두에서 0행을 돌려주었습니다.

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

모르는 값과 비교하면 답도 모른다

PostgreSQL 문서는 보통의 비교 연산자가 입력 중 하나라도 NULL이면 참도 거짓도 아닌 NULL을 낸다고 적습니다. 2 = NULL도 2 <> NULL도 NULL이라는 뜻이고, 실행 결과도 그랬습니다.

WHERE는 조건이 참인 행만 남깁니다. PostgreSQL 문서의 WHERE 설명에 따르면 결과가 거짓이나 NULL인 행은 버려집니다. 그래서 NOT IN의 값이 NULL이면 거짓일 때와 똑같이 행이 사라집니다.

PostgreSQL 문서는 값 목록을 쓴 NOT IN이 값마다 <>로 비교해 AND로 이은 식의 줄임말이라고 적고, 서브쿼리를 쓴 형식의 NULL 결과도 Boolean 조합의 일반 규칙으로 설명합니다. 그래서 서브쿼리가 돌려주는 값인 1, 1, NULL, 999를 목록으로 넣어 두 식을 나란히 실행했습니다.

SELECT 2 NOT IN (1, 1, NULL, 999), 2 <> 1 AND 2 <> 1 AND 2 <> NULL AND 2 <> 999;
SELECT 1 NOT IN (1, 1, NULL, 999), 1 <> 1 AND 1 <> 1 AND 1 <> NULL AND 1 <> 999;

첫 줄은 양쪽 모두 두 칸이 NULL이었고, 둘째 줄은 두 칸 모두 거짓이었습니다. SQLite는 거짓을 0으로, PostgreSQL은 false로 돌려주었습니다.

정리하면 서브쿼리에 NULL이 있을 때 값이 일치하는 행은 거짓, 일치하지 않는 행은 NULL입니다. 참이 될 길이 없으므로 0행은 이 데이터만의 우연이 아닙니다. PostgreSQL 문서의 NOT IN 설명과 SQLite 문서의 결과 표도 일치하지 않는 경우를 NULL로 적습니다.

개발 노트

NOT IN은 왜 아무 행도 남기지 않는가

서브쿼리 결과: 1, 1, NULL, 999 (예제의 customer_id 값)

• 고객 2번 Jun: 주문 없음

  2 NOT IN (1, 1, NULL, 999) -- NULL

  • 2 <> 1 은 참
  • 2 <> 1 은 참
  • 2 <> NULL 은 NULL
  • 2 <> 999 는 참
  • 넷을 AND로 묶으면 NULL이므로 버려진다

• 고객 1번 Mina: 주문 있음

  1 NOT IN (1, 1, NULL, 999) -- false

  • 1 <> 1 이 거짓
  • AND에 거짓이 하나 있으면 전체가 거짓이므로 버려진다

• WHERE가 남기는 행

  • 참인 행만 남고 거짓과 NULL은 버려진다
  • 서브쿼리에 NULL이 있으면 어떤 고객도 참이 될 수 없다
  • 이 서브쿼리는 바깥 행과 무관하므로 데이터가 무엇이든 0행이다

SQLite 3.54.0과 PGlite 0.5.10(PostgreSQL 18.6)에서 NOT IN과 AND로 풀어 쓴 식의 값이 같은 것을 2026-11-01에 확인했다. 다른 데이터베이스는 실행하지 않았다.

이 결론은 서브쿼리가 바깥 행마다 같은 행을 돌려주고 NOT IN이 그대로 WHERE 조건일 때의 이야기입니다. 아래 상관 서브쿼리에서는 고객마다 100에 자기 번호를 더한 번호의 주문만 봅니다.

SELECT c.name FROM customers c WHERE c.id NOT IN (SELECT o.customer_id FROM orders o WHERE o.id = 100 + c.id) ORDER BY c.id;

Mina는 1을 받아 거짓, Jun은 NULL을 받아 NULL, Sora는 999를 받아 참이었고, 양쪽 모두 Sora를 돌려주었습니다. 맨 처음 문장을 WHERE (id NOT IN (...)) IS NOT TRUE로 바꾸면 거짓과 NULL이 모두 통과해 양쪽 모두 고객 세 명을 돌려주었습니다.

고치는 세 문장

첫째는 서브쿼리에서 NULL을 빼는 것이고, 둘째는 NOT EXISTS, 셋째는 LEFT JOIN입니다. 이 데이터에서는 세 문장 모두 양쪽에서 Jun과 Sora를 돌려주었습니다.

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL) ORDER BY id;
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) ORDER BY c.id;
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL ORDER BY c.id;

LEFT JOIN에서는 NULL을 확인할 칸으로 orders의 기본 키를 썼습니다. 이어진 행이 있으면 기본 키는 NULL일 수 없으니, 이 칸이 NULL이면 이어진 주문이 없다는 뜻입니다.

PGlite에서 ORDER BY를 뺀 NOT IN과 NOT EXISTS 문장을 EXPLAIN (COSTS OFF)로 돌려 계획도 보았습니다. NOT IN은 Seq Scan에 hashed SubPlan 필터였고 NOT EXISTS는 Hash Right Anti Join이었습니다. 계획이 다르다는 것만 본 것입니다. 행이 몇 개 없는 표라 속도는 재지 않았습니다.

NOT EXISTS로 바꾸면 결과가 달라지는 방향

위의 고침들은 NULL이 서브쿼리 쪽에 있을 때였습니다. 왼쪽 값이 NULL일 수 있으면 NOT IN과 NOT EXISTS는 같은 뜻이 아닙니다. 이번에는 고객이 없는 주문을 찾습니다.

SELECT id FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) ORDER BY id;
SELECT o.id FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id) ORDER BY o.id;

첫 문장은 103번만, 둘째 문장은 102번과 103번을 돌려주었습니다. 102번의 customer_id는 NULL이어서 NOT IN에서는 NULL이 되어 버려지고, NOT EXISTS에서는 맞는 고객 행이 하나도 없으니 참이 됩니다. 손님 주문을 결과에 넣을지는 질문에 달린 일이지 어느 쪽이 맞다고 정해져 있지 않습니다. 습관처럼 바꾸면 결과에 행이 늘어날 수 있다는 점만 확인해 두면 됩니다.

왼쪽이 NULL이어도 NOT IN이 참이 되는 경우가 하나 있습니다. 서브쿼리에 행이 없을 때입니다. SQLite 문서는 오른쪽이 빈 집합이면 왼쪽이 NULL이어도 IN은 거짓, NOT IN은 참이라고 적고, PostgreSQL 문서도 서브쿼리가 행을 돌려주지 않는 경우 NOT IN을 참으로 적습니다. 실행 결과도 같았습니다. PostgreSQL 문서의 NULL 설명은 왼쪽이 NULL이면 결과가 NULL이라고 함께 적는데, PGlite 출력은 빈 서브쿼리에서 참이었습니다.

같은 함정이 있는 <>

NOT IN을 쓰지 않아도 원리는 같습니다. WHERE customer_id <> 1은 103번만 돌려주었고 NULL인 102번은 빠졌습니다. NULL도 다른 값으로 치고 싶다면 WHERE customer_id IS DISTINCT FROM 1이 102번과 103번을 돌려주었습니다. 이 연산자는 이번에 실행한 SQLite 3.54.0과 PGlite 양쪽에서 같은 문장으로 돌았습니다. 다른 데이터베이스에서는 확인하지 않았습니다.

기억할 것은 하나입니다. 서브쿼리 칸에 NULL이 들어올 수 있으면 NOT IN 대신 NOT EXISTS를 쓰고, 그때 왼쪽이 NULL인 행을 결과에 넣을지를 따로 정하십시오. 이 권장은 위 실행과 문서에서 끌어낸 판단이며, 데이터베이스나 표 크기에 따라 다른 선택이 나을 수 있습니다.