LEFT JOIN을 썼는데 매칭 없는 행이 사라진 이유

문제 발생

댓글이 없는 글도 함께 보여주려고 LEFT JOIN을 썼는데, 결과에서 댓글 없는 글이 전부 빠졌습니다.

SELECT p.id, p.title, c.id AS comment_id
FROM dev_posts p
LEFT JOIN dev_comments c ON c.dev_post_id = p.id
WHERE c.deleted_at IS NULL;      -- 여기가 문제

WHERE를 지우면 다시 나타났습니다.

원인 분석

LEFT JOIN이 INNER JOIN으로 바뀌었습니다. 매칭이 없는 행에는 오른쪽 테이블 컬럼이 전부 NULL로 채워집니다(NULL-complemented row). 그런데 c.deleted_at IS NULL은 이 행에서... 참이 됩니다. 그래서 이 예에서는 남을 것 같지만, 실제로 자주 쓰는 c.deleted_at IS NULL AND c.is_public = 1 같은 조건은 NULL = 1이 되어 UNKNOWN이 되고 행이 탈락합니다.

MySQL 문서는 이 변환을 명시적으로 설명합니다 — WHERE 조건이 null-rejected이면 MySQL은 쿼리를 outer join이 없는 쿼리로 변환합니다. 그리고 정의도 적혀 있습니다 — 어떤 조건이 그 outer join 연산에 대해 NULL-complemented 행에 대해 FALSE나 UNKNOWN으로 평가되면 null-rejected입니다.

문서의 예시가 이해를 굳혀줍니다. T1 LEFT JOIN T2 ON T1.A=T2.A에서

• null-rejected(→ INNER JOIN으로 변환): T2.B IS NOT NULL, T2.B > 3, T2.C <= T1.C
• null-rejected 아님: T2.B IS NULL, T1.B < 3 OR T2.B IS NOT NULL

이건 최적화이지 버그가 아닙니다. 어차피 결과가 같기 때문에 더 나은 실행 계획을 고를 수 있는 형태로 바꾸는 것입니다. 문제는 우리가 의도한 결과가 그게 아니었다는 점입니다.

해결 방안

1. 오른쪽 테이블에 대한 조건은 ON으로 옮깁니다. 조인 단계에서 걸러지고, 매칭 없는 왼쪽 행은 그대로 남습니다.

SELECT p.id, p.title, c.id AS comment_id
FROM dev_posts p
LEFT JOIN dev_comments c
  ON c.dev_post_id = p.id AND c.deleted_at IS NULL
WHERE p.deleted_at IS NULL;      -- 왼쪽 테이블 조건은 WHERE에 남는다

2. "왼쪽 조건은 WHERE, 오른쪽 조건은 ON"을 규칙으로 삼습니다. 이 한 줄이 대부분의 사고를 막습니다.

3. "매칭이 없는 것만" 찾을 때는 예외입니다. anti-join 패턴에서는 오른쪽 조건이 WHERE에 있어야 합니다 — IS NULL은 null-rejected가 아니라서 변환이 일어나지 않습니다.

SELECT p.id FROM dev_posts p
LEFT JOIN dev_comments c ON c.dev_post_id = p.id
WHERE c.id IS NULL;

4. 집계 결과가 이상하면 이걸 먼저 의심합니다. "댓글 없는 글의 댓글 수가 0건이 아니라 아예 목록에서 빠지는" 증상이 대표적입니다.

5. EXPLAIN으로 확인합니다. 실행 계획에 outer join이 남아 있는지 보면 변환 여부가 드러납니다.

공식 문서

• MySQL: Outer Join Simplification
• MySQL: JOIN Clause