2681 words
13 minutes
[SQL]Semi Join과 Anti Join

선행 글: [SQL]Join의 의미, [SQL]Join 알고리즘

실행 계획은 PostgreSQL 16.15 실측. customers 10만 행, orders 100만 행(주문한 고객 9만 명, 주문 없는 고객 1만 명)

0. 한눈에 비교#

구분Semi JoinAnti Join
질문“상대 테이블에 있는 행은?”“상대 테이블에 없는 행은?”
반환왼쪽 행만, 매칭 여러 건이어도 1번왼쪽 행만, 매칭이 0건일 때
SQL 표현EXISTS, INNOT EXISTS, LEFT JOIN … IS NULL, NOT IN(주의)
실행 계획 노드Semi Join 또는 inner 중복 제거 후 JoinAnti Join
대표 사례구매 이력이 있는 고객, 활성 사용자 필터미구매 고객, 신규 레코드 증분 적재, 고아 레코드 탐지
  • 일반 JOIN과의 차이: 결과 행이 늘어나지 않음 — 오른쪽 컬럼을 가져오지 않고 존재 여부만 판단
  • 일반 JOIN 후 DISTINCT로 중복을 지우는 패턴은 Semi Join으로 바꿀 수 있는 대표적인 안티패턴

Concept

  • Semi Join : 오른쪽에 매칭되는 행이 하나라도 있으면 왼쪽 행을 한 번만 반환하는 조인. 오른쪽 컬럼은 결과에 포함되지 않아 행 증폭이 없음
  • Anti Join : 오른쪽에 매칭되는 행이 하나도 없을 때만 왼쪽 행을 반환하는 조인. “없는 것 찾기”의 표준 연산
  • 존재 검사(Existence Check) : 매칭 행의 값이 아니라 존재 여부만 판단하는 조건. 첫 매칭을 찾는 즉시 탐색을 멈출 수 있어 일반 조인보다 효율적

1. Semi Join#

1.1 개념과 사용 상황#

-- 주문한 적이 있는 고객
SELECT c.customer_id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- 같은 의미
SELECT c.customer_id
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);
  • 사용 상황
    • “조건을 만족하는 하위 데이터가 있는 상위 엔티티” 필터 (주문 있는 고객, 에러 로그가 있는 배치 잡)
    • 1 관계에서 1쪽 grain을 유지한 채 N쪽 조건으로 거를 때
  • PostgreSQL에서 EXISTS와 IN (subquery)는 둘 다 Semi Join으로 변환되어 보통 같은 계획이 나옴

1.2 실행 계획: 데이터 크기에 따라 형태가 달라짐#

대량: inner를 먼저 중복 제거(unique-ify) 한 뒤 일반 Hash Join

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT c.customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Hash Join (actual rows=90000 loops=1)
Hash Cond: (c.customer_id = o.customer_id)
-> Seq Scan on customers c (actual rows=100000 loops=1)
-> Hash (actual rows=90000 loops=1)
-> HashAggregate (actual rows=90000 loops=1) -- orders.customer_id 중복 제거
Group Key: o.customer_id
-> Seq Scan on orders o (actual rows=1000000 loops=1)
Execution Time: 136.434 ms
  • orders 100만 행 → 고유 customer_id 9만 개로 줄인 뒤 조인 → 결과가 1:1이 되어 Semi Join과 같은 의미

소량: Nested Loop Semi Join — 첫 매칭에서 바로 멈춤

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT c.customer_id FROM customers c
WHERE c.customer_id <= 5
AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Nested Loop Semi Join (actual rows=5 loops=1)
-> Index Only Scan using customers_pkey on customers c (actual rows=5 loops=1)
-> Index Only Scan using idx_orders_customer on orders o (actual rows=1 loops=5)
Execution Time: 0.019 ms
  • 고객당 주문은 약 11건이지만 inner rows=1 → 첫 번째 매칭만 확인하고 다음 고객으로 넘어감

1.3 안티패턴: JOIN + DISTINCT#

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT DISTINCT c.customer_id
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;
HashAggregate (actual rows=90000 loops=1)
Group Key: c.customer_id
-> Hash Join (actual rows=1000000 loops=1) -- 100만 행으로 증폭 후
... -- 다시 9만 행으로 축소
Execution Time: 257.114 ms
  • 결과는 같지만 중간에 100만 행으로 증폭했다가 DISTINCT로 줄이는 낭비 (EXISTS 136ms vs 257ms)
  • 오른쪽 컬럼이 필요 없다면 EXISTS로 의도를 드러내는 것이 성능·가독성 모두 유리

Concept

  • EXISTS : 서브쿼리가 한 행이라도 반환하면 TRUE인 술어. 반환 값은 보지 않으므로 SELECT 1이 관례이며 Semi Join으로 변환됨
  • Unique-ify 전략 : Semi Join을 inner 쪽 중복 제거 후 일반 조인으로 수행하는 실행 방식. EXPLAIN에서 inner 아래 HashAggregate/Unique 노드로 나타남
  • Early Termination : Semi/Anti Join에서 첫 매칭을 찾으면 해당 outer 행의 탐색을 즉시 멈추는 특성. Nested Loop Semi Join의 inner rows=1로 확인
  • JOIN + DISTINCT 안티패턴 : 존재 여부만 필요한데 일반 조인으로 행을 증폭시킨 뒤 DISTINCT로 되돌리는 패턴. EXISTS로 바꾸면 중간 결과 증폭이 사라짐

2. Anti Join#

2.1 세 가지 작성법#

-- (A) NOT EXISTS ← 권장
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- (B) LEFT JOIN ... IS NULL
SELECT c.customer_id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
-- (C) NOT IN ← 주의
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
  • 사용 상황
    • 미구매 고객, 미접속 사용자 등 “없는 것” 찾기
    • 증분 적재: 타깃에 아직 없는 키만 INSERT
    • 참조 무결성 점검: 부모가 없는 고아(orphan) 자식 레코드 탐지

2.2 NOT EXISTS — Hash Anti Join#

Hash Right Anti Join (actual rows=10000 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (actual rows=1000000 loops=1)
-> Hash (actual rows=100000 loops=1)
-> Seq Scan on customers c (actual rows=100000 loops=1)
Execution Time: 94.919 ms
  • Right Anti: 해시를 왼쪽(customers) 으로 만들고, orders를 probe하며 매칭된 고객을 지워나간 뒤 남은 고객을 반환 (PG 16+)
  • 중간 결과 증폭 없이 1만 행만 출력

2.3 LEFT JOIN IS NULL — 어떤 컬럼을 검사하느냐에 따라 계획이 다름#

조인 키로 검사 → Anti Join으로 변환됨

... LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;
Hash Right Anti Join (actual rows=10000 loops=1)
Execution Time: 111.798 ms

조인 키가 아닌 컬럼(PK여도)으로 검사 → 일반 OUTER JOIN 후 필터

... LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
Hash Right Join (actual rows=10000 loops=1)
Filter: (o.order_id IS NULL)
Rows Removed by Filter: 1000000 -- 100만 행 조인 결과를 만든 뒤 버림
Execution Time: 141.944 ms
  • PostgreSQL 16은 IS NULL 대상이 조인 조건에 쓰인 컬럼일 때만 “매칭이 없었다”는 뜻으로 확신하고 Anti Join으로 바꿈
  • 의도를 명확히 하려면 NOT EXISTS가 가장 안전

2.4 NOT IN — NULL 함정과 성능 함정#

NULL 함정: 서브쿼리에 NULL이 하나라도 있으면 결과가 0행

CREATE TEMP TABLE bl(customer_id int);
INSERT INTO bl VALUES (1), (2), (NULL);
SELECT 'not_exists', count(*) FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM bl WHERE bl.customer_id = c.customer_id)
UNION ALL
SELECT 'not_in', count(*) FROM customers c
WHERE c.customer_id NOT IN (SELECT customer_id FROM bl);
kcount
not_exists99998
not_in0
  • x NOT IN (1, 2, NULL) = x <> 1 AND x <> 2 AND x <> NULL
  • x <> NULL은 항상 UNKNOWN → 전체가 TRUE가 될 수 없어 모든 행 탈락
  • 서브쿼리 컬럼이 nullable이면 NOT IN은 의미 자체가 달라짐

성능 함정: NULL 의미 때문에 PostgreSQL은 NOT IN을 Anti Join으로 바꾸지 못함

EXPLAIN (COSTS OFF) -- work_mem 기본값 4MB
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
Seq Scan on customers c
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize
-> Seq Scan on orders o
  • 서브쿼리 결과가 work_mem에 안 들어가면 고객 한 명마다 orders 100만 행을 다시 훑음 → 10만 × 100만, 실제로 5분 넘게 끝나지 않아 중단함
  • work_mem = '64MB'로 올리면 hashed SubPlan으로 바뀌어 296ms에 끝나지만, 데이터가 커지면 같은 절벽을 다시 만남
Seq Scan on customers c (actual rows=10000 loops=1)
Filter: (NOT (hashed SubPlan 1))
  • 결론: Anti Join은 NOT EXISTS로 작성. NOT IN은 상수 리스트(NOT IN (1, 2, 3))에만 사용

Concept

  • NOT EXISTS : 서브쿼리가 한 행도 반환하지 않을 때 TRUE인 술어. NULL과 무관하게 동작하고 Anti Join으로 변환되어 Anti Join의 표준 작성법
  • LEFT JOIN IS NULL 패턴 : 외부 조인 후 오른쪽이 NULL 확장된 행만 남겨 Anti Join을 흉내 내는 방식. 검사 컬럼이 조인 키가 아니면 옵티마이저가 Anti Join으로 변환하지 못할 수 있음
  • NOT IN의 NULL 함정 : 서브쿼리 결과에 NULL이 하나라도 있으면 x <> NULL이 UNKNOWN이 되어 전체 결과가 0행이 되는 현상. 삼값 논리의 직접적인 결과
  • Hashed SubPlan vs SubPlan : 서브쿼리 결과를 해시 테이블로 한 번 만들어 재사용하는지(hashed), 외부 행마다 재실행하는지의 차이. 결과가 work_mem을 넘으면 재실행 방식으로 떨어져 O(N×M)
  • Hash Right Anti Join : 왼쪽(보존 대상) 테이블로 해시를 만들고 오른쪽을 probe하며 매칭된 행을 제거하는 Anti Join 변형(PostgreSQL 16+). 왼쪽이 더 작을 때 선택됨

3. 데이터 엔지니어링에서의 활용#

3.1 증분 적재: 타깃에 없는 키만 INSERT#

INSERT INTO dw.dim_customer (customer_id, name)
SELECT s.customer_id, s.name
FROM staging.customers s
WHERE NOT EXISTS (
SELECT 1 FROM dw.dim_customer d WHERE d.customer_id = s.customer_id
);
  • 재실행해도 이미 들어간 키는 제외되므로 멱등성 확보
  • PostgreSQL 15+ MERGE, 또는 INSERT ... ON CONFLICT DO NOTHING(유니크 제약 필요)과 같은 목적

3.2 참조 무결성 점검: 고아 레코드 탐지#

-- 존재하지 않는 고객을 참조하는 주문
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
  • FK 제약이 없는 DW/레이크하우스에서 데이터 품질 테스트로 사용 (dbt relationships 테스트가 같은 원리)

3.3 Semi Join으로 grain 유지#

-- 고객 grain을 유지한 채 "최근 30일 내 주문이 있는" 고객만
SELECT c.*
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.ordered_at >= current_date - 30
);
  • 일반 JOIN을 쓰면 주문 수만큼 고객 행이 늘어나 이후 집계가 왜곡됨 (Join의 의미 - Fan-out 참고)

Concept

  • 증분 적재(Incremental Load) : 이미 적재된 데이터를 제외하고 새로 생긴 데이터만 적재하는 방식. NOT EXISTS Anti Join으로 신규 키를 골라 재실행에도 안전하게 만듦
  • 고아 레코드(Orphan Record) : 참조 대상(부모)이 존재하지 않는 자식 레코드. FK 제약이 없는 분석 환경에서는 Anti Join 기반 품질 테스트로 탐지
  • Grain 보존 필터 : 하위 테이블 조건으로 상위 테이블을 거르되 상위 행 수는 유지하는 필터링. 일반 JOIN 대신 Semi Join(EXISTS)을 써야 Fan-out이 생기지 않음

추가적으로 공부하면 좋은 컨셉, 주제들#

  • MERGE 문 : 매칭 여부에 따라 INSERT/UPDATE/DELETE를 한 번에 처리하는 SQL 표준 구문 (PostgreSQL 15+)
  • INSERT … ON CONFLICT : 유니크 제약 충돌 시 무시하거나 갱신하는 PostgreSQL 업서트 구문
  • EXCEPT : 집합 차집합 연산. Anti Join과 비슷하지만 전체 컬럼 비교 + 중복 제거 의미가 있음
  • Spark LEFT SEMI / LEFT ANTI JOIN : 분산 엔진에서 Semi/Anti Join을 명시적인 조인 타입으로 제공하는 방식
[SQL]Semi Join과 Anti Join
https://yjinheon.netlify.app/posts/02de/06-sql/de-sql-semi-anti-join/
Author
Datamind
Published at
2026-10-24
License
CC BY-NC-SA 4.0