SQL JOIN시 필터 조건을 어디에 넣어야 하나요? on vs where

Question

안녕하세요~
SQL JOIN에 대한 질문이 있습니다.

tableA
id, type
1, comment
2, comment
3, comment
4, reply

tableB
id, value
1, test
2, test2
3, test3
4, test4

라는 테이블이 있을때 tableA의 type으로 필터를 걸려고 하는데, 어떤 방식으로 해야하는지 궁금합니다.

1. SELECT * FROM tableB as b LEFT JOIN tableA as a ON a.id = b.id WHERE a.type = comment;

2. SELECT * FROM tableB as b LEFT JOIN tableA as a ON (a.id = b.id AND a.type = comment);

원하는 결과 값은 tableB와 A를 LEFT JOIN한 데이터 중 tableA type이 comment인 row만 추출하면 됩니다. 필터를 ON에다가 걸어야하나요? 아니면 WHERE에다가 걸어야하나요? 둘의 차이가 어떻게 되나요?

둘의 결과 값이 달라지긴 하더라구요. 2번을 사용하면 a.type이 comment가 아닌 것은 tableA 쪽 값들이 null로 나오더라구요.

같은 필터인데 왜 결과가 다른지 잘 모르겠습니다 ㅠㅠㅠ

손정현 · Answer

원하는 결과 값은 tableB와 A를 LEFT JOIN한 데이터 중 tableA type이 comment인 row만 추출하면 됩니다
이 목적이라면 1번 방법이 맞는 것 같습니다. 2번 방법의 경우는 말씀하신것처럼 type이 comment가 아닌 값들이 null인 상태로 같이 나옵니다.

필터를 ON에다가 걸어야하나요? 아니면 WHERE에다가 걸어야하나요?
경우에 따라 다를 것 같습니다. 어떤 JOIN인지도 영향이 있고 어떤 결과 값을 원하느냐에 따라 차이가 있을 것 같아요. 하지만 위와 같은 경우라면 WHERE에다가 거시는게 맞을 것 같습니다.

둘의 차이가 어떻게 되나요?
결과를 표면적으로 봤을 때 1번 방법은 JOIN 연산을 한 뒤 해당 결과에 WHERE 조건을 걸어서 필터를 한다고 볼 수 있고 2번 방법은 a.type = comment 조건으로 필터된 a 테이블을 b 테이블과 JOIN 한다고 볼 수 있을 것 같습니다.

실제 사용하시는 DB와 DB의 optimizer에 따라서 성능 차이가 있을 것 같습니다만 저도 깊게는 잘 모르겠네요. 온라인 SQL 툴 (MySQL)로 테스트 해봤을때는 1번 방법이 더 빠른 응답 속도를 가지고 있었습니다.

참고하시면 좋을 법한 링크 첨부합니다.
- https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#logical-processing-order-of-the-select-statement
- https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/

김대현 · Answer

JOIN인데 방식이 여러가지라 어려운 점이 있지요. LEFT JOIN의 경우, 기준 테이블 레코드와 매칭되는 조인 대상 테이블에 레코드가 없을 경우에 null이 채워져서 나옵니다. (JOIN의 종류와 차이를 공부하시면 도움이 될 것 같습니다.)

1번 방식은, a.type=comment를 LEFT JOIN시 조건으로 걸었으므로, 매칭되는 a테이블의 레코드가 없을 때도, 전체 결과값이 (null로 해당 컬럼들이 채워져) 반환됩니다만.

2번 방식은 LEFT JOIN을 끝낸 다음 결과에서 a.type=comment로 다시 필터를 걸었으므로, JOIN으로 인해 null로 채워졌던 레코드들이 걸러진 셈이죠.

INNER JOIN으로 했다면, 두 쿼리의 결과가 같게 나올 것 같습니다.