2681 words
13 minutes
[SQL]Semi Join과 Anti Join
선행 글: [SQL]Join의 의미, [SQL]Join 알고리즘
실행 계획은 PostgreSQL 16.15 실측.
customers10만 행,orders100만 행(주문한 고객 9만 명, 주문 없는 고객 1만 명)
0. 한눈에 비교
| 구분 | Semi Join | Anti Join |
|---|---|---|
| 질문 | “상대 테이블에 있는 행은?” | “상대 테이블에 없는 행은?” |
| 반환 | 왼쪽 행만, 매칭 여러 건이어도 1번 | 왼쪽 행만, 매칭이 0건일 때 |
| SQL 표현 | EXISTS, IN | NOT EXISTS, LEFT JOIN … IS NULL, NOT IN(주의) |
| 실행 계획 노드 | Semi Join 또는 inner 중복 제거 후 Join | Anti Join |
| 대표 사례 | 구매 이력이 있는 고객, 활성 사용자 필터 | 미구매 고객, 신규 레코드 증분 적재, 고아 레코드 탐지 |
- 일반 JOIN과의 차이: 결과 행이 늘어나지 않음 — 오른쪽 컬럼을 가져오지 않고 존재 여부만 판단
- 일반 JOIN 후
DISTINCT로 중복을 지우는 패턴은 Semi Join으로 바꿀 수 있는 대표적인 안티패턴
Concept
- Semi Join : 오른쪽에 매칭되는 행이 하나라도 있으면 왼쪽 행을 한 번만 반환하는 조인. 오른쪽 컬럼은 결과에 포함되지 않아 행 증폭이 없음
- Anti Join : 오른쪽에 매칭되는 행이 하나도 없을 때만 왼쪽 행을 반환하는 조인. “없는 것 찾기”의 표준 연산
- 존재 검사(Existence Check) : 매칭 행의 값이 아니라 존재 여부만 판단하는 조건. 첫 매칭을 찾는 즉시 탐색을 멈출 수 있어 일반 조인보다 효율적
1. Semi Join
1.1 개념과 사용 상황
-- 주문한 적이 있는 고객SELECT c.customer_idFROM customers cWHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- 같은 의미SELECT c.customer_idFROM customers cWHERE 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 cWHERE 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 msorders100만 행 → 고유customer_id9만 개로 줄인 뒤 조인 → 결과가 1:1이 되어 Semi Join과 같은 의미
소량: Nested Loop Semi Join — 첫 매칭에서 바로 멈춤
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)SELECT c.customer_id FROM customers cWHERE 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_idFROM 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 cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
-- (B) LEFT JOIN ... IS NULLSELECT c.customer_id FROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.customer_id IS NULL;
-- (C) NOT IN ← 주의SELECT c.customer_id FROM customers cWHERE 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 msRight 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_idWHERE 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_idWHERE 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 cWHERE NOT EXISTS (SELECT 1 FROM bl WHERE bl.customer_id = c.customer_id)UNION ALLSELECT 'not_in', count(*) FROM customers cWHERE c.customer_id NOT IN (SELECT customer_id FROM bl);| k | count |
|---|---|
| not_exists | 99998 |
| not_in | 0 |
x NOT IN (1, 2, NULL)=x <> 1 AND x <> 2 AND x <> NULLx <> NULL은 항상 UNKNOWN → 전체가 TRUE가 될 수 없어 모든 행 탈락- 서브쿼리 컬럼이 nullable이면 NOT IN은 의미 자체가 달라짐
성능 함정: NULL 의미 때문에 PostgreSQL은 NOT IN을 Anti Join으로 바꾸지 못함
EXPLAIN (COSTS OFF) -- work_mem 기본값 4MBSELECT c.customer_id FROM customers cWHERE 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.nameFROM staging.customers sWHERE 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_idFROM orders oWHERE 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 cWHERE 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/