[MySQL] EXISTS와 IN 서브쿼리의 세미조인 변환 전략과 NULL 처리 차이

EXISTS와 IN: 실무적 정의와 운영상 중요성

MySQL에서 IN 서브쿼리와 EXISTS 서브쿼리는 모두 특정 값이 다른 테이블에 존재하는지를 확인할 때 사용하는 구문이다. IN은 서브쿼리가 반환하는 값 목록과 외부 컬럼 값을 비교하고, EXISTS는 서브쿼리가 한 건이라도 행을 반환하는지 여부만 확인한다. 두 구문은 표면적으로 다른 문법이지만, MySQL 옵티마이저는 내부적으로 이를 동일한 세미조인(Semi-Join) 실행 계획으로 변환하는 경우가 많다.

운영 환경에서는 두 구문이 항상 같은 결과를 낸다고 가정하는 실수가 자주 발생한다. 특히 서브쿼리 결과 컬럼에 NULL이 하나라도 포함되면 IN과 EXISTS, 그리고 이들의 부정형인 NOT IN과 NOT EXISTS의 결과가 서로 달라진다. 이 차이를 명확히 이해하지 못한 상태에서 조인 방식을 바꾸면, 데이터 정합성이 깨진 채로 배포되어 장애로 이어진다. DBA는 세미조인 변환 조건과 NULL 처리 방식을 함께 점검해야 한다.

Part 1. 기본 문법 및 사용법 (Basic Syntax)

IN 서브쿼리는 외부 쿼리의 컬럼 값이 서브쿼리가 반환하는 값 목록에 포함되는지를 검사한다.

SELECT o.id, o.status
FROM parent_orders o
WHERE o.id IN (
    SELECT r.order_id
    FROM child_refunds r
);

EXISTS 서브쿼리는 외부 쿼리와 서브쿼리를 상관(Correlated)시켜, 조건을 만족하는 행이 한 건이라도 존재하는지만 확인한다.

SELECT o.id, o.status
FROM parent_orders o
WHERE EXISTS (
    SELECT 1
    FROM child_refunds r
    WHERE r.order_id = o.id
);

부정형인 NOT IN과 NOT EXISTS도 동일한 구조를 가진다.

-- NOT IN
SELECT o.id FROM parent_orders o
WHERE o.id NOT IN (SELECT r.order_id FROM child_refunds r);

-- NOT EXISTS
SELECT o.id FROM parent_orders o
WHERE NOT EXISTS (
    SELECT 1 FROM child_refunds r WHERE r.order_id = o.id
);

문법상으로는 IN과 EXISTS, NOT IN과 NOT EXISTS가 서로 대체 가능한 것처럼 보인다. 그러나 실행 계획 수립 방식과 NULL 처리 방식은 명확히 다르다. 이 차이는 Part 2에서 다룬다.

Part 2. 내부 동작 메커니즘과 안티 패턴

MySQL 옵티마이저는 IN, EXISTS, ANY, SOME 서브쿼리를 조건에 따라 세미조인으로 변환한다. 세미조인은 외부 테이블의 각 행에 대해 서브쿼리 테이블에 일치하는 행이 존재하는지만 확인하고, 일치 여부가 확인되면 더 이상 탐색하지 않는 조인 방식이다. 세미조인이 적용되면 IN과 EXISTS는 대부분 동일한 실행 계획으로 수렴한다. optimizer_switch 시스템 변수의 semijoin=on 설정이 이 변환을 제어하며, MySQL 8.4에서는 기본적으로 활성화되어 있다.

세미조인은 내부적으로 다음 네 가지 전략 중 하나를 선택한다.

전략	동작 방식	적용 조건
Duplicate Weedout	임시 테이블로 중복 행을 제거하며 조인	서브쿼리 테이블에 고유 인덱스가 없을 때
FirstMatch	일치하는 첫 행만 찾고 탐색을 중단	상관 서브쿼리, EXISTS 변환의 기본 형태
LooseScan	인덱스를 이용해 중복 값을 건너뛰며 스캔	서브쿼리 컬럼에 인덱스가 있고 정렬 순서를 활용 가능할 때
Materialization	서브쿼리 결과를 임시 테이블로 구체화한 뒤 조인	비상관 서브쿼리, 결과 집합이 재사용될 때

EXPLAIN FORMAT=JSON으로 실행 계획을 확인하면 select#2가 별도 서브쿼리로 남지 않고 materialized-from-subquery 또는 first_match 같은 표시와 함께 상위 쿼리 블록에 병합된 것을 확인할 수 있다. 이 병합이 확인되면 IN과 EXISTS는 실질적으로 같은 세미조인 계획을 공유한다.

EXPLAIN FORMAT=JSON
SELECT o.id FROM parent_orders o
WHERE o.id IN (SELECT r.order_id FROM child_refunds r);

가장 치명적인 안티 패턴은 NOT IN 서브쿼리에 NULL을 허용하는 컬럼을 그대로 사용하는 것이다. SQL의 3값 논리(Three-Valued Logic)에서 NOT IN은 내부적으로 각 값과의 비교를 AND로 연결한 부정 비교와 동일하게 처리된다. 서브쿼리 결과 목록에 NULL이 단 하나라도 포함되면, 모든 비교 대상에 대해 결과가 UNKNOWN이 되어 WHERE 절 전체가 참이 되는 행이 하나도 남지 않는다. 결과적으로 NOT IN 조건은 서브쿼리에 NULL이 섞이는 순간 예외 없이 빈 결과 집합을 반환한다.

반면 NOT EXISTS는 값 비교가 아니라 상관 조건을 만족하는 행의 존재 여부만 확인하기 때문에 서브쿼리 컬럼의 NULL 값에 영향을 받지 않는다. 이 차이 때문에 운영 환경에서는 서브쿼리 컬럼이 NULL을 가질 수 있는 한, NOT IN과 NOT EXISTS는 절대 같은 결과를 보장하지 않는다. 대용량 트랜잭션 처리 배치나 리포트 쿼리에서 NOT IN을 NOT EXISTS로 무심코 바꾸거나 그 반대로 바꾸면, 아무 오류 메시지 없이 결과 건수가 0건으로 떨어지는 장애가 발생한다. DBA는 이 패턴을 코드 리뷰 단계에서 반드시 걸러야 한다.

Part 3. 성능 최적화 및 튜닝 전략

성능 최적화의 핵심은 세미조인 변환이 실제로 발생했는지를 EXPLAIN FORMAT=JSON과 옵티마이저 트레이스(optimizer trace)로 직접 확인하는 것이다. 옵티마이저 트레이스는 다음과 같이 활성화한다.

SET optimizer_trace = "enabled=on";
SELECT o.id FROM parent_orders o
WHERE EXISTS (SELECT 1 FROM child_refunds r WHERE r.order_id = o.id);
SELECT trace FROM information_schema.optimizer_trace;

트레이스 결과의 considered_execution_plans 항목에서 semijoin_strategy_choice를 확인하면 어떤 전략이 선택되었는지, 그리고 왜 다른 전략이 배제되었는지를 판단할 수 있다.

아래는 동일한 데이터 집합에 대해 IN, EXISTS, NOT IN, NOT EXISTS를 실행했을 때 확인해야 할 항목을 정리한 표다. 실제 값은 로컬 MySQL 8.4 환경에서 EXPLAIN FORMAT=JSON과 COUNT(*) 실행 결과로 채워야 한다.

튜닝 시 반드시 지켜야 할 원칙은 다음과 같다.

상황	권장 구문	이유
서브쿼리 컬럼에 NULL이 존재할 수 있음	NOT EXISTS 또는 서브쿼리에 IS NOT NULL 명시	NOT IN은 NULL 하나로 전체 결과가 소거됨
서브쿼리 컬럼이 NOT NULL 제약으로 보장됨	NOT IN, IN, EXISTS 모두 사용 가능	NULL로 인한 3값 논리 문제가 발생하지 않음
서브쿼리 결과가 대량이고 재사용됨	IN + Materialization 유도	임시 테이블을 1회 생성 후 재사용, 반복 스캔 방지
상관 조건 컬럼에 인덱스 존재	EXISTS + FirstMatch	인덱스 탐색으로 첫 일치 행만 확인, 조기 종료

서브쿼리 테이블의 상관 컬럼에는 반드시 인덱스를 생성해야 한다. 인덱스가 없으면 FirstMatch나 LooseScan 전략을 선택할 수 없고, 옵티마이저는 Duplicate Weedout으로 전환하며 임시 테이블을 생성한다. 이 임시 테이블이 메모리 상의 tmp_table_size를 초과하면 디스크로 스필되어 디스크 I/O 병목을 유발한다. 커버링 인덱스를 설계할 때는 상관 컬럼과 함께 서브쿼리가 실제로 참조하는 컬럼까지 포함해 테이블 접근 자체를 제거하는 방식을 검토해야 한다. 인덱스 설계 기준은 커버링 인덱스 설계: 테이블 접근 제거 조건과 과도한 인덱스 비용 글에서 다룬 접근 제거 조건을 그대로 적용할 수 있다.

또한 옵티마이저가 세미조인 대신 전체 테이블 스캔(Full Table Scan)을 선택하는 경우도 있다. 서브쿼리 결과 집합의 선택도가 낮게 추정되거나, 통계 정보가 오래되어 행 수 추정치가 왜곡된 경우다. 이때는 ANALYZE TABLE로 통계를 갱신하거나 히스토그램을 재구성해 추정치를 교정해야 한다. 낮은 선택도 인덱스에서 Full Table Scan이 정상적인 선택인지 여부를 판단하는 기준은 낮은 선택도 인덱스에서 Full Table Scan을 선택하는 기준과 검증 방법 글에서, 편향된 분포를 가진 컬럼의 추정치를 교정하는 방법은 히스토그램 통계로 편향 컬럼의 행 수 추정치를 교정하는 방법 글에서 다룬다.

정리하면, IN과 EXISTS는 서브쿼리에 NULL이 없고 세미조인 변환이 정상적으로 이루어질 때만 동일한 실행 계획과 결과를 보장한다. NOT IN은 서브쿼리 컬럼에 NULL이 하나라도 섞이면 예외 없이 빈 결과를 반환하므로, NULL 가능성이 있는 컬럼에는 NOT EXISTS를 사용하거나 IS NOT NULL 조건을 서브쿼리에 명시해야 한다. 운영 배포 전에는 반드시 EXPLAIN FORMAT=JSON으로 세미조인 전략을 확인하고, 실제 데이터에 NULL이 포함된 상태에서 결과 건수를 검증하는 절차를 거쳐야 한다.