SQL

ANSI SQL: LATERAL

dewstream 2025. 5. 19. 08:00
728x90

※ ANSI SQL LATERAL.
 
안녕하세요. 듀스트림입니다.
 
오늘의 포스팅은 ANSI SQL:1999 표준부터 도입된 기능인 LATERAL JOIN을 PostgreSQL 관점에서 작성한 내용입니다.

1. LATERAL?

LATERAL 키워드는 FROM 절에서 서브쿼리나 테이블 함수가 앞선 테이블의 컬럼을 참조할 수 있도록 허용합니다.

이를 통해 서브쿼리나 테이블 함수는 각 행에 대해 독립적으로 실행되어, 외부 쿼리의 컬럼 값을 동적으로 사용할 수 있습니다.

즉, LATERAL을 사용하면 서브쿼리나 함수가 외부 쿼리의 컬럼을 참조하여 계산을 수행할 수 있게 됩니다.

요약하자면,
• LATERAL은 상관 서브쿼리(correlated subquery) 개념을 FROM 절에서 사용할 수 있게 해주는 기능입니다.
• LATERAL Inner 쿼리는 바깥에서 넘겨준 값을 파라미터로 받아, 바깥 행을 받을 때 마다 매번 재실행합니다.
+ PostgreSQL은 LATERAL을 Nested Loop 형태로 실행됩니다.

 

 

7.2. Table Expressions

7.2. Table Expressions # 7.2.1. The FROM Clause 7.2.2. The WHERE Clause 7.2.3. The GROUP BY and HAVING Clauses 7.2.4. GROUPING …

www.postgresql.org

Oracle에서 LATERAL을 잘 사용하지 않는 이유

Oracle에서는 LATERAL을 지원하지만, 자주 사용되지 않는 이유 중 하나는 스칼라 서브쿼리 캐시 기능 때문입니다.

Oracle은 스칼라 서브쿼리의 결과를 캐시하여 동일한 값에 대해 반복 계산을 피하고, 성능을 최적화합니다. 이러한 기능 덕분에 LATERAL을 사용하지 않아도 훨씬 효율적인 쿼리 작성이 가능합니다.

스칼라 서브쿼리와 LATERAL 쿼리 비교

구분 스칼라 서브쿼리 예시 LATERAL 예시
구조 SELECT u.id, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) FROM users u; SELECT u.id, o.order_count FROM users u CROSS JOIN LATERAL (SELECT COUNT(*) AS order_count FROM orders o WHERE o.user_id = u.id) o;
동작 방식 서브쿼리가 각 행에 대해 독립적으로 실행되며, 외부 쿼리의 컬럼을 참조하지 못함. 서브쿼리가 각 행에 대해 독립적으로 실행되며, 외부 쿼리의 컬럼을 참조할 수 있음.
성능 특성 동일한 계산을 반복할 수 있어 성능 저하 가능성 있음. LATERAL을 사용하면 외부 쿼리의 컬럼을 참조하여 효율적인 계산 가능.

스칼라 서브쿼리 예시

예를 들어 PostgreSQL은 아래와 같은 N:1 관계에서 서브쿼리를 반복적으로 실행하게 됩니다.

SELECT
  u.id,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)
FROM users u;

쿼리 분석

  1. FROM users u:
    • 이 부분은 users 테이블에서 모든 사용자(u)를 하나씩 선택하는 역할을 합니다.
    • 즉, users 테이블의 각 사용자에 대해 루프를 돌게 됩니다.
  2. SELECT u.id:
    • 루프가 돌 때마다 users 테이블의 현재 사용자(u)의 id 값을 가져옵니다.
    • 각 사용자에 대해 이 값을 출력합니다.
  3. 서브쿼리 (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id):
    • 각 사용자의 id에 대해 서브쿼리가 실행됩니다.
    • 서브쿼리는 orders 테이블에서 user_id가 현재 사용자(u.id)와 일치하는 모든 주문을 찾고, 그 수를 셉니다.
    • 서브쿼리 내부에서 orders 테이블을 반복하여 확인하는 방식이므로, 각 사용자마다 해당 사용자에 대한 주문 개수를 구하는 역할을 합니다.

 

루프 구조

  1. 초기화: users 테이블에서 첫 번째 사용자(u)를 가져옵니다.
  2. 첫 번째 사용자에 대해 서브쿼리 실행:
    • 첫 번째 사용자의 id를 가져옵니다 (u.id).
    • orders 테이블에서 user_id = u.id인 모든 레코드를 찾고, 해당 주문의 개수를 셉니다.
    • 이 값을 첫 번째 사용자와 함께 출력합니다.
  3. 두 번째 사용자에 대해 반복:
    • users 테이블에서 두 번째 사용자(u)를 가져옵니다.
    • 다시 orders 테이블에서 user_id = u.id인 주문의 개수를 셉니다.
    • 이 값을 두 번째 사용자와 함께 출력합니다.
  4. 모든 사용자에 대해 반복:
    • users 테이블의 모든 사용자에 대해 위 과정을 반복합니다.
    • 결과적으로 각 사용자 id와 해당 사용자가 가진 주문 개수가 출력됩니다.

LATERAL 예시

같은 결과를 내는 LATERAL 쿼리입니다.

SELECT 
  u.id,
  o.order_count
FROM 
  users u
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS count
    FROM orders o
    WHERE o.user_id = u.id
) o;

쿼리 분석

  1. FROM users u:
    • users 테이블에서 모든 사용자(u)를 하나씩 선택합니다.
    • users 테이블의 각 사용자에 대해 루프를 돌게 됩니다.
  2. CROSS JOIN LATERAL:
    • CROSS JOIN LATERAL은 users 테이블의 각 행에 대해 서브쿼리를 실행합니다. LATERAL 키워드는 서브쿼리가 외부 쿼리의 컬럼을 사용할 수 있게 만들어줍니다.
    • 즉, LATERAL을 사용하면 서브쿼리가 users 테이블의 id 값을 참조할 수 있게 됩니다. o.user_id = u.id 조건을 통해 각 사용자의 주문 개수를 세게 됩니다.
    • CROSS JOIN은 두 테이블을 모든 가능한 조합으로 결합하는 방식이지만, LATERAL과 함께 사용되면 서브쿼리가 각 행에 대해 실행되므로 실제로는 각 users 테이블의 행에 대해 한 번씩 실행됩니다.
  3. 서브쿼리:
    • 서브쿼리 SELECT COUNT(*) AS count FROM orders o WHERE o.user_id = u.id 에서는 orders 테이블에서 각 사용자에 맞는 주문을 찾고 그 개수를 셉니다.
    • WHERE o.user_id = u.id는 users 테이블의 각 user_id에 맞는 주문을 찾아서 개수를 셉니다.
    • 이 부분에서 중요한 점은 각 사용자의 id에 대해 서브쿼리가 독립적으로 실행되어, orders 테이블에서 해당 사용자의 주문 개수를 구한다는 점입니다.

 

루프 구조

  1. 초기화: users 테이블에서 첫 번째 사용자(u)를 가져옵니다.
  2. 첫 번째 사용자에 대해 CROSS JOIN LATERAL 실행:
    • 첫 번째 사용자의 id를 가져옵니다 (u.id).
    • CROSS JOIN LATERAL은 서브쿼리를 실행하여, orders 테이블에서 user_id = u.id에 해당하는 주문을 찾아 그 개수를 셉니다.
    • 결과적으로, 첫 번째 사용자(u.id = 1)에 대한 주문 개수를 구합니다.
  3. 두 번째 사용자에 대해 반복:
    • users 테이블에서 두 번째 사용자(u)를 가져옵니다.
    • 같은 방식으로 CROSS JOIN LATERAL을 사용하여, 해당 사용자(u.id = 2)에 대한 주문 개수를 구합니다.
  4. 모든 사용자에 대해 반복:
    • 이 과정을 users 테이블에 있는 모든 사용자에 대해 반복하여, 각 사용자에 대한 주문 개수를 구합니다.
• PostgreSQL에서 LATERAL 조인은 내부적으로 Nested Loop Join 형태로 동작합니다.
• 참조(의존성) 방향은 Inner(LATERAL) ← Outer로 LATER 내부가 외부(왼쪽) 테이블을 참조하는 형태입니다.
• NLJ 실행 루프 방향은 Outer → Inner(LATERAL) 순서로 루프가 도는 구조입니다.
• 실행 계획 구조에서는 Outer가 먼저고 Inner가 나중으로 나오며, 평가 순서는 왼쪽에서 오른쪽으로 평가합니다.

 

정리하자면,

  • 스칼라 서브쿼리에서는 users 테이블의 각 사용자에 대해 서브쿼리가 독립적으로 실행되어 user_id에 맞는 주문 개수를 구했습니다.
  • LATERAL을 사용한 쿼리에서는 서브쿼리가 users 테이블의 각 행에 대해 실행되므로, 서브쿼리가 외부 쿼리의 user_id 값을 참조하면서 효율적인 방식으로 각 사용자의 주문 개수를 계산할 수 있습니다.
  • LATERAL을 사용함으로써 서브쿼리가 매번 users 테이블의 각 행에 대해 계산되며, 성능 면에서도 효율적이고 이해하기 쉬운 방식으로 동작합니다. LATERAL은 서브쿼리가 외부 쿼리의 컬럼 값을 참조할 수 있도록 하여, 각 사용자에 대해 독립적인 계산을 수행하는 데 유용합니다.
LATERAL을 사용한 쿼리는 각 행에 대해 서브쿼리를 독립적으로 실행하여, 외부 쿼리의 컬럼 값을 참조할 수 있게 만들어 줍니다.
이 방식은 서브쿼리가 매번 users 테이블의 각 행에 대해 계산되므로, 성능 면에서도 효율적이고 이해하기 쉬운 방식으로 동작합니다.

 

실행 시점(스코프)의 차이

항목 서브쿼리 LATERAL
참조 가능 범위 외부 테이블 참조 불가 왼쪽 테이블 컬럼 참조 가능
실행 횟수 한 번 (전체 기준) 왼쪽 행마다 한 번
실행 순서 독립적, 병렬 최적화 가능 왼쪽 → 오른쪽 순차 실행
성능 특성 캐시/최적화 쉬움 행마다 실행 시 비용↑
활용 목적 전역 통계, 상수화된 값 행별 연산, row-level 계산

 

+ LATERAL 구문 별 차이점

구문 조인 조건 (ON) 왼쪽 행이 오른쪽 결과와 매칭되지 않을 때 설명
CROSS JOIN LATERAL 없음
(ON TRUE와 동일)		 매칭 안 되면 행 자체가 제거됨 (INNER와 동일) 모든 왼쪽 행에 대해 오른쪽 쿼리 실행, 결과 없으면 해당 행 제외
JOIN LATERAL
(= INNER JOIN LATERAL)		 반드시 ON 필요 매칭 안 되면 행 제외 CROSS JOIN LATERAL과 거의 동일 (단, 명시적 조건 가능)
LEFT JOIN LATERAL 반드시 ON 필요 매칭 안 돼도 왼쪽 행 유지, 오른쪽 컬럼은 NULL OUTER JOIN 특성: 왼쪽 기준 유지

LATERAL과 메모이즈(MEMOIZE)의 관계

PostgreSQL에서는 LATERAL을 사용하여 서브쿼리나 함수를 각 행에 대해 독립적으로 실행할 수 있습니다. 그러나 LATERAL 자체는 결과를 캐시하지 않으므로, 동일한 계산을 반복할 수 있어 성능 저하를 초래할 수 있습니다. 이러한 문제를 해결하기 위해 MEMOIZE를 활용하여 결과를 캐시하고, 동일한 계산에 대한 반복 실행을 피할 수 있습니다.

 

※ PostgreSQL의 memoize 기능은 현재 스칼라 서브쿼리에는 적용되지 않습니다. 이는 memoize가 주로 파라미터화된 중첩 루프 조인에 사용되며, 서브쿼리의 실행 계획을 미리 생성하는 방식과의 호환성 문제로 인해 발생합니다.

PostgreSQL에서 LATERAL이 Nested Loop 조인을 선택하는 이유

PostgreSQL의 쿼리 플래너는 LATERAL을 사용한 서브쿼리를 각 행에 대해 독립적으로 실행합니다. 이러한 방식은 Nested Loop 조인과 유사한 동작을 하며, 각 행에 대해 서브쿼리를 실행하여 결과를 결합합니다. 따라서 LATERAL을 사용할 때는 Nested Loop 조인이 선택되는 경우가 많습니다.

LATERAL을 사용해야 하는 시나리오

  • 행별로 독립적인 계산이 필요한 경우: 각 행에 대해 독립적인 계산이 필요할 때 LATERAL을 사용하여 효율적인 쿼리 작성이 가능합니다.
  • 외부 쿼리의 컬럼을 참조해야 하는 서브쿼리나 함수가 필요한 경우: LATERAL을 사용하면 서브쿼리나 함수가 외부 쿼리의 컬럼을 참조하여 계산을 수행할 수 있습니다.
  • 테이블 함수나 JSON 처리 등 복잡한 계산이 필요한 경우: LATERAL을 사용하여 테이블 함수나 JSON 처리 등의 복잡한 계산을 효율적으로 수행할 수 있습니다.

LATERAL 사용 시 주의사항

  • LATERAL을 사용하면 서브쿼리나 함수가 각 행에 대해 독립적으로 실행되므로, 동일한 계산을 반복할 수 있어 성능 저하를 초래할 수 있습니다. 이러한 문제를 해결하기 위해 MEMOIZE를 활용하여 결과를 캐시하고, 동일한 계산에 대한 반복 실행을 피할 수 있습니다.
  • 인덱스되지 않은 대상 테이블은 성능에 상당한 영향을 미칠 수 있습니다.
  • 위와 같은 이유로 LATERAL은 인덱스된 테이블이나 소규모 데이터 셋에 효과적이며, 데이터 셋이 커질수록 성능 최적화에 대한 고려를 해야합니다.
  • LATERAL을 사용할 때는 조인 순서와 조건을 명확히 확인하여 의도한 대로 동작하는지 검증해야 합니다.

2. 예제

구분 시나리오 예시
1 사용자별 가장 최근 주문 조회 users → orders
2 로그 테이블에서 최근 3건 조회 servers → logs
3 문자열 배열 분해 후 JOIN content → regexp_split_to_table()
4 동적 필터링 (테이블 함수, JSON 처리 등) jsonb_array_elements_text() 등

 

예제 1: users + orders 테이블 - 유저별 최근 주문 조회용

• 테이블 생성

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  amount NUMERIC,
  order_time TIMESTAMP
);

 

• 데이터 삽입

-- 사용자 3명
INSERT INTO users (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

-- Alice의 주문 (3건)
INSERT INTO orders (user_id, amount, order_time) VALUES
  (1, 10000, '2024-01-01 10:00'),
  (1, 12000, '2024-02-01 11:00'),
  (1, 9000,  '2024-03-01 12:00');

-- Bob의 주문 (2건)
INSERT INTO orders (user_id, amount, order_time) VALUES
  (2, 5000,  '2024-01-15 09:00'),
  (2, 8000,  '2024-01-20 13:00');

-- Charlie는 주문 없음

 

• 쿼리 실행

SELECT u.id AS user_id, u.name, o.amount, o.order_time
FROM users u
LEFT JOIN LATERAL (
  SELECT o.amount, o.order_time
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.order_time DESC
  LIMIT 1
) o ON TRUE;

[그림 1] 예제 1 결과

예제 2: servers + logs 테이블 - 서버별 최근 로그 3건

• 테이블 생성

CREATE TABLE servers (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  server_id INT REFERENCES servers(id),
  message TEXT,
  log_time TIMESTAMP
);

 

• 데이터 삽입

-- 서버 2대
INSERT INTO servers (name) VALUES
  ('web01'),
  ('db01');

-- web01 로그 4건
INSERT INTO logs (server_id, message, log_time) VALUES
  (1, 'Started server', '2024-01-01 00:00'),
  (1, 'Handled request', '2024-01-01 00:01'),
  (1, 'Handled request', '2024-01-01 00:02'),
  (1, 'Stopped server',  '2024-01-01 00:03');

-- db01 로그 2건
INSERT INTO logs (server_id, message, log_time) VALUES
  (2, 'Started DB', '2024-01-01 00:05'),
  (2, 'Checkpoint', '2024-01-01 00:06');

 

• 쿼리 실행

SELECT s.name, l.message, l.log_time
FROM servers s
JOIN LATERAL (
  SELECT *
  FROM logs l
  WHERE l.server_id = s.id
  ORDER BY l.log_time DESC
  LIMIT 3
) l ON TRUE;

[그림 2] 예제 2 결과

 

예제 3: articles 테이블 - regexp_split_to_table() 테스트

• 테이블 생성

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  content TEXT
);

 

• 데이터 삽입

INSERT INTO articles (content) VALUES
  ('PostgreSQL is fast and reliable'),
  ('LATERAL joins are powerful'),
  ('SQL is declarative');

 

• 쿼리 실행

SELECT a.id, word
FROM articles a
JOIN LATERAL regexp_split_to_table(a.content, '\s+') AS word ON TRUE;

[그림 3] 예제 3 결과

예제 4: events 테이블 - JSON 배열 파싱

• 테이블 생성

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  tags JSONB
);

 

• 데이터 삽입

INSERT INTO events (tags) VALUES
  ('["error", "database", "critical"]'),
  ('["login", "auth"]'),
  ('[]');

 

• 쿼리 실행

SELECT e.id, tag
FROM events e
JOIN LATERAL jsonb_array_elements_text(e.tags) AS tag ON TRUE;

[그림 4] 예제 4 결과

+ 추가 필터 조건을 줄 수도 있습니다.

-- 특정 사용자의 주문 총액이 1만 원 초과인 경우에만 매칭
SELECT u.id, x.total_amount
FROM users u
JOIN LATERAL (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) x ON x.user_id = u.id AND x.total_amount > 10000;


-- 오른쪽 결과(last_order_date)가 특정 시점 이후인 경우만 매칭
-- 그 외에는 왼쪽 행은 유지되고, 오른쪽 컬럼은 NULL
SELECT u.id, x.last_order_date
FROM users u
LEFT JOIN LATERAL (
  SELECT o.user_id, max(o.created_at) AS last_order_date
  FROM orders o
  WHERE o.user_id = u.id
) x ON x.last_order_date > '2025-01-01';

오늘은 여기까지~

 

728x90
저작자표시 비영리 변경금지 (새창열림)

'SQL' 카테고리의 다른 글

SQL: 인덱스  (0) 2025.09.14
ANSI SQL: FOR UPDATE  (3) 2025.08.01
ANSI SQL: 윈도우 함수 LAG()  (0) 2025.05.08
ANSI SQL: CTE와 서브쿼리 비교  (1) 2025.01.02
PostgreSQL(ANSI SQL): LEFT JOIN ... IS NULL과 NOT EXISTS의 차이  (0) 2024.12.23

'SQL'의 다른글

  • 현재글ANSI SQL: LATERAL

관련글

티스토리툴바