Oracle NOT IN 조건에 NULL이 포함되면 조회 결과가 나오지 않는 이유

Oracle에서 NOT IN 조건을 사용할 때 목록이나 서브쿼리 결과에 NULL이 포함되면 예상과 전혀 다른 조회 결과가 나올 수 있다.

실무에서는 서브쿼리로 제외 대상을 조회한 뒤 NOT IN으로 필터링하는 경우가 많은데, 서브쿼리 결과에 NULL 하나가 섞여 전체 결과가 0건이 되는 상황도 발생한다.

이번 글에서는 왜 이런 현상이 발생하는지 SQL의 NULL 처리 방식과 함께 정리한다.

---

1. 문제 상황

다음과 같은 테이블이 있다고 가정한다.

CREATE TABLE EMP_TEST (
    EMP_ID NUMBER,
    EMP_NAME VARCHAR2(50)
);

INSERT INTO EMP_TEST VALUES (1, 'KIM');
INSERT INTO EMP_TEST VALUES (2, 'LEE');
INSERT INTO EMP_TEST VALUES (3, 'PARK');
INSERT INTO EMP_TEST VALUES (4, 'CHOI');

일반적인 IN 조건은 다음과 같다.

SELECT *
FROM EMP_TEST
WHERE EMP_ID IN (1, 2, 3);

결과는 예상대로 EMP_ID가 1, 2, 3인 데이터가 조회된다.

EMP_ID  EMP_NAME
------  --------
1       KIM
2       LEE
3       PARK

여기까지는 특별한 문제가 없다.

---

2. NULL이 포함되면 어떻게 될까?

예를 들어 다음 조건을 생각해볼 수 있다.

SELECT *
FROM EMP_TEST
WHERE EMP_ID IN (1, 2, NULL);

NULL이 들어갔다고 해서 전체 조회가 실패하는 것은 아니다.

EMP_ID가 1 또는 2이면 이미 조건이 TRUE가 되므로 해당 데이터는 정상적으로 조회된다.

문제가 크게 발생하는 것은 NOT IN에 NULL이 포함되는 경우이다.

SELECT *
FROM EMP_TEST
WHERE EMP_ID NOT IN (1, 2, NULL);

직관적으로 생각하면 다음 결과를 예상하기 쉽다.

3 PARK
4 CHOI

하지만 실제로는 조회 결과가 0건이 된다.

왜 이런 현상이 발생할까?

---

3. 원인: Oracle의 NULL은 '값이 없음'이 아니라 '알 수 없음'

SQL에서 NULL은 일반적인 값처럼 비교할 수 없다.

예를 들어 다음 조건은 TRUE가 아니다.

3 = NULL

마찬가지로 다음 조건 역시 TRUE가 아니다.

3 <> NULL

SQL에서 NULL과의 비교 결과는 TRUE 또는 FALSE가 아니라 UNKNOWN이 된다.

따라서 NULL 비교는 다음과 같이 해야 한다.

IS NULL

또는

IS NOT NULL

---

4. NOT IN을 풀어서 보면 원인이 보인다

다음 SQL을 살펴보자.

WHERE EMP_ID NOT IN (1, 2, NULL)

이를 논리적으로 풀어보면 다음과 비슷하다.

WHERE EMP_ID <> 1
  AND EMP_ID <> 2
  AND EMP_ID <> NULL

예를 들어 EMP_ID = 3이라면 다음과 같다.

3 <> 1       → TRUE
3 <> 2       → TRUE
3 <> NULL    → UNKNOWN

결국 조건은 다음과 같다.

TRUE AND TRUE AND UNKNOWN

SQL의 3-valued logic에서는 이 결과가 TRUE가 아니다.

WHERE 절은 최종 결과가 TRUE인 행만 반환하기 때문에 해당 행은 조회되지 않는다.

EMP_ID = 4 역시 마찬가지이다.

결과적으로 모든 데이터가 제거되면서 0건이 조회되는 현상이 발생한다.

---

5. IN과 NOT IN의 차이

IN에서도 NULL은 UNKNOWN을 발생시킨다.

WHERE EMP_ID IN (1, 2, NULL)

논리적으로 보면 다음과 같다.

WHERE EMP_ID = 1
   OR EMP_ID = 2
   OR EMP_ID = NULL

EMP_ID = 1인 경우에는

TRUE OR FALSE OR UNKNOWN

이미 하나의 조건이 TRUE이므로 최종 결과도 TRUE가 된다.

따라서 1번 데이터는 정상적으로 조회된다.

반면 NOT IN은 내부적으로 AND 조건처럼 동작하기 때문에 NULL 하나가 전체 조건에 영향을 줄 수 있다.

정리하면 다음과 같다.

 

조건	NULL 포함 시 영향
IN (1, 2, NULL)	1, 2 등 일치하는 값은 정상 조회
NOT IN (1, 2, NULL)	NULL 때문에 전체 결과가 조회되지 않을 수 있음
= NULL	정상적인 NULL 비교가 아님
<> NULL	정상적인 NULL 비교가 아님
IS NULL	NULL 여부 정상 확인
IS NOT NULL	NULL이 아닌 값 정상 확인

---

6. 실무에서 더 위험한 경우: 서브쿼리

실제 개발에서는 값을 직접 작성하는 것보다 다음과 같은 SQL에서 문제가 발생할 가능성이 높다.

SELECT *
FROM EMP_TEST A
WHERE A.EMP_ID NOT IN (
    SELECT B.EMP_ID
    FROM EXCLUDE_EMP B
);

EXCLUDE_EMP의 데이터가 다음과 같다고 가정한다.

EMP_ID
------
1
2
NULL

개발자는 보통 "1번과 2번 직원을 제외해야겠다"고 생각한다.

하지만 서브쿼리 결과에 NULL이 포함되어 있기 때문에 실제 SQL은 사실상 다음 조건이 된다.

WHERE A.EMP_ID NOT IN (1, 2, NULL)

그 결과 예상했던 3번, 4번 직원까지 조회되지 않는다.

이 문제가 까다로운 이유는 SQL 자체에는 문법 오류가 없고 에러도 발생하지 않는다는 점이다.

단순히 결과가 0건으로 나오기 때문에 데이터 문제나 JOIN 문제로 오해하기 쉽다.

---

7. 해결 방법 1: NULL 제거

NOT IN을 유지해야 한다면 서브쿼리에서 NULL을 확실하게 제거해야 한다.

SELECT *
FROM EMP_TEST A
WHERE A.EMP_ID NOT IN (
    SELECT B.EMP_ID
    FROM EXCLUDE_EMP B
    WHERE B.EMP_ID IS NOT NULL
);

이렇게 하면 서브쿼리 결과가 다음처럼 된다.

1
2

따라서 정상적으로 3, 4번 데이터가 조회된다.

---

8. 해결 방법 2: NOT EXISTS 사용

실무에서는 NULL 가능성이 있는 서브쿼리를 이용해 제외 조건을 만들 경우 NOT EXISTS를 사용하는 것도 좋은 방법이다.

SELECT *
FROM EMP_TEST A
WHERE NOT EXISTS (
    SELECT 1
    FROM EXCLUDE_EMP B
    WHERE B.EMP_ID = A.EMP_ID
);

NOT EXISTS는 특정 값이 NULL인지 직접 비교하는 방식이 아니라 조건을 만족하는 행이 존재하는지 여부를 확인한다.

따라서 NOT IN에서 발생하는 NULL 문제를 피할 수 있다.

특히 다음과 같은 형태라면 NOT EXISTS를 우선적으로 검토할 만하다.

SELECT *
FROM A
WHERE NOT EXISTS (
    SELECT 1
    FROM B
    WHERE B.ID = A.ID
);

---

9. IN 자체가 문제인 것은 아니다

여기서 주의할 부분이 있다.

단순히

"IN 절에 NULL이 들어가면 전체 결과가 안 나온다"

라고 이해하면 정확하지 않다.

다음 SQL은 정상적으로 결과가 나온다.

SELECT *
FROM EMP_TEST
WHERE EMP_ID IN (1, 2, NULL);

실무에서 치명적인 문제를 일으키는 대표적인 경우는 다음이다.

NOT IN (..., NULL)

따라서 정확하게 표현하면 다음과 같다.

Oracle에서 NOT IN 조건의 비교 대상에 NULL이 포함되면 SQL의 3-valued logic에 의해 조건이 UNKNOWN이 되어 예상과 달리 전체 결과가 조회되지 않을 수 있다.

---

10. 정리

Oracle에서 NULL은 일반적인 값이 아니라 UNKNOWN(알 수 없는 값)​으로 처리된다.

따라서 다음과 같은 비교는 주의해야 한다.

COLUMN = NULL
COLUMN <> NULL
COLUMN NOT IN (..., NULL)

NULL 여부를 직접 확인하려면 반드시 다음을 사용해야 한다.

COLUMN IS NULL
COLUMN IS NOT NULL

특히 서브쿼리를 사용하는 NOT IN은 반드시 서브쿼리 결과에 NULL이 들어올 가능성을 확인해야 한다.

WHERE ID NOT IN (
    SELECT ID
    FROM TARGET
    WHERE ID IS NOT NULL
);

또는 요구사항에 따라 NOT EXISTS로 변경하는 방법을 검토할 수 있다.

WHERE NOT EXISTS (
    SELECT 1
    FROM TARGET B
    WHERE B.ID = A.ID
);

실무에서 NOT IN 조건을 작성했는데 에러는 없는데 결과가 갑자기 0건으로 나온다면, 가장 먼저 서브쿼리 결과에 NULL이 포함되어 있는지 확인해보는 것이 좋다.

---

핵심 한 줄

NOT IN (1, 2, NULL)

은 단순히 "1, 2를 제외한다"는 의미로 동작하지 않는다.

NOT IN의 대상에 NULL이 하나라도 포함되면 전체 조회 결과가 사라질 수 있으므로 반드시 NULL 여부를 확인해야 한다.

---