PostgreSQL의 LATERAL JOIN을 이용한 SQL에서의 행별 반복 시뮬레이션

소개

관계형 데이터베이스 영역에서 SQL은 주로 세트 기반 작업, 즉 전체 데이터 세트를 한 번에 처리하도록 설계되었습니다. 이 패러다임은 일반 프로그래밍 언어의 "for-each" 루프와 유사한 행별 처리에 자연스럽게 맞는 시나리오에 직면할 때 종종 어려운 과제를 제시합니다. 기존 SQL 구문이 때때로 이러한 동작을 모방할 수 있지만, 특정 사용 사례의 경우 복잡하거나 비효율적인 경우가 많습니다. 이것이 바로 PostgreSQL의 LATERAL JOIN이 강력하고 우아한 솔루션으로 등장하는 이유입니다. 이를 통해 하위 쿼리가 이전 FROM 절 항목의 열을 참조할 수 있어 효과적으로 행별 계산을 가능하게 하고 복잡한 데이터 조작을 위한 새로운 가능성을 열어줍니다. 이 기사에서는 LATERAL JOIN이 SQL의 세트 기반 처리와 행 기반 처리 간의 격차를 해소하는 방법, 실제 예제 및 유용성에 대한 통찰력을 제공합니다.

행별 로직을 위한 LATERAL JOIN 이해

LATERAL JOIN의 성능을 완전히 이해하려면 몇 가지 핵심 개념을 먼저 파악하는 것이 중요합니다.

핵심 개념

• 하위 쿼리 독립성: 일반적으로 JOIN 조건 또는 SELECT 목록에서 실행되는 하위 쿼리는 메인 쿼리의 FROM 절의 열을 참조할 수 없습니다. 행별로 작동하지만 복잡한 작업의 경우 비효율적인 경우가 많은 상관 하위 쿼리를 제외하고는 그렇습니다.
• 세트 기반 vs. 행 기반 처리: SQL은 기본적으로 데이터 세트로 작동합니다. 단순한 JOIN은 세트를 결합합니다. SQL에서 "행별"이라고 말할 때는 하위 쿼리 또는 함수의 결과가 다른 테이블의 개별 행의 값에 의존하는 작업을 참조합니다.
• LATERAL 키워드: LATERAL 키워드를 하위 쿼리 또는 FROM 절 항목에 적용하면 해당 항목이 동일한 FROM 목록에서 이전에 나타나는 FROM 항목에서 제공한 열을 참조할 수 있습니다. 이것이 "for-each"와 유사한 동작을 가능하게 하는 중요한 요소입니다.

LATERAL JOIN의 원리

사용자 테이블이 있고 각 사용자에 대해 최근 3개의 주문을 찾고 싶다고 상상해 보세요. LATERAL JOIN이 없으면 복잡한 창 함수나 여러 상관 하위 쿼리를 사용해야 할 수 있습니다. LATERAL JOIN을 사용하면 각 사용자 행을 반복하고 각 사용자에 대해 최신 3개의 주문을 검색하는 하위 쿼리를 실행하는 것으로 생각할 수 있습니다.

SQL 엔진은 LATERAL JOIN을 만나면 먼저 왼쪽 테이블( LATERAL JOIN 앞의 테이블)을 처리합니다. 왼쪽 테이블에서 생성된 각 행에 대해 하위 쿼리(오른쪽)를 실행하여 현재 행의 값을 하위 쿼리로 전달합니다. 그런 다음 이 하위 쿼리의 결과가 왼쪽 테이블의 현재 행과 조인됩니다. 이는 효과적으로 "for-each" 루프를 시뮬레이션합니다. 외부 쿼리의 각 행에 대해 해당 행의 데이터를 사용하여 작업을 수행합니다.

구현 및 적용 시나리오

실제 예제를 통해 설명해 보겠습니다.

시나리오 1: 그룹별 상위 N개 관련 레코드 찾기

users와 orders라는 두 테이블이 있다고 가정합니다. 각 사용자에 대해 가장 최근 주문 3개를 찾고 싶습니다.

users 테이블:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

INSERT INTO users (user_id, username) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

orders 테이블:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO orders (order_id, user_id, order_date, amount) VALUES
(101, 1, '2023-01-10', 50.00),
(102, 1, '2023-01-15', 75.00),
(103, 2, '2023-01-12', 20.00),
(104, 1, '2023-01-20', 100.00),
(105, 3, '2023-01-05', 30.00),
(106, 2, '2023-01-18', 45.00),
(107, 1, '2023-01-25', 120.00),
(108, 3, '2023-01-10', 60.00),
(109, 2, '2023-01-22', 90.00),
(110, 1, '2023-01-28', 80.00);

LATERAL JOIN 사용:

SELECT
    u.username,
    o.order_id,
    o.order_date,
    o.amount
FROM
    users AS u,
    LATERAL (
        SELECT *
        FROM orders AS ord
        WHERE ord.user_id = u.user_id
        ORDER BY ord.order_date DESC
        LIMIT 3
    ) AS o;

이 예제에서 각 user 행(u)에 대해 LATERAL 하위 쿼리( o로 별칭 지정됨)가 실행됩니다. 이 하위 쿼리는 order_date를 기준으로 내림차순으로 정렬하고 LIMIT 3으로 가져와 현재 u.user_id로 orders를 필터링합니다. 결과는 user 행에 다시 조인됩니다. 이는 일반적인 분석 작업인 그룹별 상위 N개 관련 레코드를 가져오는 깔끔한 방법을 제공합니다.

시나리오 2: 동적 열 생성 또는 복잡한 계산

각 행에 계산 매개변수가 포함된 테이블이 있고 각 행에 대해 다른 계산을 수행하려는 경우를 상상해 보세요.

CREATE TABLE calculation_params (
    param_id INT PRIMARY KEY,
    value1 INT,
    value2 INT
);

INSERT INTO calculation_params (param_id, value1, value2) VALUES
(1, 10, 5),
(2, 20, 4),
(3, 15, 3);

이제 각 행에 대해 value1 * value2 및 value1 + value2를 계산하지만 함수 또는 파생 테이블에서 반환된 구조로 넣고 싶다고 해봅시다.

SELECT
    cp.param_id,
    calcs.product_result,
    calcs.sum_result
FROM
    calculation_params AS cp,
    LATERAL (
        SELECT
            cp.value1 * cp.value2 AS product_result,
            cp.value1 + cp.value2 AS sum_result
    ) AS calcs;

여기서 LATERAL 쿼리는 행별로 적용되는 함수 역할을 하여 원본 행의 데이터를 기반으로 파생 열을 생성합니다. 이는 테이블 반환 함수 또는 VALUES 절과 결합할 때 특히 유용할 수 있습니다.

시나리오 3: 행별 배열 또는 JSONB 풀기

LATERAL JOIN의 일반적인 사용 사례는 테이블 반환 함수, 특히 unnest() 또는 jsonb_array_elements()와 함께 사용하는 것입니다. 이때 배열 또는 JSONB 데이터는 메인 테이블의 열에 저장됩니다.

CREATE TABLE product_tags (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[] -- tags 배열
);

INSERT INTO product_tags (product_id, name, tags) VALUES
(1, 'Laptop', ARRAY['electronics', 'computing', 'portable']),
(2, 'Keyboard', ARRAY['electronics', 'peripherals']),
(3, 'Mouse', ARRAY['electronics', 'peripherals', 'wireless']);

각 제품을 개별 태그로 나열하려면:

SELECT
    pt.product_id,
    pt.name,
    tag_element AS tag
FROM
    product_tags AS pt,
    LATERAL unnest(pt.tags) AS tag_element;

LATERAL이 없으면 unnest()는 모든 행의 모든 태그를 단일 세트로 풀어서 해당 제품과 효율적으로 연결하기 어렵게 만듭니다. LATERAL은 unnest()가 각 product_tags 행에 대해 호출되어 해당 행의 tags 배열을 전달하도록 합니다.

LEFT LATERAL JOIN을 사용한 선택적 결과

LEFT JOIN과 마찬가지로 LEFT LATERAL JOIN을 사용하면 LATERAL 하위 쿼리가 행을 생성하지 않더라도 기본 테이블의 행을 반환할 수 있습니다. "for-each" 작업이 모든 외부 행에 대한 결과를 생성하지 않을 수 있지만 여전히 외부 행을 포함하려면 이 작업이 중요합니다.

-- 'orders' 테이블에 user 3의 주문이 없다고 가정합니다.
-- Charlie(user_id = 3)가 주문이 하나만 있었고 3개를 가져오려고 하면 하나만 반환됩니다.
-- Charlie에게 주문이 0개였다면, 단순 LATERAL JOIN은 Charlie를 제외합니다.

ALTER TABLE orders DISABLE TRIGGER ALL; -- 예제를 위해 임시로 외래 키 검사 비활성화
DELETE FROM orders WHERE user_id = 3;
ALTER TABLE orders ENABLE TRIGGER ALL;

SELECT
    u.username,
    o.order_id,
    o.order_date,
    o.amount
FROM
    users AS u
LEFT JOIN LATERAL (
        SELECT *
        FROM orders AS ord
        WHERE ord.user_id = u.user_id
        ORDER BY ord.order_date DESC
        LIMIT 3
    ) AS o ON TRUE; -- LATERAL 하위 쿼리의 WHERE 절에서 조인 조건이 처리되므로 ON TRUE가 일반적입니다.

이 쿼리는 이제 Charlie가 관련 주문이 없더라도 포함하지만 주문 열에는 NULL 값이 표시됩니다.

결론

PostgreSQL의 LATERAL JOIN은 SQL에서 진정한 행 종속적 작업을 가능하게 하는 매우 다양하고 강력한 기능으로, 세트 기반 쿼리에 "for-each" 루프 의미론을 효과적으로 가져옵니다. 이전 FROM 항목의 열을 참조하는 기능은 그룹당 N개의 상관 항목 찾기, 행별 파생 데이터 동적으로 생성, 행별 배열 또는 JSONB 데이터 효율적으로 풀기 등과 같은 문제에 대한 우아한 솔루션을 제공합니다. LATERAL JOIN을 이해하고 활용함으로써 개발자는 복잡한 분석 및 데이터 변환 작업을 위해 더 간결하고 읽기 쉬우며 종종 더 성능이 뛰어난 쿼리를 작성할 수 있으며, 세트 기반 쿼리와 선언적 SQL이라는 두 세계 간의 격차를 해소할 수 있습니다. 이전에는 번거롭거나 비효율적인 시나리오를 처리할 수 있도록 SQL을 지원하며 고급 PostgreSQL 사용자에게 필수적인 도구가 됩니다.