semi-join과 anti-join은 매칭 여부만 보므로 INNER JOIN과 달리 행이 불어나지 않는다

요약

semi-join은 매칭이 있는 행만, anti-join은 매칭이 없는 행만 남긴다. 둘 다 오른쪽 테이블의 값을 결과에 쓰지 않는다.
존재 여부만 보므로 매칭이 N건이어도 왼쪽 행은 한 번만 나온다. INNER JOIN처럼 DISTINCT를 붙일 일이 없다.
SQL 키워드가 아니라 옵티마이저 개념이다. NOT EXISTS / NOT IN / LEFT JOIN + IS NULL 세 가지로 표현하며, 논리적 결과는 같고 실행 계획만 달라진다.

본문

정의

MySQL 문서의 표현이 정확하다.

A semijoin is an operation that returns only one instance of each row in class that is matched by rows in roster.

For some questions, the only information that matters is whether there is a match, not the number of matches.

anti-join은 그 반대다 (MySQL 8.0.17부터 문서에 등장).

An antijoin is an operation that returns only rows for which there is no match.

INNER JOIN과 뭐가 다른가

"유저"와 "내가 남긴 액션 기록"으로 비교하면:

연산	남기는 것	우리말로
INNER JOIN	매칭된 행 + 오른쪽 컬럼도 가져옴	액션을 남긴 유저와 그 기록
semi-join	매칭된 행만	내가 액션을 남긴 유저
anti-join	매칭 안 된 행만	내가 액션을 남기지 않은 유저

결정적 차이는 중복이다. 유저 A에게 액션을 3번 남겼다면:

INNER JOIN  → A가 3번 나온다 (중복)
semi-join   → A가 1번 나온다
anti-join   → A는 안 나온다

INNER JOIN으로 "액션을 남긴 유저 목록"을 뽑으면 DISTINCT가 필요해지고, DISTINCT는 임시 테이블·정렬을 부른다. semi/anti-join은 애초에 그럴 일이 없다.

표현하는 세 가지 방법

ANTI JOIN 같은 키워드는 없다. 아래 셋 다 논리적으로 같은 결과를 낸다.

-- 1. NOT EXISTS
SELECT u.id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM user_actions a WHERE a.user_id = u.id AND a.created_at >= '7일 전'
);

-- 2. NOT IN
SELECT u.id FROM users u
WHERE u.id NOT IN (
  SELECT a.user_id FROM user_actions a WHERE a.created_at >= '7일 전'
);

-- 3. LEFT JOIN + IS NULL
SELECT u.id FROM users u
LEFT JOIN user_actions a
  ON u.id = a.user_id AND a.created_at >= '7일 전'
WHERE a.id IS NULL;

NOT IN은 NULL에 취약하다

서브쿼리 결과에 NULL이 하나라도 섞이면 결과가 통째로 0건이 된다.

NOT IN은 <> ALL의 별칭이다.

NOT IN is an alias for <> ALL.

그래서 u.id NOT IN (1, 2, NULL)은 u.id <> 1 AND u.id <> 2 AND u.id <> NULL이 된다. 마지막 항이 영원히 UNKNOWN이라 전체가 TRUE가 될 수 없고, 행은 조건이 TRUE일 때만 반환되므로 아무것도 안 남는다.

In general, tables containing NULL values and empty tables are "edge cases." When writing subqueries, always consider whether you have taken those two possibilities into account.

서브쿼리 컬럼이 nullable이면 NOT IN을 피하고 NOT EXISTS나 LEFT JOIN + IS NULL을 쓴다.

어느 걸 쓸지는 실행 계획이 정한다

셋의 차이는 옵티마이저가 무엇을 고를 수 있느냐다.

NOT EXISTS / NOT IN → antijoin 변환 후보가 되고, 그중 materialization·first match 등 여러 전략 중 하나가 선택된다
LEFT JOIN + IS NULL → 변환 대상이 아니라 계획이 고정된다

선택도가 나쁘면 옵티마이저가 안쪽 테이블을 전량 스캔하는 전략을 고를 수 있다. 그럴 때 LEFT JOIN + IS NULL로 바꾸는 건 더 좋은 계획을 유도하는 게 아니라 나쁜 계획을 고를 여지를 없애는 것이다. (관찰됨 — 제외 대상이 수백 건인데 수천만 행 테이블을 통째로 훑는 계획이 나온 사례)

구현 시 조건을 WHERE가 아니라 ON에 둬야 하는 이유는 LEFT JOIN 오른쪽 테이블 조건을 WHERE에 두면 INNER JOIN으로 퇴화한다 참고.

semi-join과 anti-join은 매칭 여부만 보므로 INNER JOIN과 달리 행이 불어나지 않는다

요약

본문

관련 노트

참고

이 문서를 참조하는 노트 (1)

함께 읽기 좋은 글