조인 종류와 작성 2

조인 종류와 작성에서 조건이 맞는 짝만 남기는 내부 조인(inner join)과, 한쪽 행을 짝이 없어도 NULL로 채워 남기는 외부 조인(outer join)을 봤다. 이 문서는 외부 조인에서 조건을 ON에 쓰느냐 WHERE에 쓰느냐에 따라 결과 행 수가 달라지는 함정, 채워 넣은 NULL이 집계를 바꾸는 방식, 그리고 짝이 있는지 없는지만 확인하는 조인(join)을 뜻에 맞게 쓰는 법을 다룬다.

ON과 WHERE — 외부 조인에서 결과가 달라진다

ON은 어떻게 짝지을지를, WHERE는 짝지은 결과에서 무엇을 남길지를 정한다. 내부 조인에서는 조건을 어디에 두든 결과가 같다. 외부 조인에서는 다르다. 아래 LEFT JOIN에서 왼쪽 테이블은 FROM에 먼저 쓴 users로 짝이 없어도 모두 남고, 오른쪽 테이블은 JOIN 뒤에 쓴 orders로 짝이 없으면 NULL로 채워진다.

-- ① 모든 회원 + 그중 결제 완료 주문(없으면 NULL)
SELECT u.id, o.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'PAID';

-- ② 결제 완료 주문이 있는 회원만 (사실상 INNER JOIN)
SELECT u.id, o.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'PAID';

①에서 status 조건은 짝을 고르는 데만 쓰인다. 결제 주문이 없는 회원도 o.id가 NULL인 채로 남는다. ②에서는 짝지은 뒤 WHERE가 거르는데, 짝이 없는 회원 행은 o.status가 NULL이라 = 'PAID'가 참이 되지 않아 빠진다. 결과적으로 내부 조인과 같아진다. MySQL 옵티마이저(optimizer)도 이렇게 NULL로 채운 행을 반드시 버리는 WHERE 조건을 보면 외부 조인을 내부 조인으로 바꿔 실행한다. 내부 조인은 어느 테이블을 먼저 읽어도 결과가 같아서 더 싼 조인 순서를 고를 수 있기 때문이다.

반대 방향 함정도 있다. LEFT JOIN ... ON ... AND u.active = 1처럼 왼쪽 테이블 조건을 ON에 쓰면 왼쪽 행이 걸러지지 않는다. 왼쪽 행은 어차피 전부 남으므로 active = 0인 회원도 오른쪽이 NULL인 채로 나온다. 왼쪽 행을 거르는 조건은 WHERE에 쓴다.

정리하면 외부 조인에서 오른쪽 테이블 조건은 ON에, 왼쪽 테이블 조건은 WHERE에 두는 것이 대개 의도와 맞다. 오른쪽 조건을 WHERE에 둔다면 o.id IS NULL처럼 짝이 없는 행을 일부러 찾는 경우다.

외부 조인과 NULL

외부 조인 결과에는 채워 넣은 NULL이 섞이므로 집계가 달라진다.

• 회원별 주문 수를 LEFT JOIN 뒤 COUNT(*)로 세면, 주문이 없는 회원도 NULL로 채운 한 행이 있어 1이 나온다. **COUNT(o.id)**로 세야 NULL을 빼고 0이 나온다.
• 주문이 없는 회원의 SUM(o.amount)는 0이 아니라 NULL이다. 0으로 보여야 하면 COALESCE(SUM(o.amount), 0)으로 감싼다. COALESCE()는 인자 가운데 NULL이 아닌 첫 값을 돌려주므로 합계가 NULL이면 0이 나온다.
• 조인 키가 NULL인 행은 어떤 행과도 짝지어지지 않는다. NULL = NULL도 참이 아닌 '알 수 없음'이고, 조인 조건은 참일 때만 짝을 짓기 때문이다.

세미 조인과 안티 조인 작성법

"주문한 적 있는 회원"처럼 짝이 있는지만 알고 싶을 때 INNER JOIN을 쓰면, 주문이 세 건인 회원이 세 번 나온다. 이를 DISTINCT로 지우는 것보다, 짝이 있는지만 확인하는 세미 조인(semi-join)으로 쓰는 편이 뜻이 분명하다.

-- 세미 조인: 주문이 있는 회원 (회원당 한 번)
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 안티 조인: 주문이 한 번도 없는 회원
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 안티 조인의 다른 표현: 짝이 없어 NULL로 채워진 행만 남기기
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

안티 조인(anti-join)은 세미 조인의 반대로, 짝이 없는 행만 남기는 것이다. NOT EXISTS나 LEFT JOIN ... WHERE 오른쪽.PK IS NULL로 쓴다. IS NULL 검사는 NULL이 될 수 없는 칼럼(오른쪽 PK(Primary Key, 기본 키))에 걸어야 "짝이 없음"을 정확히 뜻한다. NOT IN (서브쿼리)도 같은 뜻처럼 보이지만, 서브쿼리(subquery, 괄호 안에 넣은 SELECT) 결과에 NULL이 하나라도 있으면 한 행도 돌려주지 않는 함정이 있다. x NOT IN (1, NULL)은 x <> 1 AND x <> NULL로 풀리는데, x <> NULL이 '알 수 없음'이라 전체가 참이 될 수 없기 때문이다(서브쿼리·CTE·윈도우 함수).

옵티마이저는 IN (서브쿼리)를 세미 조인으로 바꿔 조인처럼 최적화할 수 있고, MySQL 8.0.16부터는 EXISTS도 같은 대상이 됐다. 8.0.17부터는 NOT IN·NOT EXISTS도 안티 조인으로 바꾼다. 단, 비교하는 칼럼이 NULL을 허용하면 대부분의 경우 안티 조인 변환이 적용되지 않는다. 그래서 "EXISTS가 JOIN보다 느리다" 같은 옛 규칙보다, 뜻에 맞는 문장을 쓰고 옵티마이저가 실제로 고른 실행 계획(execution plan)을 EXPLAIN으로 확인하는 것이 낫다.

면접 포인트

• "LEFT JOIN인데 결과가 INNER JOIN처럼 나와요" → 오른쪽 테이블 조건을 WHERE에 뒀다. ON으로 옮긴다. 왼쪽 테이블 조건은 WHERE에.
• "회원별 주문 수가 0이 아니라 1로 나와요" → LEFT JOIN 뒤 COUNT(*)는 NULL로 채운 행도 센다. COUNT(o.id)로 센다.
• "주문 없는 회원 찾기" → NOT EXISTS 또는 LEFT JOIN ... WHERE o.id IS NULL. NOT IN은 NULL 함정.
• 조인 알고리즘(NL(Nested Loop, 중첩 루프) 조인·해시 조인)은 작성법과 별개 질문이다 → 조인 최적화.