sql join on과 where 그리고 outer join

left outer join 시 on 절에는 우측(널값으로 채워지는쪽의) 추가 제약조건을 넣고 좌측의 추가 제약조건은 where절에 넣어야 한다. 라는데..

나는 이해가 되지 않는 관계로.. 패스한다... 

아래는 postgresql 을 기준으로 만들어진 left outer join 예제이다.

테스트용 테이블 만들기 귀찮아서 그냥 샘플 데이터용으로 만들었다.

left outer join 이라는 용어에서도 알 수 있듯이 왼쪽 테이블이 기준이되는 아우터 조인이다.

즉, 기준이 되는 왼쪽 테이블과 우측 테이블을 비교하여 일치하는 데이터가 없더라도 기준 테이블인 왼쪽 테이블의 데이터는 출력한다.

WITH t1
     AS (SELECT 1 AS no, 4 AS digit
         UNION ALL
         SELECT 2 AS no, 5 AS digit
         UNION ALL
         SELECT 3 AS no, 6 AS digit),
     t2
     AS (SELECT 1 AS no, 7 AS digit
         UNION ALL
         SELECT 2 AS no, 8 AS digit
         UNION ALL
         SELECT 2 AS no, 9 AS digit)
SELECT *
FROM   t1 LEFT JOIN t2
ON     t1.no = t2.no

on절 아래로 조건을 추가해보자.

WITH t1
     AS (SELECT 1 AS no, 4 AS digit
         UNION ALL
         SELECT 2 AS no, 5 AS digit
         UNION ALL
         SELECT 3 AS no, 6 AS digit),
     t2
     AS (SELECT 1 AS no, 7 AS digit
         UNION ALL
         SELECT 2 AS no, 8 AS digit
         UNION ALL
         SELECT 2 AS no, 9 AS digit)
SELECT *
FROM   t1 LEFT JOIN t2
ON     t1.no = t2.no
AND  t2.digit = 8

이제 여기서 위와는 다르게 추가로 작성한 on 아래 and 절을 지우고 where조건을 추가해보자.

WITH t1
     AS (SELECT 1 AS no, 4 AS digit
         UNION ALL
         SELECT 2 AS no, 5 AS digit
         UNION ALL
         SELECT 3 AS no, 6 AS digit),
     t2
     AS (SELECT 1 AS no, 7 AS digit
         UNION ALL
         SELECT 2 AS no, 8 AS digit
         UNION ALL
         SELECT 2 AS no, 9 AS digit)
SELECT *
FROM   t1 LEFT JOIN t2
ON     t1.no = t2.no
WHERE t2.digit = 8

이제 on과 where 절의 차이점이 보인다.

흔히 아우터 조인의 결과로 알고 있는 좌측 기준 데이터가 출력되어야하는 현상이 깨졌기 떄문이다.

조인되는 범위가 다르다 라고 한다. -> 나는 그냥 시점이 다르다고 이해했다.

on절로 아우터 조인된 결과에 추가적으로 조건을 더 걸어 원하는 결과를 뽑아내는 것.  이게 on과 where의 차이점이라고 볼 수 있겠다.

아래 출처에 이미지와 함께 설명이 잘 되어 있다. 그림을 보면 이해가 잘 될 것이다.

[출처] http://rapapa.net/?p=311#comment-6018