SQL LEFT JOIN ON과 WHERE 차이: 공개 사진이 없는 상품도 남기는 법

상품 목록에 사진을 붙이는 쿼리를 바꿨더니 사진이 없는 상품까지 목록에서 사라졌다. LEFT JOIN을 썼으니 상품은 모두 남을 줄 알았는데, 사진 상태 조건을 WHERE로 옮긴 순간 결과가 달라진 것이다. 이번에는 상품 세 개를 끝까지 같은 입력으로 사용해 조건의 자리를 구분해 보자.

LEFT JOIN은 어느 행을 남길까?

도식의 위쪽 ON 경로는 공개 사진이 없는 상품 B와 C도 왼쪽 행으로 남긴다. 아래쪽 WHERE 경로는 조인이 끝난 뒤 조건에 맞지 않는 행을 걸러 상품 A만 남긴다는 차이가 핵심이다.

product를 왼쪽, photo를 오른쪽에 놓는다. 왼쪽 상품은 사진과 짝이 없어도 결과에 남고, 짝이 없는 오른쪽 열에는 NULL이 들어간다. NULL은 숫자 0이나 빈 문자열이 아니라 이 조인 결과에 붙일 사진 행이 없다는 표시다.

아래 예제에는 컵의 공개 사진, 램프의 초안 사진, 사진이 전혀 없는 책상이 있다. 데이터는 동작을 설명하기 위해 만든 값이다.

CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE photo (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  status TEXT NOT NULL
);

INSERT INTO product VALUES (1, 'cup'), (2, 'lamp'), (3, 'desk');
INSERT INTO photo VALUES (101, 1, 'public'), (102, 2, 'draft');

상품 ID와 사진의 product_id가 같을 때 짝을 만든다. 여기서는 조인 조건을 읽기 쉽게 하려고 외래키 제약을 생략했다. 실제 테이블에서는 참조 무결성과 허용 상태값을 별도로 설계해야 한다.

공개 사진 조건을 ON에 두면 상품 세 개가 남는다

상품은 모두 보여 주고, 공개 사진만 곁에 표시하려면 사진 상태를 ON에 넣는다. ON은 어떤 사진을 짝으로 인정할지 결정한다.

SELECT p.name, ph.id AS photo_id
FROM product AS p
LEFT JOIN photo AS ph
  ON ph.product_id = p.id AND ph.status = 'public'
ORDER BY p.id;

램프에는 초안 사진 102가 있지만 공개 사진은 없다. 책상은 사진 자체가 없다. 두 경우 모두 이번 조인에서 붙일 공개 사진이 없으므로 photo_id가 NULL이지만, 상품 행은 살아 있다. 따라서 이 결과만으로 램프에 사진이 전혀 없다고 결론 내리면 안 된다.

같은 조건을 WHERE로 옮기면 왜 두 상품이 사라질까?

이번에는 ID가 같은 사진을 먼저 붙이고, 결과에서 공개 사진만 고른다.

SELECT p.name, ph.id AS photo_id
FROM product AS p
LEFT JOIN photo AS ph ON ph.product_id = p.id
WHERE ph.status = 'public'
ORDER BY p.id;

결과는 cup | 101 한 행이다. 램프의 draft는 조건에 맞지 않는다. 책상의 오른쪽 값은 NULL이라 ph.status = 'public'이 참이 아니다. WHERE는 참인 행만 통과시키므로 둘 다 빠진다. LEFT JOIN의 문법이 내부 조인으로 바뀐 것은 아니지만, 이 필터를 거친 결과는 공개 사진이 있는 상품만 남는다.

WHERE ph.status = 'public' OR ph.id IS NULL이면 될까? 이 조건은 컵과 책상은 남기지만 램프는 여전히 지운다. 램프는 초안 사진 102와 이미 짝을 이뤄 ph.id가 NULL이 아니기 때문이다. 오른쪽에 다른 상태의 행이 있는지까지 확인해야 하는 이유다.

목록과 집계를 검증할 때 확인할 것

먼저 상품 세 개가 실제로 존재하는지, 각 상품의 사진 상태가 무엇인지 각각 조회한다. 그다음 조인 결과에서 어떤 상품 ID가 빠졌는지 비교하면 ON의 짝짓기와 WHERE의 결과 필터링 중 어디서 빠졌는지 좁힐 수 있다.

사진 수를 세는 목록이라면 COUNT(*) 대신 COUNT(ph.id)가 필요한지 검토한다. 공개 사진이 없는 상품에도 왼쪽을 보존한 결과 행은 하나 만들어지므로 COUNT(*)는 1을 반환할 수 있다. 반면 COUNT(ph.id)는 오른쪽 사진 ID가 NULL인 행을 세지 않는다. 사진을 여러 장 허용하면 상품 하나가 결과에 여러 번 나타날 수도 있으니, 조인 전후 행 수와 집계 단위를 함께 확인하자.

상품 목록에 반드시 모든 상품이 필요하다면 공개 상태 조건을 ON에 둔다. 반대로 공개 사진이 있는 상품만 찾는 검색이라면 WHERE가 의도에 맞을 수 있다. 대량 데이터에서는 인덱스와 실행 계획도 확인해야 하지만, 계획에 보이는 물리적 처리 순서를 이 글의 논리적 결과 설명과 같은 것으로 단정하지는 않는다.

핵심 요약: ON은 붙일 사진, WHERE는 남길 결과를 정한다

LEFT JOIN의 왼쪽 상품을 보존하면서 공개 사진만 붙이려면 사진 상태를 ON에 둔다. 상태를 WHERE에 두면 초안 사진만 있는 상품과 사진 없는 상품이 함께 사라진다. 먼저 원하는 상품 집합을 정하고, 작은 데이터로 공개·초안·사진 없음 세 경우를 확인하면 조건을 옮길 때 생기는 누락을 빨리 찾을 수 있다.