LATERAL JOIN 개요

LATERAL JOIN이란?

• SQL 하위 쿼리가 메인 쿼리의 각 행에 접근할 수 있도록 해주는 조인 방식
• 표준 SQL 및 PostgreSQL, MySQL 8.0 이상, Oracle 등에서 지원되는 기능
• LATERAL 키워드를 사용하면 서브 쿼리 안에서 외부 쿼리의 칼럼을 자유롭게 참조 가능
• 전통적인 JOIN에서는 서브쿼리 내에서 외부 쿼리의 컬럼을 직접 참조할 수 없지만, LATERAL JOIN은 이를 가능케 하여 더욱 유연한 데이터 변환 및 분석이 가능해짐

 

LATERAL JOIN PostgreSQL 예시 #1

• 각 부서별로 직원들의 정보와, 그 직원이 속한 부서에서 최근 등록된 공지사항 2개를 최신순으로 함께 보여주고 싶다고 가정
• 이런 요구사항은 전통적인 JOIN이나 서브쿼리만으로는 복잡하거나 효율이 떨어질 수 있지만, LATERAL JOIN을 사용하면 훨씬 간결하고 직관적으로 해결

 

DDL + DML

 

	-- 부서 테이블
	CREATE TABLE departments (
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL
	);
	-- 직원 테이블
	CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	department_id INTEGER NOT NULL,
	CONSTRAINT fk_department
	FOREIGN KEY (department_id) REFERENCES departments(id)
	);
	-- 공지사항 테이블
	CREATE TABLE announcements (
	id SERIAL PRIMARY KEY,
	department_id INTEGER NOT NULL,
	content TEXT NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT fk_announcement_department
	FOREIGN KEY (department_id) REFERENCES departments(id)
	);
	-- 1. 부서 데이터 삽입
	INSERT INTO departments (name) VALUES
	('영업부'),
	('개발부'),
	('인사부');
	-- 2. 직원 데이터 삽입
	INSERT INTO employees (name, department_id) VALUES
	('김철수', 1), -- 영업부
	('이영희', 1), -- 영업부
	('박민수', 2), -- 개발부
	('최지은', 2), -- 개발부
	('정다은', 3); -- 인사부
	-- 3. 공지사항 데이터 삽입
	INSERT INTO announcements (department_id, content, created_at) VALUES
	(1, '영업부 워크숍 안내', '2023-10-01 09:00:00'),
	(1, '영업부 회의 일정 변경', '2023-11-01 10:00:00'),
	(2, '개발부 신입 사원 모집', '2023-10-15 15:30:00'),
	(2, '개발부 프로젝트 마감 안내', '2023-11-05 14:00:00'),
	(2, '개발부 회의 일정 안내', '2023-11-10 11:00:00'),
	(3, '인사부 연말 정산 안내', '2023-11-01 08:00:00');

view raw .sql hosted with ❤ by GitHub

 

LATERAL JOIN 쿼리

 

	SELECT
	e.name AS employee_name,
	d.name AS department_name,
	a.content AS latest_announcement
	FROM
	employees e
	JOIN
	departments d
	ON
	e.department_id = d.id
	LEFT JOIN LATERAL (
	SELECT
	content
	FROM
	announcements a
	WHERE
	a.department_id = e.department_id
	ORDER BY
	created_at DESC
	LIMIT 1
	) a ON true;

view raw .sql hosted with ❤ by GitHub

 

부연 설명

• 해당 쿼리는 employees 테이블에서 각 직원(e)을 조회하면서, 그 직원의 부서 정보(d)를 JOIN
• LATERAL 키워드가 붙은 서브쿼리는 employees 테이블의 각 행(한 명의 직원)을 처리할 때마다, 그 직원이 속한 부서의 최근 공지사항 2개를 announcements 테이블에서 조회
• 서브쿼리 안에서 e.department_id를 자유롭게 사용하여, 조건에 맞는 공지사항만 가져옴
• LEFT JOIN LATERAL을 사용했으므로, 해당 부서의 공지사항이 2개 미만이거나 없더라도 직원 정보는 항상 표시
• 각 직원에 대해 서브쿼리(공지사항 조회)가 반복 실행되므로, 복잡한 연산이나 동적 매핑이 필요한 상황에서 매우 유용

 

LATERAL JOIN의 장점이 드러나는 이유

• 행별 동적 데이터 추출: 직원마다 속한 부서가 다를 수 있는데, 각 직원에 맞는 맞춤형 서브쿼리 결과를 가져올 수 있음
• N개 제한 쉽게 처리: 부서별 최신 N개 데이터(이 예시에서는 2개)를 쉽게 추출할 수 있음
  • 일반 JOIN만으로는 복잡한 ROW_NUMBER() 윈도우 함수나 중첩 서브쿼리가 필요

 

• 가독성 및 유지보수성: 쿼리가 간결하고, 논리적으로도 읽기 쉽기 때문에 향후 유지보수에 유리
• 유연한 활용: JSON, 배열, 동적 파싱 등 다양한 상황에도 손쉽게 응용할 수 있음

 

LATERAL JOIN PostgreSQL 예시 #2

• LATERAL JOIN은 JSON 데이터를 행으로 펼치거나 JSON 내부 값을 동적으로 조회할 때 매우 유용함

 

DDL + DML

	CREATE TABLE users (
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	contacts JSONB NOT NULL -- 연락처 정보를 JSON 배열로 저장
	);
	INSERT INTO users (name, contacts) VALUES
	('김철수', '[{"type": "email", "value": "chulsoo@email.com"}, {"type": "phone", "value": "010-1234-5678"}]'),
	('이영희', '[{"type": "email", "value": "younghee@email.com"}]'),
	('박민수', '[{"type": "phone", "value": "010-8765-4321"}, {"type": "kakao", "value": "minsu_kakao"}]');

view raw .sql hosted with ❤ by GitHub

 

LATERAL JOIN 쿼리

	SELECT
	u.name AS user_name,
	contact.value->>'type' AS contact_type,
	contact.value->>'value' AS contact_value
	FROM
	users u,
	LATERAL jsonb_array_elements(u.contacts) AS contact
	ORDER BY
	u.name, contact_type;

view raw .sql hosted with ❤ by GitHub

 

부연 설명

• LATERAL JOIN을 이용해 jsonb_array_elements 함수를 호출하면, contacts 배열의 각 요소가 contact.value로 행으로 분리되어 반환되며 이렇게 하면 한 사용자의 여러 연락처 정보가 각각의 행으로 펼쳐짐
• contact.value->>'type' 문법은 JSON 오브젝트에서 type 값을 문자열로 추출하는 방식
• 결과적으로, 각 사용자의 연락처를 종류별로 한눈에 확인할 수 있음