SOFTMOA TECHNOLOGY
SQLite NOT IN 조회 결과가 0건일 때 NULL 확인 방법
소프트모아가 정리한 기술 기록입니다.
Contents
SQLite에서 제외 목록을 NOT IN으로 조회했는데 오류 없이 0건이 나왔다면, 대상이 모두 제외됐다고 판단하기 전에 서브쿼리가 NULL을 반환하는지 확인해야 합니다. 일치하지 않는 값도 비교 결과가 참이 아닌 NULL이 되어 WHERE에서 빠질 수 있습니다. ‘같은 코드가 없는 행’을 찾으려면 NOT EXISTS를 쓰되, 조회 대상 자체의 코드가 없는 행을 포함할지도 따로 정하는 것이 핵심입니다.[1][2]
제외된 상품과 코드 미입력을 한 화면에서 구별하기
아래는 상품 코드로 제외 목록을 적용하는 가상 데이터입니다. 후보는 P10·P20·P30과 코드 미입력 행까지 4건, 제외 목록은 P20과 NULL 2건입니다. 실제 고객이나 운영 DB 자료가 아닙니다. 이 글에서는 코드가 확인된 상품만 후속 검토에 넘긴다는 조건을 둡니다. 따라서 기대 대상은 P10과 P30이며, 코드 미입력 행은 별도 확인 대상입니다.
검증 환경은 로컬 SQLite CLI 3.54.0입니다. 다음 두 SQL 블록을 순서대로 not-in-check.sql에 저장하고 아래 명령으로 실행합니다. :memory: 연결과 CTE의 VALUES만 사용하므로 기존 DB 파일이나 테이블을 변경하지 않습니다.[3]
sqlite3 --version
sqlite3 -header -column -nullvalue NULL :memory: < not-in-check.sql먼저 WHERE로 걸러 버리지 말고 각 후보의 조건값을 직접 출력합니다. not_in과 not_exists 열을 함께 보면 ‘실제로 제외 코드와 일치함’과 ‘판단값이 NULL임’을 구분할 수 있습니다.
WITH candidate(row_id, code) AS (
VALUES (1, 'P10'), (2, 'P20'), (3, 'P30'), (4, NULL)
), blocked(code) AS (
VALUES ('P20'), (NULL)
)
SELECT row_id, code,
code NOT IN (SELECT code FROM blocked) AS not_in,
NOT EXISTS (
SELECT 1 FROM blocked b WHERE b.code = c.code
) AS not_exists
FROM candidate c ORDER BY row_id;row_id code not_in not_exists
------ ---- ------ ----------
1 P10 NULL 1
2 P20 0 0
3 P30 NULL 1
4 NULL NULL 1P10이 제외 목록에 없어도 통과하지 않는 이유
P20은 제외 목록에서 일치하므로 not_in이 0입니다. 반면 P10과 P30은 일치하지 않지만, 오른쪽에 NULL이 있어 not_in이 NULL입니다. 이는 문법 오류가 아니라 확인할 수 없는 비교 결과입니다. WHERE는 참인 행만 남기고 거짓과 NULL인 행은 모두 제거하므로, 이 데이터에 원래 NOT IN 조건을 적용하면 결과가 0건입니다.[1][2]
NULL을 ‘빈 문자열’이나 ‘제외할 값이 없음’으로 읽으면 여기서 결론이 어긋납니다. 또 오른쪽에 NULL이 있다고 모든 계산값이 NULL인 것도 아닙니다. 이미 일치한 P20의 조건값은 거짓입니다. 위 출력처럼 반환된 행 수뿐 아니라 조건값을 확인해야 두 경우가 보입니다.[1]
not_exists는 비교식 b.code = c.code를 만족하는 행이 있는지를 검사합니다. 그런데 4번 후보의 코드도 NULL이므로 이 등호 조건을 만족하는 행이 없습니다. 따라서 NOT EXISTS만 WHERE에 넣으면 1·3·4번, 총 3건이 남습니다. 제외 목록의 NULL 문제를 피했다고 해서 미입력 코드까지 유효해진 것은 아닙니다.[1]
미입력은 보류하고 두 코드만 남기는 조회
이 예제의 조건에 맞게 왼쪽 코드의 존재 여부를 명시합니다. 앞 블록 뒤에 다음 SQL을 붙여 실행하면 됩니다. 상관 조건의 c.code를 빼먹으면 ‘현재 후보와 일치하는 행’이 아니라 제외 목록 전체의 존재 여부를 검사하게 되므로, 바깥 행과의 연결도 함께 점검합니다.
WITH candidate(row_id, code) AS (
VALUES (1, 'P10'), (2, 'P20'), (3, 'P30'), (4, NULL)
), blocked(code) AS (
VALUES ('P20'), (NULL)
)
SELECT c.row_id, c.code
FROM candidate c
WHERE c.code IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM blocked b WHERE b.code = c.code
)
ORDER BY c.row_id;row_id code
------ ----
1 P10
3 P30다른 선택은 NOT IN의 서브쿼리에 WHERE code IS NOT NULL을 넣는 것입니다. 이 데이터에서는 역시 P10과 P30이 나옵니다. 다만 SQLite는 오른쪽 결과가 빈 집합이면 왼쪽이 NULL이어도 NOT IN을 참으로 평가합니다. ‘코드 미입력은 항상 보류’가 조건이라면, 오른쪽 NULL 필터만 믿지 말고 바깥쪽 IS NOT NULL도 명시해야 합니다.[1]
- 상품 코드 없는 행을 미등록 목록에 포함해야 하는 업무라면, 위 보류 조건부터 바꿔야 합니다. 쿼리 교체보다 누락값의 의미를 먼저 합의합니다.
- 다중 열 비교, 문자열 대소문자나 공백 차이까지 이 예제가 해결하지는 않습니다. 실제 비교 열과 데이터 규칙으로 별도 검증해야 합니다.
- NOT EXISTS가 항상 더 빠르다는 결론은 아닙니다. 여기서는 결과의 의미만 검증했으며, 운영 데이터의 실행 계획이나 성능은 측정하지 않았습니다.
수정한 조회는 데이터를 고치지 않습니다. 다시 원래 NOT IN을 실행하면 여전히 0건이고, 후보의 미입력 1건과 제외 목록의 NULL 1건도 그대로입니다. 이 출력은 후속 검토 목록일 뿐 판매 승인이나 데이터 정정 결과가 아닙니다. 실제 시스템에서는 누락 코드를 확인할 담당자와 수정 이력을 따로 연결해야 합니다.
공식 문서 확인 범위
연산자의 NULL·빈 집합 규칙, WHERE의 필터링, CLI의 메모리 연결을 아래 문서로 대조했습니다. 예제와 출력은 이 글을 위해 만든 데이터를 로컬에서 실행한 결과입니다.
소프트모아의 서비스는 softmoa.com에서 확인할 수 있습니다.
소프트모아는 해당 시스템을 구축합니다. 문의하기
