LEFT JOIN에 WHERE를 걸면 INNER JOIN이 되는 이유
강의 목록 화면에 열을 하나 더 붙이는 일이었다. 관리자 페이지에서 강의마다 별점 5점짜리 리뷰가 몇 개인지 보여 달라는 요청이었다. 기존 쿼리에 reviews 테이블을 LEFT JOIN으로 붙이고 WHERE에 r.rating = 5를 적었다. 로컬에서 숫자가 잘 나오는 걸 보고 그대로 배포했다.
다음 날 오전에 지난주 연 강의 세 개가 목록에 안 보인다는 메시지가 왔다. 세어 보니 목록이 47줄에서 19줄로 줄어 있었다. 사라진 강의는 전부 5점 리뷰가 없는 강의였다. 리뷰가 아예 없는 강의도, 4점 리뷰만 받은 강의도 함께 빠졌다. 순간 등이 서늘했다. 분명 LEFT JOIN을 썼는데 왼쪽 행이 왜 사라지나.
원인은 조건을 적은 자리였다. 오른쪽 테이블 조건을 ON에 두느냐 WHERE에 두느냐에 따라 결과가 달라진다. WHERE에 두면 옵티마이저가 그 조인을 아예 내부 조인으로 바꿔 버린다. 그날 정리한 내용을 예제와 함께 남긴다.
예제 데이터로 다시 만들어 본다
사고를 재현하려고 작은 테이블 두 개를 만들었다. 강의 셋, 리뷰 셋이다. ‘SQL 입문’은 5점과 3점을 받았고, ‘인덱스 설계’는 4점 하나, ‘트랜잭션 기초’는 리뷰가 없다. 운영에서 사라진 두 부류인 리뷰 없는 강의와 5점이 없는 강의를 하나씩 넣은 셈이다.
CREATE TABLE courses (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL,
rating INTEGER NOT NULL
);
INSERT INTO courses VALUES (1, 'SQL 입문'), (2, '인덱스 설계'), (3, '트랜잭션 기초');
INSERT INTO reviews VALUES (1, 1, 5), (2, 1, 3), (3, 2, 4);이 SQL은 PostgreSQL, MySQL, SQLite에서 고치지 않고 그대로 돌아간다.
조건 하나, 자리 둘
먼저 내가 배포했던 쿼리다.
SELECT c.title, r.rating
FROM courses c
LEFT JOIN reviews r ON r.course_id = c.id
WHERE r.rating = 5; title | rating
----------+--------
SQL 입문 | 5강의 셋 중 둘이 사라졌다. 같은 조건을 ON 쪽으로 옮기면 이렇게 된다.
SELECT c.title, r.rating
FROM courses c
LEFT JOIN reviews r ON r.course_id = c.id AND r.rating = 5; title | rating
---------------+--------
SQL 입문 | 5
인덱스 설계 |
트랜잭션 기초 |PostgreSQL 공식 문서의 테이블 표현식 장은 이 차이를 짧게 설명한다. ON에 둔 제한은 조인 전에, WHERE에 둔 제한은 조인 뒤에 처리된다. 내부 조인에서는 상관없지만 외부 조인에서는 크게 달라진다는 말도 덧붙어 있다. 처음 읽었을 때는 너무 당연한 말 같아서 넘겼다. 중간 결과를 직접 펼쳐 보고 나서야 무슨 뜻인지 와닿았다.
조인 조건이 r.course_id = c.id 하나뿐이면 LEFT JOIN이 만드는 중간 결과는 네 줄이다.
| title | r.id | rating |
|---|---|---|
| SQL 입문 | 1 | 5 |
| SQL 입문 | 2 | 3 |
| 인덱스 설계 | 3 | 4 |
| 트랜잭션 기초 | NULL | NULL |
WHERE r.rating = 5는 이 네 줄을 다 만든 뒤에 거르니 첫 줄만 남는다. 반대로 ON에 넣으면 짝을 고르는 단계부터 5점 리뷰만 후보가 된다. ‘인덱스 설계’는 4점 리뷰가 있어도 짝이 되지 못한다. 짝이 없는 왼쪽 행은 LEFT JOIN 규칙에 따라 오른쪽을 NULL로 채운 채 결과에 남는다. 그래서 세 줄이 나온다.
NULL은 참도 거짓도 아니다
WHERE가 NULL로 채운 줄을 지우는 건 비교 연산의 성질 때문이다. NULL = 5는 거짓이 아니라 UNKNOWN이고, WHERE는 결과가 참인 줄만 통과시킨다. 짝이 없어 rating이 NULL이 된 줄은 무엇과 비교해도 참이 될 수 없다. LEFT JOIN이 살려 둔 줄을 WHERE가 남김없이 지우고, 오른쪽에 실제 짝이 있는 줄만 남는다. 그건 INNER JOIN의 결과와 똑같다.
원래 요구 사항이던 5점 리뷰 개수는 이렇게 센다.
SELECT c.title, count(r.id) AS five_star
FROM courses c
LEFT JOIN reviews r ON r.course_id = c.id AND r.rating = 5
GROUP BY c.id, c.title
ORDER BY c.id;count(*)가 아니라 count(r.id)를 쓴 데도 이유가 있다. count(*)는 NULL로 채운 줄도 한 줄로 세서 리뷰 없는 강의에 1을 찍는다. 이것도 한 번 당해 봤다.
옵티마이저는 이미 알고 있다
재미있는 건 데이터베이스가 이 사정을 나보다 먼저 안다는 점이다. 세 데이터베이스 모두 WHERE 조건이 NULL로 채운 줄을 반드시 버린다고 증명할 수 있으면 외부 조인을 내부 조인으로 바꾼다. 내부 조인은 조인 순서를 자유롭게 고를 수 있어서 계획을 세우기가 편하다.
PostgreSQL
플래너 소스의 reduce_outer_joins 주석에 바로 이 예가 나온다. a LEFT JOIN b ... WHERE b.y = 42에서 = 연산자가 strict하면, 즉 입력이 NULL일 때 결과도 NULL이면 LEFT JOIN을 평범한 JOIN으로 줄일 수 있다는 설명이다.
EXPLAIN (COSTS OFF)
SELECT c.title, r.rating
FROM courses c
LEFT JOIN reviews r ON r.course_id = c.id
WHERE r.rating = 5; QUERY PLAN
--------------------------------------
Hash Join
Hash Cond: (c.id = r.course_id)
-> Seq Scan on courses c
-> Hash
-> Seq Scan on reviews r
Filter: (rating = 5)조인 노드 이름에서 Left가 빠졌다. 통계에 따라 Nested Loop가 나올 수도 있지만 어느 쪽이든 Left는 붙지 않는다. rating 필터가 reviews를 읽는 단계로 내려간 것도 보인다. ON 버전으로 바꾸면 Hash Left Join이나 Hash Right Join처럼 방향이 붙은 노드가 돌아온다.
같은 주석에는 다른 변환도 적혀 있다. WHERE b.y IS NULL처럼 짝 없는 줄만 남기는 조건이 붙으면 조인 종류를 안티 조인으로 바꾼다. 리뷰 없는 강의를 찾는 쿼리에 EXPLAIN을 걸면 Anti Join 노드가 보인다. 오른쪽 조건이 WHERE에 있다고 늘 내부 조인이 되는 건 아니다.
MySQL
MySQL 8.0 매뉴얼은 이를 외부 조인 단순화라고 부른다. 기준은 null-rejected 조건이다. NULL로 채운 줄에서 FALSE나 UNKNOWN이 되는 조건이 있으면 내부 조인으로 바꾼다. T2.B > 3은 여기에 들고 T2.B IS NULL이나 T1.B < 3 OR T2.B > 3은 들지 않는다. EXPLAIN 바로 뒤에 SHOW WARNINGS를 실행하면 옵티마이저가 다시 쓴 쿼리가 나온다. 내 쿼리로 해 보니 left join이 사라지고 join만 남아 있었다.
EXPLAIN SELECT c.title, r.rating
FROM courses c
LEFT JOIN reviews r ON r.course_id = c.id
WHERE r.rating = 5;
SHOW WARNINGS;SQLite
SQLite 옵티마이저 문서에도 OUTER JOIN 강도 축소 항목이 있다. WHERE가 참이 되려면 오른쪽 테이블의 어떤 열이 NULL이 아니어야 할 때 LEFT JOIN을 일반 조인으로 내린다. 문서는 이 판단이 완벽하지 않다고 적는다. datetime() 함수가 NULL을 받으면 NULL을 돌려준다는 걸 몰라서 줄여도 되는 조인을 그냥 두기도 한다. 결과는 같고 계획만 덜 영리해지는 쪽이다.
급하게 고치다 한 번 더 틀렸다
그날 오전에는 원인을 반쯤만 이해한 채 핫픽스를 올렸다. NULL 때문이라는 건 알았으니 WHERE를 r.rating = 5 OR r.id IS NULL로 바꿨다. 리뷰 없는 강의가 돌아온 걸 보고 안도했다. 그런데 오후에 다시 세어 보니 여전히 몇 줄이 모자랐다. 4점 리뷰만 받은 강의들이었다. 이 강의들은 조인에서 짝을 찾았으니 r.id가 NULL이 아니고, rating도 5가 아니어서 두 조건을 다 통과하지 못한다. 허탈했다.
| 조건 위치 | SQL 입문 | 인덱스 설계 | 트랜잭션 기초 |
|---|---|---|---|
WHERE r.rating = 5 | 남음 | 빠짐 | 빠짐 |
WHERE r.rating = 5 OR r.id IS NULL | 남음 | 빠짐 | 남음 |
ON ... AND r.rating = 5 | 남음 | 남음 | 남음 |
OR IS NULL은 오른쪽 짝이 아예 없는 경우만 살린다. 짝은 있는데 조건에 맞지 않는 행까지 살리려면 조건을 ON으로 옮겨야 한다. reviews를 서브쿼리로 먼저 거른 뒤 LEFT JOIN 해도 결과는 같지만, 나는 ON 쪽이 한눈에 읽혀서 그쪽을 쓴다.
지금 쓰는 방식
그 뒤로 LEFT JOIN을 쓸 때 몇 가지를 습관처럼 확인한다.
- 오른쪽 테이블 열이 WHERE에 보이면 일단 멈춘다. 짝을 고르는 조건이면 ON으로 옮긴다.
- WHERE에 남겨도 되는 건 IS NULL로 짝 없는 행을 찾을 때, 그리고 처음부터 내부 조인을 원했을 때다. 후자라면 JOIN으로 고쳐 쓴다.
- 왼쪽 테이블만 센 개수와 결과 줄 수를 비교한다. 다르면 무언가 행을 지우고 있다.
- 미심쩍으면 EXPLAIN에서 조인 노드에 Left가 남았는지 본다. MySQL이면 SHOW WARNINGS까지 본다.
LEFT JOIN 뒤에 WHERE를 붙이는 건 문법상 아무 문제가 없어서 에러도 경고도 나지 않는다. 그래서 더 위험하다. 옵티마이저가 LEFT를 조용히 지우는 건 틀려서가 아니라 내가 이미 INNER JOIN을 써 놓았기 때문이다. 요즘은 EXPLAIN에서 Left가 사라진 걸 보면 반가운 최적화보다 의도와 쿼리가 어긋났다는 신호로 먼저 읽는다. 내가 기대한 줄 수와 데이터베이스가 돌려준 줄 수를 맞춰 보는 일이 결국 가장 싸게 먹히는 테스트다.