mysql on절 vs where절 : 언제 어떻게 필터링 되는가?

 어제 잠깐 outer join에 대해서 했었습니다. 거기에서 on 절이 나왔었는데요. where 조건과 헷갈릴 여지가 꽤 다분해 보였습니다. 이 둘의 차이점만 간단하게 다루어 보도록 하겠습니다. 그 전에 이런 질문을 생각해 봅시다.

 

 이 두 쿼리는 임의의 t1, t2 데이터에 대해서, 임의의 condition 1과 condition2에 대해서 같은 결과를 리턴할까요? 이렇게 극단적으로 가는 질문은 아니오가 답인 경우가 많습니다. 어떻게 다를까요?

 

 

---

 on 뒤에 붙는 조건은, JOIN 조건을, where 뒤에 붙는 조건은 필터링 조건을 의미합니다. 이 둘이 어떻게 다른지 예제 테이블로 보여드리도록 하겠습니다. 테이블 t1은 아래와 같습니다.

 

 

 다음에, 테이블 t2는 아래와 같습니다.

 

 

 그러면 여기서 JOIN 조건이 무엇인지 먼저 생각해 봅시다. 아래와 같은 쿼리를 작성했을 때, 어떻게 해석이 되는지 보겠습니다.

 

 

 보시면, t1.a = t2.a와, t2.b = 1은 JOIN 조건임을 알 수 있습니다. t1과 t2를 카티션 곱 하면서, 조건에 맞는 것들을 필터링 해 보도록 합시다.

 

 

 t1, t2의 레코드들은 각각 <a, b>쌍입니다. 그리고 이들을 카티션 곱 해서 나온 또 다른 레코드는 <t1.a, t1.b, t2.a, t2.b>로 나올 겁니다. 일단 t1의 <1,1>과 t2를 카티션 곱 했을 때, t1.a = t2.a이고, t1.b = 1인 레코드를 노란색으로 표시해 봅시다.

 

 

 <1,1,1,1> 인가요? 따라서, 결과값에는 <1,1,1,1>이 포함됩니다. 그 다음에, t1의 <1,2> 레코드로 가 봅시다.

 

 

 마찬가지로 뽑아보면 어떻게 되나요? t1.a와 t2.a가 같고, t2.b가 1인 것은 <1,2,1,1>밖에 없습니다. 따라서, <1,2,1,1>은 결과 Set에 포함이 됩니다.

 

 

 다음에 <2,1>을 봅시다. 이것과 t2와 카티션 곱을 하면, <2,1,1,1>, <2,1,1,2>가 나옵니다. 여기서 t1.a와 t2.a가 같은 경우는 없습니다. 따라서 결과값이 없어요. inner join이였으면 결과 Set에 포함되지 않았을 건데 left join입니다. JOIN 조건에 걸리는 게 없기 때문에, null이 2개 뒤에 붙습니다. 따라서 <2,1,null,null>이 결과값에 들어갑니다. <2,2>와 t2를 카티션 곱해도, <3,1>과 해도, <3,2>와 해도 마찬가지입니다. 왜냐하면, t2.a가 2인 레코드도, t2.a가 3인 레코드가 t2에 없기 때문입니다.

 

 

 따라서 결과 값은 위와 같이 나옵니다. JOIN 구문이 헷갈리신다면 두 테이블을 기준으로 nested loop를, 더 쉽게 for loop를 돌린다고 생각하시면 됩니다. 아 카티션 곱을 이해하면 되겠거니. 생각하시면 됩니다. 해석의 흐름을 위와 같이 하시면 크게 문제는 없을 듯 싶네요.

 

 where에 의해서 필터링이 걸러지지 않았다는 것도 포인트라면 포인트입니다.

 

 

---

 그런데 t2.b = 1 조건을 where 절로 빼면 어떻게 될까요? 이 경우에는 이야기가 달라집니다.

 

 

 정확히 말하면 JOIN 필터 조건이 t1.a = t2.a인 거고, 거기서 나온 결과값의 필터가 t2.b = 1인 겁니다. 단순히 저는, ON 조건에 있었던 condition2를 바깥으로 뺀 것 뿐입니다.

 

 

 그러면 어떻게 결과가 달라질까요? 일단 2번 줄까지 해석을 해 보면, JOIN 조건이 t1.a = t2.a인 t1, t2를 LEFT JOIN을 수행하세요. 정도로 보시면 되는데요.

 

 

 천천히 해석해 봅시다. 먼저, <1,1>과 t2를 카티션 곱 해 봅시다. 그러면 <1,1,1,1>하고 <1,1,1,2>가 나오게 되는데요. 여기서 t1.a = t2.a가 같은 레코드는 <1,1,1,1>과 <1,1,1,2>입니다. 이들이 where 필터에 걸리기 전의 결과값에 추가가 될 겁니다. 

 

 

 다음. <1,2>와 t2를 x연산을 해 봅시다. 그러면 <1,2,1,1>과 <1,2,1,2>가 나오는데요. t1.a와 t2.a가 같은 것은 <1,2,1,1>과 <1,2,1,2>입니다. 이 둘이 where 필터링이 되기 전 결과 Set에 추가됩니다.

 

 

 다음에 <2,1>과 t2를 x연산 해 봅시다. 그러면 <2,1,1,1>과 <2,1,1,2>가 나오는데요. t1.a와 t2.a가 같은 레코드가 존재하지 않습니다. 따라서, <2,1,null,null>이 결과 Set에 들어갈 겁니다. t1의 나머지 레코드들인 <2,2>, <3,1>, <3,2>도 마찬가지입니다. 따라서, 필터링이 걸리기 전에, 결과값은 아래와 같습니다.

 

 

 여기서 where로 한 번 더 걸러봅시다. t2.b는 레코드의 마지막 필드입니다. 이 값이 1인 것만 뽑아 봅시다.

 

 

 최종적으로 t2.b = 1 조건에 의해서 2개만 뽑히게 되고, 결과값은 아래와 같습니다.

 

 

 outer join에서, on절과 where절이 차이가 있다는 것입니다. 꽤 긴 글이였지만, 이 글의 내용을 요약하면 1문장으로 요약할 수 있습니다. 그러면 inner join에서 on절과, where절은 같을까요? 더 정확하게 말해서, on 절에 있는 내용을 where 절로 치환해서, 같은 결과를 얻을 수 있을까요? 만약에 그렇다면 왜 그럴까요?