[AWS Redshift] FULL JOIN과 COALESCE

오늘은 full join과 coalesce를 같이 활용해보았는데 이것을 한번 정리해 보려고 합니다.

 

1. full join

   두개의 테이블의 모든 공통 요소를 결합하는 join형태로서 둘중 하나라도 값을 가지고 있다면 병합됩니다.(합집합)

2. coalesce

   주로 사용되는 목적은 NULL처리 입니다. NULL인경우 어떤값으로 변경하도록 하는데 많이 사용합니다.

 

그럼 이둘을 합쳐서 사용해보면 어떤 효과가 발생할까요??

SELECT 
    COALESCE(dt.id, dt2.id) AS id, 
    COALESCE(dt.create_date, dt2.create_date) AS create_date, 
    (COALESCE(dt.driving_sec, 0) + COALESCE(dt2.cancel_driving_sec, 0)) AS total_driving_sec
FROM 
    (
        SELECT 
            d.id, 
            DATE(d.created) AS create_date,
            SUM(datediff(second, d.created, d.start_time::timestamp) + datediff(second, d.start_time::timestamp, d.end_time::timestamp)) AS driving_sec
        FROM 
            drive d 
        WHERE 
            d.drive_state = 'done' 
            AND d.created >= '2023-07-01'
        GROUP BY 
            d.id, DATE(d.created)
    ) AS dt
FULL JOIN 
    (
        SELECT 
            d.id, 
            DATE(d.created) AS create_date, 
            SUM(datediff(second, d.created, t.cancel_date)) AS cancel_driving_sec
        FROM 
            drive d 
        LEFT JOIN 
            trip t ON t.drive_id = d.id
        WHERE 
            d.drive_state = 'canceled' 
            AND d.created >= '2023-07-01'
        GROUP BY 
            d.id, DATE(d.created)
    ) AS dt2 ON dt.id = dt2.id AND dt.create_date = dt2.create_date

 

 

다음과 같은 쿼리에서  coalesce없이 두테이블을 Full JOIN하면 나오는 테이블의 Column 구조는 다음과 같습니다.

이러한 상황에서, driver_id와 create_date 같은 필드에 대해 COALESCE 함수를 사용함으로써, 두 테이블 중 어느 한 쪽에라도 값이 존재하는 경우 해당 값을 선택하게 됩니다. 만약 양쪽 테이블 모두에 값이 존재한다면, FULL JOIN 조건(ON dt.driver_id = dt2.driver_id AND dt.create_date = dt2.create_date)에 의해 자동적으로 일치하는 값을 가지므로, 어느 쪽 값을 선택해도 동일한 결과를 얻게 됩니다.

 

조금 복잡하게 설명했지만! 이런경우가 생긴다면 한번 고려하여 사용해보시기를 바랍니다