챕터 5에서는 여러 테이블의 데이터를 함께 가져오는 다중 테이블 쿼리에 대해 공부했고, 가장 단순하고 일반적인 조인인 INNER JOIN을 살펴봤었다. 이번에는 더 다양한 조인에 대해 공부해보겠다.

• 다중 테이블 쿼리 포스팅 다시보기
• 집합 연산자 포스팅 다시보기

Outer Join

아래 그림으로 여러 JOIN의 활용법을 한 눈에 알 수 있다.

사실상 어떤 테이블을 먼저 위치시키느냐에 따라 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과는 같을 수 있다. 참조하는 테이블을 JOIN문에 넣는것이 좋아보인다.

OUTER JOIN이 INNER JOIN과 다른 가장 큰 점은 참조하는 테이블에 행이 없을 경우에도 결과를 반환하느냐이다. OUTER JOIN은 조인 조건을 만족하는 행이 없는 경우에도 해당 열의 값을 추가한다. 만약, 조인 조건이 실패하면 값은 null로 표시된다.

예제 : 영화별로 DVD 재고가 몇 개씩 있는지 알아보자.

• 결과셋에 나타나야 하는 컬럼 : 영화ID, 영화제목, 재고 수
• 영화ID, 영화제목 → film 테이블 참조
• 재고수 → inventory 테이블 참조

이때 만약 INNER JOIN을 사용한다면 문제가 발생할 수 있다.

SELECT
    f.film_id
    , f.title
    , count(*) num_inv
FROM film f
    INNER JOIN inventory i
    ON f.film_id = i.film_id
GROUP BY f.film_id, f.title ;

film 테이블에는 총 1,000개의 영화가 있음에도 불구하고 958개의 결과값만 나타난다. 왜냐하면 INNER JOIN을 사용하여 inventory 테이블에서 재고가 1개도 없는 영화를 제거했기 때문이다.

그럼 OUTER JOIN을 사용해보자.

SELECT
    f.film_id
    , f.title
    , count(i.inventory_id) num_inv
FROM film f
    LEFT OUTER JOIN inventory i
    ON f.film_id = i.film_id
GROUP BY f.film_id, f.title ;

1,000개의 결과값이 모두 나타나게 되고, inventory 테이블에서 값이 null인 영화들은 재고수가 0으로 나타나게 된다.

Left / Right Outer Join

• LEFT OUTER JOIN : JOIN 왼쪽에 있는 테이블이 결과셋의 행 수를 결정한다.
• RIGHT OUTER JOIN : JOIN 오른쪽에 있는 테이블이 결과셋의 행 수를 결정한다.

Three-way Outer Join

3개의 테이블을 OUTER JOIN 하는 것을 말한다. (한 테이블을 다른 두 테이블과 외부조인)

예제 : DVD별로 영화ID, 영화제목, 재고ID, 대여날짜를 조회해보자.

• 영화ID와 영화제목은 film 테이블에, 재고ID는 inventory 테이블에, 대여날짜는 rental 테이블에 있으므로 3개의 테이블을 조인해야 한다.

SELECT
    f.film_id
    , f.title
    , i.inventory_id
    , r.rental_date
FROM film f
    LEFT OUTER JOIN inventory i
    ON f.film_id = i.film_id
    LEFT OUTER JOIN rental r
    ON i.inventory_id = r.inventory_id
WHERE f.film_id
    BETWEEN 13 AND 15 ;

inventory 테이블에 있는 모든 대여정보가 나타나지만, 영화ID 14 Alice Fantasia는 열의 값이 null이 된다. (inventory 테이블에 없기 때문)

Cross Join

두 테이블의 데카르트 곱을 생성하고자 할 때 사용한다. 결과셋의 행 갯수는 테이블 A의 행수 * 테이블 B의 행수가 된다.

실제 업무에서는 많이 사용되지 않는다고 하지만, 교차조인을 사용하는 좋은 예제를 살펴보자.

예제 : 2005년의 일별 영화 대여 횟수를 표시하는 보고서를 만들어보자.

2005년은 365일일까 365일일까? 그럼 300개가 넘는 행은 어떻게 쉽게 만들 수 있을까? 가 출발점이 된다.

먼저 아래 3개 집합을 사용해서 데카르트 곱을 계산하면 400개의 행이 만들어진다.

• 1씩 증가하는 0~9
• 10씩 증가하는 0~90
• 100씩 증가하는 0~300

다음으로 DATE_ADD()함수를 이용해 위의 결과값의 각 숫자를 2005년 1월 1일에 더해주고, 필터조건으로 2006년의 모든 날짜를 지운다.

SELECT
    date_add('2005-01-01', INTERVAL (ones.num + tens.num + hundreds.num) DAY ) dt
FROM
        (SELECT 0 num UNION ALL
        SELECT 1 num UNION ALL
        SELECT 2 num UNION ALL
        SELECT 3 num UNION ALL
        SELECT 4 num UNION ALL
        SELECT 5 num UNION ALL
        SELECT 6 num UNION ALL
        SELECT 7 num UNION ALL
        SELECT 8 num UNION ALL
        SELECT 9 num) ones
    CROSS JOIN
        (SELECT 0 num UNION ALL
        SELECT 10 num UNION ALL
        SELECT 20 num UNION ALL
        SELECT 30 num UNION ALL
        SELECT 40 num UNION ALL
        SELECT 50 num UNION ALL
        SELECT 60 num UNION ALL
        SELECT 70 num UNION ALL
        SELECT 80 num UNION ALL
        SELECT 90 num) tens
    CROSS JOIN
        (SELECT 0 num UNION ALL
        SELECT 100 num UNION ALL
        SELECT 200 num UNION ALL
        SELECT 300 num) hundreds
WHERE date_add('2005-01-01', INTERVAL (ones.num + tens.num + hundreds.num) DAY) <'2006-01-01'
ORDER BY 1 ;

이렇게 하면 아래와 같이 2005년의 일별 테이블이 만들어진다.
DATE_ADD()함수를 사용하면 좋은 점은 서버가 알아서 윤일여부를 파악하기 때문에 2월 29일이 있는 해의 경우 자동으로 포함된다는 점이다.

총 365개의 행이 만들어졌다.
그럼 이번에는 rental 테이블과 조인해보자. rental 테이블을 기준으로 지금 만든 365개 행의 테이블과 조인해야 하는데, 영화를 대여하지 않은 날을 비롯한 모든 날이 포함되어야 하므로 RIGHT OUTER JOIN을 사용한다.

SELECT
    days.dt
    , COUNT(r.rental_id) num_rentals
FROM rental r
    RIGHT OUTER JOIN
     (SELECT DATE_ADD('2005-01-01', INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
      FROM (SELECT 0 num
            UNION ALL
            SELECT 1 num
            UNION ALL
            SELECT 2 num
            UNION ALL
            SELECT 3 num
            UNION ALL
            SELECT 4 num
            UNION ALL
            SELECT 5 num
            UNION ALL
            SELECT 6 num
            UNION ALL
            SELECT 7 num
            UNION ALL
            SELECT 8 num
            UNION ALL
            SELECT 9 num) ones
               CROSS JOIN
           (SELECT 0 num
            UNION ALL
            SELECT 10 num
            UNION ALL
            SELECT 20 num
            UNION ALL
            SELECT 30 num
            UNION ALL
            SELECT 40 num
            UNION ALL
            SELECT 50 num
            UNION ALL
            SELECT 60 num
            UNION ALL
            SELECT 70 num
            UNION ALL
            SELECT 80 num
            UNION ALL
            SELECT 90 num) tens
               CROSS JOIN
           (SELECT 0 num
            UNION ALL
            SELECT 100 num
            UNION ALL
            SELECT 200 num
            UNION ALL
            SELECT 300 num) hundreds
      WHERE DATE_ADD('2005-01-01', INTERVAL (ones.num + tens.num + hundreds.num) DAY) < '2006-01-01'
     ) days
    ON days.dt = date(r.rental_date)
GROUP BY days.dt
ORDER BY 1 ;

365개의 행이 결과셋으로 출력되었고, 대여횟수가 1도 없는 날은 0으로 표시되었다.
num_rentals를 정렬함으로써, 어느날 가장 대여횟수가 많았는지도 볼 수 있다.
그냥 이런식으로도 할 수 있다 정도로 넘어가본다.. ㅎㅎㅎ

Natural Join

NATURAL JOIN은 데이터베이스 서버가 알아서 조인조건을 추론해서 두 테이블을 조인하는 방식이다. 따라서 ON 에 조건을 따로 입력할 필요가 없다.
결론 : 쓰지 않는 것이 좋다.