늘모자란, 개발

제외 목록에 없는 항목을 고르는 SQL에서 결과가 갑자기 0행이 됐다면, 목록 길이보다 NULL부터 확인할 만하다. SQLite에서 오른쪽 목록이 2 하나일 때는 1과 3이 남았는데, 같은 목록에 NULL을 한 행 추가하자 NOT IN의 결과가 모두 사라졌다. 이번 글은 SQLite 기반 SQL 데이터 조회에서 ‘일치하는 행이 없다’는 조건과 ‘목록에 포함되지 않는다’는 조건이 언제 달라지는지 작은 재현 실험으로 살펴본다. 속도 비교가 아니라 반환되는 행의 의미를 비교한 실험이다.

비교하려는 것은 문법이 아니라 NULL의 처리 정책

검토한 대안은 원래의 NOT IN, 오른쪽에서 NULL을 제거한 NOT IN, 등호로 연결한 NOT EXISTS다. 오른쪽 목록의 NULL을 지우면 문제가 끝나는지, NOT EXISTS로 바꾸면 원래 결과를 그대로 보존하는지가 질문이다. 나중에는 왼쪽 NULL을 제외하는 조건과 SQLite의 IS 비교도 추가했다. 이 두 대안은 NULL을 유효한 비교 대상에 포함할지 결정하는 방법이며, 문법만 바꾸는 수정으로 취급하지 않았다.

SQLite 공식 표현식 문서는 IN과 NOT IN의 결과를 왼쪽 NULL 여부, 오른쪽 NULL 여부, 빈 집합 여부, 실제 일치 여부로 나눠 설명한다. 오른쪽이 비어 있지 않고 NULL을 포함하며 왼쪽 값이 일치하지 않으면 두 연산의 결과는 NULL이다.[1] 한편 WHERE는 조건이 참인 행만 통과시키고 거짓이나 NULL인 행은 제외한다.[2] 따라서 ‘제외 목록에서 못 찾았다’는 상황이 반드시 참으로 바뀌지는 않는다. 비교 결과를 알 수 없다는 상태도 출력에서는 탈락한다.

재현 조건과 방법

Python 3.11.15의 sqlite3 모듈과 SQLite 3.50.4를 사용했다. 메모리 데이터베이스에 candidates(id INTEGER)와 blocked(id INTEGER)를 만들었다. 왼쪽 테이블에는 1, 2, 3, NULL을 각각 한 행씩 넣었고 오른쪽은 2만 있는 경우, 2와 NULL이 있는 경우, NULL만 있는 경우, 빈 경우로 바꿨다. 외부 서비스나 운영 데이터를 조회하지 않았다.

같은 왼쪽 네 행을 유지한 채 오른쪽 네 집합과 쿼리 다섯 종류를 비교했다. 각 쿼리는 id로 정렬했고, 반환된 전체 행 배열을 미리 적은 기대값과 대조했다. 추가로 1과 NULL을 왼쪽 피연산자로 둔 NOT IN 식을 계산하고, NULL을 허용하는 UNIQUE 열에 NULL 두 행을 삽입했다. 지연 시간이나 실행 계획은 측정하지 않았다.

CREATE TABLE candidates(id INTEGER);
INSERT INTO candidates VALUES (1), (2), (3), (NULL);
CREATE TABLE blocked(id INTEGER);
-- blocked에는 각 실험 조건의 행을 넣는다.

-- A: 원래 NOT IN
SELECT id FROM candidates
WHERE id NOT IN (SELECT id FROM blocked)
ORDER BY id;

-- B: 오른쪽 NULL만 제거
SELECT id FROM candidates
WHERE id NOT IN (
  SELECT id FROM blocked WHERE id IS NOT NULL
)
ORDER BY id;

-- C: 등호로 연결한 NOT EXISTS
SELECT c.id FROM candidates AS c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked AS b WHERE b.id = c.id
)
ORDER BY c.id;

NULL을 오른쪽에 추가했을 때

오른쪽이 2 한 행일 때 원래 NOT IN과 오른쪽 NULL을 제거한 NOT IN은 모두 1과 3, 즉 2행을 반환했다. 등호로 연결한 NOT EXISTS는 여기에 왼쪽 NULL까지 포함해 3행을 반환했다. 오른쪽이 2와 NULL일 때 원래 NOT IN은 0행, 오른쪽 NULL을 제거한 NOT IN은 2행, 등호로 연결한 NOT EXISTS는 3행이었다. 오른쪽이 NULL 한 행일 때는 각각 0행, 4행, 4행이었고, 오른쪽이 비면 세 방식 모두 4행을 반환했다.

그림의 숫자는 이 실행에서 직접 얻은 반환 행 수다. ‘Filtered NOT IN’은 오른쪽에만 IS NOT NULL을 넣은 방식이고, ‘NOT EXISTS (=)’는 등호로 연결한 방식이다. 그림에서 두 방법의 숫자가 같아지는 조건만 보고 동등하다고 판단하면, 위쪽 두 행에 있는 차이를 놓친다. 행 수를 넘어 실제 값도 확인했다. 오른쪽이 2와 NULL일 때 B의 결과는 1과 3이고 C의 결과는 NULL, 1, 3이었다.

오른쪽의 NULL은 1이나 3과 같다는 증거가 아니다. 그렇다고 다르다는 증거도 되지 않는다. 이 경우 NOT IN은 NULL을 반환하므로 WHERE에서 통과하지 않는다.[1][2] 실제로 1 NOT IN (SELECT id FROM blocked)를 별도 계산했을 때 오른쪽이 2이면 1이었고, 2와 NULL이면 NULL이었다. 출력이 사라진 원인을 구문 오류나 연결 장애로 돌릴 필요가 없는 재현이다. 같은 연결에서 오른쪽 내용만 바꿔도 결과가 달라졌다.

오른쪽 NULL 제거로 해결되지 않는 반례

오른쪽 NULL을 제거하는 수정은 이번 조건에서 정상적인 정수 값 1과 3을 되살렸다. 그러나 왼쪽 NULL의 정책까지 정한 것은 아니다. 오른쪽이 2일 때 B는 왼쪽 NULL을 제외했고 C는 남겼다. 이번 실험에서는 ‘오른쪽 NULL만 지우면 등호 기반 NOT EXISTS와 완전히 같은 결과가 된다’는 대안이 성립하지 않았다. 두 쿼리의 행 수가 2와 3으로 다른 것을 예상값 배열 대조로 확인했다.

EXISTS 자체는 하위 쿼리가 행을 하나라도 반환하면 1, 하나도 반환하지 않으면 0이 된다. 선택한 열의 값이나 NULL 여부가 EXISTS의 결과를 바꾸지는 않는다.[1] C에서 중요한 부분은 하위 쿼리의 b.id = c.id다. 이번 데이터에서는 왼쪽 NULL과 일치하는 행이 이 조건으로 나오지 않았으므로 NOT EXISTS가 참이 됐고, 왼쪽 NULL은 남았다. NOT EXISTS라는 이름만으로 NULL을 제외한다고 해석해서는 안 된다.

빈 목록은 또 다른 경계 조건

오른쪽이 빈 집합이면 SQLite의 NOT IN은 왼쪽이 NULL이어도 참을 반환한다.[1] 실제 실행에서도 원래 NOT IN이 NULL, 1, 2, 3의 4행을 반환했다. 오른쪽이 NULL만 있던 조건에서 NULL을 필터링한 B도 오른쪽이 빈 집합으로 바뀌어 같은 네 행을 반환했다. 오른쪽이 비었을 때까지 왼쪽 NULL이 항상 탈락할 것이라고 예상하면 이 조건에서 틀린다.

이 차이를 테스트에 남기려면 ‘정상 값 몇 건이 살아났는가’만 검사하지 않는 편이 낫다. 이 실험에서는 NULL의 포함 여부까지 보는 전체 행 배열을 기대값으로 사용했다. 네 조건별 비교, NOT IN의 NULL 반례, B와 C의 비동등성, 빈 집합의 스칼라 결과, UNIQUE 삽입 결과를 검사한 8개의 assert 문이 모두 통과했다. 이는 해당 작은 입력의 예상 결과를 확인한 것이며, 실제 서비스의 모든 입력을 검증했다는 뜻은 아니다.

왼쪽 NULL을 어떻게 취급할 것인가

미정인 id는 결과에 넣지 않는 정책이라면 C의 바깥 조건에 c.id IS NOT NULL AND를 추가할 수 있다. 이 네 조건에서 그 방식은 순서대로 1과 3, 1과 3, 1·2·3, 1·2·3을 반환했다. 반대로 오른쪽의 NULL이 왼쪽 NULL을 제외하는 표시여야 한다면, 이번 SQLite 실험에서는 하위 쿼리의 등호를 IS로 바꾼 대안이 그 동작을 보였다. 오른쪽이 2와 NULL일 때 1과 3이 남았고, NULL만 있을 때 1·2·3이 남았다. 오른쪽이 2만 있거나 비었을 때는 왼쪽 NULL이 남았다. 어느 쪽도 원래 NOT IN을 무조건 보존하는 치환은 아니다.

-- 왼쪽의 미정 id는 결과에서 제외하는 정책
SELECT c.id FROM candidates AS c
WHERE c.id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM blocked AS b WHERE b.id = c.id
  )
ORDER BY c.id;

-- 이번 SQLite에서 NULL과 NULL도 일치로 취급한 비교
SELECT c.id FROM candidates AS c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked AS b WHERE b.id IS c.id
)
ORDER BY c.id;

스키마를 보고 NULL이 없다고 가정하는 대안도 별도로 확인했다. INTEGER UNIQUE 열에 NULL을 두 번 넣는 삽입은 이 SQLite 실행에서 성공했고, NULL 행 수는 2였다. 이번 조건에서는 UNIQUE 선언만으로 NULL이 없다는 가정을 뒷받침할 수 없었다. 중복 제한과 NULL 허용 여부는 다른 질문으로 남겨야 한다. 이 삽입 실험을 다른 데이터베이스의 UNIQUE 정책에 대한 증거로 사용하지는 않는다.

이 실험이 말하지 않는 것

이번 결과는 SQLite 3.50.4의 스칼라 정수 열과 네 가지 작은 입력에 한정된다. 다른 데이터베이스, 복합 키, 문자열 정렬 규칙, 실제 데이터 분포, 실행 계획이나 속도는 검사하지 않았다. 공식 문서 두 페이지는 같은 SQLite 프로젝트의 의미 규칙을 설명하는 자료이며 독립된 두 연구가 아니다. 로컬 재현은 그 규칙과 이번 쿼리 결과를 대조한 별도의 관찰이지 성능 벤치마크가 아니다.

따라서 이 결과로 NOT EXISTS가 더 빠르다고 말할 수 없다. 먼저 결정할 수 있는 것은 왼쪽 NULL을 결과에 남길지, 오른쪽 NULL을 비교 대상에서 지울지, 두 NULL을 일치로 취급할지다. 이 세 조건을 정한 뒤 빈 오른쪽 집합까지 포함한 기대 행 배열로 수정 전후를 비교하면 된다. 이번 실험에서는 원래 NOT IN, 오른쪽 NULL 제거, 등호 기반 NOT EXISTS를 서로 다른 정책으로 다루는 결론을 택했다. 제외 목록에 없다는 짧은 문장부터 실제로 어떤 행을 뜻하는지 정해야 쿼리 수정의 성공을 판단할 수 있다.

Sources

[1] https://www.sqlite.org/lang_expr.html — SQLite expressions

[2] https://www.sqlite.org/lang_select.html — SQLite SELECT WHERE filtering