37. 조인의 새 친구: LATERAL

서른일곱번째 SQL BOOSTER 이어지는 이야기입니다.

LATERAL JOIN에 대해 알아보는 시간입니다. LATERAL JOIN을 활용하면 기존에 조인으로는 절대(?) 만들수 없던, 조인 결과도 비교적 쉽게 만들어 낼 수 있습니다.

PDF로 다운로드하기

SQL_Booster_이어지는이야기37_조인의새친구_LATERAL.pdf

396.6KB

SQL BOOSTER 이어지는 이야기 몰아보기: https://cafe.naver.com/dbian/6444

2000년대 초반부터 DBMS와 SQL을 다룬 개발자들에게 다소 생소한 조인이 있습니다. 바로 LATERAL JOIN입니다. LATERAL JOIN은 인라인 뷰 안에서, 인라인 뷰 바깥 테이블의 값을 참조할 수 있는 조인 기능입니다.

37-1. 매장별 주문금액 구하기

‘2018년 4월 1일’에 오픈한 매장들에 대해 매장별 주문금액 합계를 구하려고 합니다. 아래와 같이 SQL을 작성할 수 있습니다.

[SQL-37-1-1]
SELECT  T1.SHOP_ID ,MAX(T1.SHOP_NM) SHOP_NM ,MAX(T1.SHOP_START_YMD) SHOP_START_YMD
        ,SUM(T2.ORD_AMT) SUM_ORD_AMT
FROM    STARTDBORA.MS_SHOP T1
        INNER JOIN STARTDBORA.TR_ORD T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
WHERE   T1.SHOP_START_YMD = '20180401'
GROUP BY T1.SHOP_ID
ORDER BY T1.SHOP_ID;

SHOP_ID|SHOP_NM      |SHOP_START_YMD|SUM_ORD_AMT|
-------+-------------+--------------+-----------+
S001   |New York-1st |20180401      |   50272000|
S015   |Charlotte-1st|20180401      |   63211700|
S034   |Columbus-2nd |20180401      |   36704900|
S035   |Charlotte-2nd|20180401      |   36716200|
S043   |Chicago-3rd  |20180401      |   13070000|
SQL
복사

위 SQL은 아래와 같이 인라인 뷰를 활용해 해결할 수도 있습니다. TR_ORD 테이블에 대한 집계를 인라인 뷰로 별도 처리 후 MS_SHOP과 조인을 합니다. 위 SQL을 아래와 같이 변경할 이유가 특별히 있는 것은 아닙니다. 상황에 따라 처리 조건이 복잡하거나 조인할 대상이 많아지면, 아래와 같은 패턴을 고려하게 됩니다.

[SQL-37-1-2]
SELECT  T1.SHOP_ID ,T1.SHOP_NM ,T1.SHOP_START_YMD
        ,T2.SUM_ORD_AMT
FROM    STARTDBORA.MS_SHOP T1
        INNER JOIN (
            SELECT  X.SHOP_ID
                    ,SUM(X.ORD_AMT) SUM_ORD_AMT
            FROM    STARTDBORA.TR_ORD X
            GROUP BY X.SHOP_ID
            ) T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
WHERE   T1.SHOP_START_YMD = '20180401'
ORDER BY T1.SHOP_ID;
SQL
복사

위 SQL과 같이 인라인 뷰를 활용한 조인은, 바깥쪽 테이블의 컬럼을 인라인 뷰 안에서는 사용할 수 없습니다. 예를 들어, 아래와 같은 SQL은 에러가 발생하며 실행되지 않습니다.

[SQL-37-1-3]
SELECT  T1.SHOP_ID ,T1.SHOP_NM ,T2.SUM_ORD_AMT
FROM    STARTDBORA.MS_SHOP T1
        INNER JOIN (
            SELECT  X.SHOP_ID
                    ,SUM(X.ORD_AMT) SUM_ORD_AMT
            FROM    STARTDBORA.TR_ORD X
            WHERE   X.SHOP_ID = T1.SHOP_ID -- > 바깥쪽 테이블의 컬럼(T1.SHOP_ID)를 사용할 수 없다.
            GROUP BY X.SHOP_ID
            ) T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
WHERE   T1.SHOP_START_YMD = '20180401'
ORDER BY T1.SHOP_ID;

ORA-00904: "T1"."SHOP_ID": 부적합한 식별자
SQL
복사

일반적인 조인이 아닌, LATERAL JOIN을 활용하면 인라인 뷰 안에서도 바깥쪽 테이블의 컬럼을 사용할 수 있습니다. 위 SQL을 아래와 같이 LATERAL JOIN으로 변경하면 정상적으로 실행됩니다.

[SQL-37-1-4]
SELECT  T1.SHOP_ID ,T1.SHOP_NM ,T2.SUM_ORD_AMT
FROM    STARTDBORA.MS_SHOP T1
        INNER JOIN LATERAL (              -- > JOIN 구문에 LATERAL 추가
            SELECT  X.SHOP_ID
                    ,SUM(X.ORD_AMT) SUM_ORD_AMT
            FROM    STARTDBORA.TR_ORD X
            WHERE   X.SHOP_ID = T1.SHOP_ID -- > LATERAL이므로 바깥쪽 테이블의 컬럼 사용 가능
            GROUP BY X.SHOP_ID
            ) T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
WHERE   T1.SHOP_START_YMD = '20180401'
ORDER BY T1.SHOP_ID;
SQL
복사

37-2. 매장별 최근 주문 n 건 보여주기

이번에는 ‘2018년 4월 1일’에 오픈한 매장의 최근 주문을 매장별로 세 건씩 보여주려고 합니다. LATERAL JOIN과 함께 인라인 뷰 안에서 FETCH 구문을 활용하면 원하는 결과를 어렵지 않게 얻을 수 있습니다. 다음과 같습니다. 매장별로 최근 주문 세 건씩이 조회되고 있습니다.

[SQL-37-2-1]
SELECT  T1.SHOP_ID ,T1.SHOP_NM ,T1.SHOP_START_YMD ,T2.ORD_DTM ,T2.ORD_NO ,T2.ORD_AMT
FROM    STARTDBORA.MS_SHOP T1
        INNER JOIN LATERAL (              
            SELECT  X.SHOP_ID ,X.ORD_DTM ,X.ORD_NO ,X.ORD_AMT
            FROM    STARTDBORA.TR_ORD X
            WHERE   X.SHOP_ID = T1.SHOP_ID -- > LATERAL이므로 바깥쪽 테이블의 컬럼 사용 가능
            ORDER BY X.ORD_DTM DESC
            FETCH FIRST 3 ROWS ONLY       -- > 인라인 뷰에서 세 건만 추출되도록 처리
            ) T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
WHERE   T1.SHOP_START_YMD = '20180401'
ORDER BY T1.SHOP_ID ,T2.ORD_DTM DESC;

SHOP_ID|SHOP_NM      |SHOP_START_YMD|ORD_DTM                |ORD_NO|ORD_AMT|
-------+-------------+--------------+-----------------------+------+-------+
S001   |New York-1st |20180401      |2025-01-25 07:04:00.000|730081|  18000|
S001   |New York-1st |20180401      |2025-01-25 07:02:00.000|729948|  10400|
S001   |New York-1st |20180401      |2025-01-24 07:02:00.000|729680|  10800|
S015   |Charlotte-1st|20180401      |2025-01-25 07:12:00.000|730220|  10800|
S015   |Charlotte-1st|20180401      |2025-01-25 07:06:00.000|730151|   4500|
S015   |Charlotte-1st|20180401      |2025-01-25 07:04:00.000|730093|   5000|
S034   |Columbus-2nd |20180401      |2025-01-25 07:04:00.000|730101|   9900|
S034   |Columbus-2nd |20180401      |2025-01-25 07:02:00.000|729974|   4500|
S034   |Columbus-2nd |20180401      |2025-01-24 07:04:00.000|729759|  14300|
S035   |Charlotte-2nd|20180401      |2025-01-25 07:12:00.000|730223|   9900|
…생략…
SQL
복사

이처럼 LATERAL JOIN을 사용하면 ‘바깥 테이블의 값’을 인라인 뷰 안에서 직접 활용해, 매장별로 최신 주문만 뽑거나 조건에 따라 동적인 집계를 해야 하는 문제도 해결할 수 있습니다.

우리가 주의할 사항이 있습니다. LATERAL JOIN을 알았으니 이제 무조건 LATERAL을 여기저기 사용하면 될까요? 절대 그런 실수를 해서는 안 됩니다. LATERAL은 성능상 좋은 점도 있지만 나쁜 점도 있습니다. 가능하면 LATERAL이 아닌, 전통적인 방식의 조인 구문을 사용하는 것이 좋습니다. 원하는 데이터 집합이 LATERAL 처리가 필요한 경우만 LATERAL을 고려하기 바랍니다. 오라클이 아닌 다른 DBMS라면, 성능 목적으로 LATERAL을 사용해야 할 때가 제법 있습니다. 이역시도 성능에 도움이 진짜 되는지, LATERAL을 적용한 결과 집합이 원하는 집합인지를 판단하고 사용해야 합니다.

DBMS나 버전에 따라 LATERAL을 지원하지 않을 수도 있습니다. LATERAL을 사용할 수 없다면, 아래와 같이 분석함수와 인라인 뷰를 활용해 매장별 최근 주문 n 건을 처리할 수 있습니다.

[SQL-37-2-2]
SELECT  T3.SHOP_ID ,T3.SHOP_NM ,T3.SHOP_START_YMD ,T3.ORD_DTM ,T3.ORD_NO ,T3.ORD_AMT
FROM    (
        SELECT  T1.SHOP_ID ,T1.SHOP_NM ,T1.SHOP_START_YMD ,T2.ORD_DTM ,T2.ORD_NO ,T2.ORD_AMT
                ,ROW_NUMBER() OVER(PARTITION BY T1.SHOP_ID ORDER BY T2.ORD_DTM DESC) RNK
        FROM    STARTDBORA.MS_SHOP T1
                INNER JOIN STARTDBORA.TR_ORD T2
                    ON (T2.SHOP_ID = T1.SHOP_ID)
        WHERE   T1.SHOP_START_YMD = '20180401'
        ) T3
WHERE   T3.RNK <= 3;
SQL
복사

준비한 내용은 여기까지입니다.

Open DBMS로 배우는 SQL 튜닝 입문!

StartUP Tuning For PostgreSQL의 오프라인 강의 수강 모집중입니다. 많은 관심 부탁드립니다.

https://cafe.naver.com/dbian/8709