LEFT JOIN 오른쪽 테이블 조건을 WHERE에 두면 INNER JOIN으로 퇴화한다

요약

LEFT JOIN이 만든 NULL 행은 오른쪽 테이블 컬럼에 대한 WHERE 조건을 통과할 수 없다. NULL과의 비교가 UNKNOWN이기 때문이다.
그래서 조건을 WHERE에 두면 결과가 INNER JOIN과 같아진다. MySQL은 이걸 아예 INNER JOIN으로 바꿔 실행한다.
"짝이 없는 행만 남기는" anti-join을 하려면 조건이 반드시 ON 절에 있어야 한다. Django에서는 FilteredRelation + isnull=True 조합이다.

본문

ON과 WHERE의 역할이 다르다

ON = 무엇과 짝지을지 정하는 규칙
WHERE = 짝짓기가 끝난 결과에서 무엇을 남길지 정하는 필터

INNER JOIN에서는 둘 중 어디에 써도 결과가 같다. 갈라지는 건 OUTER JOIN에서뿐이다. 짝이 없는 행을 남기느냐 마느냐가 걸려 있기 때문이다.

왜 퇴화하는가

"최근 7일 내 액션이 있는 사용자를 제외" 하고 싶다고 하자.

후보	액션 기록	원하는 결과
A	3일 전	제외
B	300일 전	남아야 함
C	없음	남아야 함

조건을 ON에 두면 (올바름):

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;

B의 300일 전 기록은 ON 조건에서 걸러져 "짝 없음"으로 처리된다. 조인 후 A만 짝이 있고 B·C는 NULL이므로 B, C가 남는다.

조건을 WHERE에 두면 (틀림):

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

ON에 기간 조건이 없으니 B에도 300일 전 행이 붙는다. 그다음 WHERE에서:

A: 기간 조건 통과, a.id IS NULL 실패 → 탈락
B: 기간 조건 실패 → 탈락
C: a가 NULL이라 a.created_at도 NULL. NULL >= '7일 전'은 참이 아니다(UNKNOWN) → 탈락

0건. 두 조건이 서로 모순이다. a.created_at >= ...은 a가 존재해야 참이 될 수 있고, a.id IS NULL은 a가 없어야 참이다.

IS NULL이 없어도 이미 퇴화한다. WHERE a.created_at >= '7일 전'만 있어도 C가 죽으므로 INNER JOIN과 결과가 같다.

MySQL은 이 변환을 명시적으로 한다

옵티마이저가 우연히 그렇게 되는 게 아니라 문서화된 최적화다(outer join simplification).

For a LEFT JOIN, if the WHERE condition is always false for the generated NULL row, the LEFT JOIN is changed to an inner join.

WHERE 조건이 "생성된 NULL 행에 대해 항상 거짓"이면 null-rejected로 판정하고 INNER JOIN으로 바꾼다.

Django에서 anti-join 쓰기

qs.annotate(
    recent_action=FilteredRelation(
        "user_actions",
        condition=Q(user_actions__created_at__gte=seven_days_ago),
    )
).filter(recent_action__isnull=True)

둘 다 있어야 성립한다.

	하는 일
`FilteredRelation`	조건을 WHERE가 아니라 ON 절에 넣는다
`__isnull=True`	조인을 LEFT OUTER로 승격시키고 `WHERE ... IS NULL`을 붙인다

FilteredRelation만 쓰면 조인은 하지만 제외가 안 되고, isnull만 쓰면 조건이 WHERE로 가서 위 함정에 빠진다.

FilteredRelation의 condition은 관계 경로가 붙은 이름만 받는다(created_at__gte가 아니라 user_actions__created_at__gte). 같은 조건을 일반 필터와 조인 조건 양쪽에서 쓰려면 룩업 이름에 prefix를 붙여주는 헬퍼가 필요하다.

소스 근거 (Django 4.2):

db/models/query_utils.py — class FilteredRelation의 docstring이 "Specify custom filtering in the ON clause of SQL joins."
db/models/sql/query.py — require_outer = (lookup_type == "isnull" and condition.rhs is True and not current_negated). isnull=True가 LEFT OUTER 승격을 요구한다.

EXISTS 대신 LEFT JOIN을 고르는 경우도 있다

논리적으로는 NOT EXISTS도 같은 결과를 준다. 그런데 EXISTS는 semijoin 변환 후보가 되고, 옵티마이저가 그중 materialization(서브쿼리 결과를 임시 테이블로 만들어 재사용)을 고르면 안쪽 테이블을 전량 스캔하는 계획이 나올 수 있다. 제외 대상이 몇백 건뿐인데 수천만 행 테이블을 통째로 훑는 식이다. (관찰됨 — 특정 스키마·데이터 분포에서)

LEFT JOIN은 애초에 그 변환 대상이 아니다. 더 좋은 계획을 유도한 게 아니라 나쁜 계획을 고를 여지를 없앤 것이다. 같은 전략이 테이블 크기와 선택도에 따라 이득도 손해도 되므로, 어느 쪽이든 EXPLAIN으로 실제 선택을 확인해야 한다.

LEFT JOIN 오른쪽 테이블 조건을 WHERE에 두면 INNER JOIN으로 퇴화한다

요약

본문

관련 노트

참고

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

함께 읽기 좋은 글