답변.

질문의 배경

종종 필터링 기준이 동적으로 결정되는 쿼리를 작성해야 합니다. 값 목록이 사용자 인터페이스에 의해 지정되고 누락된 값(NULL)도 결과에 포함되거나 제외될 수 있어야 합니다. 이러한 쿼리는 대량의 데이터에서 빠르게 실행되어야 하고 SQL 의미론적으로 올바르며 작동해야 합니다.

문제

IN, NOT IN 조건 및 NULL을 체크하는 조건을 부적절하게 조합하면 예기치 않은 결과가 발생할 수 있습니다. SQL에서 표현식 "NULL IN (...)"은 항상 UNKNOWN을 반환하고, "NOT IN"은 목록의 요소 중 하나라도 NULL인 경우 빈 결과를 초래할 수 있습니다. 잘못 작성된 조건에서 대규모 데이터의 성능이 급격히 떨어질 수 있습니다.

해결책

올바른 조합은 다음과 같습니다:

목록에서 값을 선택할 때 NULL을 반환해야 하는 경우 NULL 조건을 추가합니다:

SELECT * FROM sales
WHERE region IN ('Moscow','Samara') OR region IS NULL;

부정을 위해서는 명시적인 논리를 사용합니다:

SELECT * FROM sales
WHERE (region NOT IN ('Moscow','Samara') OR region IS NULL);

누락된 값을 완전히 배제하려면:

SELECT * FROM sales
WHERE region NOT IN ('Moscow','Samara') AND region IS NOT NULL;

대규모 테이블에서는 목록 필터링이 자주 발생하므로 인덱스가 필수적입니다.

주요 특징:

NULL에 대한 필터링은 명시적으로 관리해야 하며, 그렇지 않으면 행을 잃게 됩니다.
IN 및 NOT IN의 올바른 사용이 올바른 선택에 매우 중요합니다.
IN/NOT IN에 사용되는 필드에 대한 인덱스는 필터를 크게 가속화합니다.

함정 질문.

"WHERE field NOT IN ('a', NULL)"를 체크하면 무엇이 발생하나요?

결과는 항상 비어 있습니다. 각 행이 목록의 NULL과 비교되므로 UNKNOWN이 발생하고 모든 것이 필터링됩니다.

코드 예:

SELECT * FROM test WHERE name NOT IN ('Ivan', NULL);

"IN (NULL,...)"가 NULL이 있는 행에 값을 제공하나요?

아니요. NULL IN (...) 표현식은 항상 UNKNOWN을 반환하며 TRUE가 아닙니다.

대규모 값 목록에 대한 필터링 속도를 높이는 방법은?

인덱스를 사용하고 목록이 크면(수백/수천 값) 임시 테이블과 JOIN을 사용하는 것이 바람직합니다. 이는 긴 IN보다 필터링 속도를 높입니다.

일반적인 오류 및 안티 패턴

NULL에 대한 명시적인 조건 없이 IN/NOT IN만 사용하기
NULL과 함께 NOT IN의 동작 특성을 고려하지 않기
필터링 필드에 대한 인덱스 최적화하지 않기

생활 사례

부정적인 사례

분석 보고서에서 "WHERE city NOT IN ('London', field_2, NULL)"를 적용했습니다. 그 결과 — 완전히 비어 있는 결과, 아무도 잃어버린 행의 원인을 오랫동안 이해하지 못했습니다.

장점:

짧고 "읽기 쉬운" 쿼리

단점:

데이터 손실, 개발자들에게 명확하지 않은 논리

긍정적인 사례

CRM에서 고객 지역 목록을 선택하고 "누락된 값을 포함" 버튼을 추가했습니다. 필터의 논리:

... WHERE city IN ('SPB','NNov') OR city IS NULL