NOT IN과 NOT EXISTS는 같은 결과를 낸다고 알려져 있다. 실제로 대부분의 경우 그렇다. 그런데 비교 대상에 NULL이 하나라도 섞이면 NOT IN은 아무 행도 반환하지 않는다. 에러도 경고도 없이 0건이 나온다.
왜 0건이 되는가
SQL의 비교 연산은 참과 거짓 외에 UNKNOWN이라는 상태를 갖는다. NULL과의 비교 결과가 UNKNOWN이다. NOT IN은 내부적으로 이렇게 풀린다.
-- 이 조건은
where cust_id not in (10, 20, null)
-- 이렇게 해석된다
where cust_id != 10 and cust_id != 20 and cust_id != null마지막 cust_id != null은 언제나 UNKNOWN이다. AND로 묶인 조건에 UNKNOWN이 하나 있으면 전체가 참이 될 수 없다. 참이 아니면 행은 걸러진다. 그래서 어떤 행도 남지 않는다.
NOT EXISTS는 다르게 동작한다. 서브쿼리에 행이 존재하는지만 보기 때문에 NULL의 영향을 받지 않는다.
-- 이건 정상 동작한다
select * from customers c
where not exists (
select 1 from blacklist b where b.cust_id = c.cust_id
);재현해 보기
다음은 설명을 위한 예시다.
create table customers (cust_id number, cust_nm varchar2(50));
create table blacklist (cust_id number);
insert into customers values (10, 'A');
insert into customers values (20, 'B');
insert into customers values (30, 'C');
insert into blacklist values (10);
insert into blacklist values (null);
commit;-- 0건
select * from customers
where cust_id not in (select cust_id from blacklist);
-- 20, 30 두 건
select * from customers c
where not exists (
select 1 from blacklist b where b.cust_id = c.cust_id
);블랙리스트가 아닌 고객을 뽑는 쿼리인데, NULL 한 건 때문에 전원이 블랙리스트 취급을 받는다. 반대 방향인 IN은 이런 문제가 없다. OR로 묶이기 때문에 참인 조건이 하나만 있으면 된다.
실무에서 드러나는 양상
이 문제가 까다로운 이유는 처음에는 잘 돌기 때문이다. 개발 시점에 블랙리스트 테이블에 NULL이 없으면 정상 동작한다. 운영 중 어느 날 NULL이 한 건 들어오는 순간 결과가 0건으로 바뀐다.
그리고 에러가 안 난다. 화면에는 “조회 결과가 없습니다”가 뜨고, 배치는 0건 처리로 정상 종료한다. 데이터가 없는 것과 구분되지 않는다.
서브쿼리 컬럼이 NOT NULL이면 안전하지만, 조인이나 외부 유입 데이터를 거치면 NOT NULL 보장이 쉽게 깨진다. 특히 인터페이스 테이블처럼 외부에서 들어오는 데이터를 대상으로 쓸 때 위험하다.
대응
1. NOT EXISTS를 기본으로 쓴다. 가장 단순한 원칙이다. NOT IN이 반드시 필요한 상황은 드물고, NOT EXISTS는 NULL에 영향을 받지 않는다.
2. NOT IN을 써야 한다면 NULL을 배제한다.
select * from customers
where cust_id not in (
select cust_id from blacklist where cust_id is not null
);3. 컬럼에 NOT NULL 제약을 건다. 애초에 NULL이 못 들어오게 하는 것이 근본적이다.
성능은 어떤가
예전에는 NOT IN이 느리고 NOT EXISTS가 빠르다는 말이 있었다. 오라클 11g 이후의 옵티마이저는 두 형태를 모두 안티조인(Anti Join)으로 변환할 수 있어서, 조건이 같다면 실행계획도 대체로 같게 나온다.
-- 실행계획에서 이런 오퍼레이션이 보이면 안티조인으로 처리된 것이다
-- HASH JOIN ANTI
-- NESTED LOOPS ANTI다만 서브쿼리 컬럼이 NULL을 허용하면 오라클은 HASH JOIN ANTI NA(Null Aware) 같은 형태를 쓴다. NULL 가능성을 고려해야 하므로 일반 안티조인보다 제약이 생긴다. NOT NULL 제약이 걸려 있으면 옵티마이저가 더 나은 선택을 할 수 있다는 뜻이다.
즉 NOT NULL 제약은 정확성뿐 아니라 성능에도 도움이 된다. 제약은 문서가 아니라 옵티마이저가 읽는 정보다.
같은 뿌리의 함정 하나
NULL이 만드는 혼란은 집계 함수에서도 나온다.
select count(*), count(close_dt) from orders;count(*)는 전체 행 수를 세지만 count(close_dt)는 NULL이 아닌 행만 센다. 이 차이를 이용하면 미처리 건수를 한 번에 구할 수 있다.
select count(*) - count(close_dt) as not_closed from orders;SUM이나 AVG도 NULL을 빼고 계산한다. AVG를 쓸 때 분모가 전체 행 수가 아니라는 점은 집계가 미묘하게 틀리는 흔한 원인이다.
정리
NOT IN은 비교 대상에 NULL이 하나라도 있으면 결과가 0건이 된다. 에러가 나지 않고, 데이터가 없는 것과 구분되지 않으며, 운영 중 NULL이 들어오는 날 갑자기 나타난다.
실무 원칙은 세 줄로 요약된다. 부정 조건은 NOT EXISTS로 쓴다. NOT IN을 쓸 거면 서브쿼리에 IS NOT NULL을 붙인다. 가능하면 컬럼에 NOT NULL 제약을 건다.