MySQL LATERAL DERIVED TABLE과 Top N 데이터 찾기

최창효·2024년 9월 13일

Lateral Derived Table이란

FROM절에서 서브쿼리를 통해 생성되는 임시 테이블을 Derived Table이라고 합니다. 그리고 Lateral Derived Table은 조금 특별한 형태의 Derived Table을 말합니다.

LATERAL은 MySQL 8.0.14버전에 새롭게 추가된 키워드인데요. 일반적인 Derived Table은 동일한 FROM절에 있는 선행 테이블의 컬럼값을 참조할 수 없지만 LATERAL키워드를 선언한 Lateral Derived Table은 동일한 FROM절에 있는 선행 테이블의 컬럼값을 참조할 수 있습니다.

간단한 예시로 Lateral에 대해 살펴보겠습니다.

테이블 생성

CREATE TABLE balance(
		id int NOT NULL AUTO_INCREMENT,
        name varchar(50) NOT NULL,
		balance int NOT NULL,
		PRIMARY KEY (id)
);

Derived Table

SELECT
	t1.id,
    t1.balance,
    t2.multiple_balance
FROM
	balance t1,
    (SELECT t1.balance * 10 AS multiple_balance FROM DUAL) t2 
;

(SELECT t1.balance * 10 AS multiple_balance FROM DUAL) t2라는 Derived Table이 정의되어 있습니다.
Derived Table에서 선행 테이블인 t1의 컬럼값 balance에 접근하려고 합니다.

쿼리를 실행하면 아래와 같이 실패합니다.

Lateral Derived Table

이번에는 Derived Table에 LATERAL키워드를 사용해 실행해 보겠습니다.

SELECT
	t1.id,
    t1.balance,
    t2.multiple_balance
FROM
	balance t1,
    LATERAL (SELECT t1.balance * 10 AS multiple_balance FROM DUAL) t2 
;

Derived Table 앞에 LATERAL키워드를 사용했습니다.
마찬가지로 Derived Table에서 선행 테이블인 t1의 컬럼값 balance에 접근하려고 합니다.

이번에는 쿼리가 정상적으로 잘 실행됩니다.

LATERAL키워드로 인해 서브쿼리에서 선행 테이블(t1)의 컬럼값(balance)에 접근이 가능해졌습니다.

LATERAL JOIN

LATERAL은 Join과 함께 사용할 때도 유용합니다.

SELECT
FROM A JOIN (SELECT FROM t1 WHERE t1.id = A.id) as B
ON A.col = B.col

B라는 Derived Table을 생성하는 서브쿼리는 Join에서도 선행 테이블인 A테이블의 컬럼값을 참조할 수 없습니다.

이때 LATERAL키워드를 이용하면 선행 테이블의 컬럼값을 참조할 수 있습니다.

SELECT 
FROM A JOIN LATERAL (SELECT FROM t1 WHERE t1.id = A.id) as B
ON

Derived Table 앞에 LATERAL키워드를 붙여줍니다.

ㅤ
이번에도 구체적인 예시로 살펴보겠습니다.

테이블 생성

CREATE TABLE categories (
		id int NOT NULL AUTO_INCREMENT,
		name varchar(50) NOT NULL,
		PRIMARY KEY (id)
);

CREATE TABLE articles (
		id int NOT NULL AUTO_INCREMENT,
		category_id int NOT NULL,
		title varchar(255) NOT NULL,
		views int NOT NULL,
		PRIMARY KEY (id),
		KEY ix_categoryid_views (category_id, views)
);

category별로 article이 몇개 있는지 확인하려면 어떻게 해야 할까요?

일반 Join

SELECT categories.name, count(*) 
FROM categories
LEFT JOIN articles
ON categories.id = articles.category_id
GROUP BY categories.id;

Lateral Join

SELECT c.name, t.count
FROM categories c
LEFT JOIN LATERAL (
	SELECT count(*) AS count
    FROM articles a
    WHERE a.category_id = c.id
) as t
ON TRUE;

원래라면 선행 테이블인 c의 id를 서브쿼리에서 참조할 수 없기 때문에 WHERE a.category_id = c.id를 실행할 수 없습니다.
Lateral Join에서는 Join의 ON절에 있어야 할 조건이 서브쿼리의 WHERE절에서 사용되는 경우가 많습니다.
- LEFT JOIN은 문법상 ON이 필수이기 때문에 조건이 WHERE절로 옮겨갔다 하더라도 ON키워드를 생략할 수 없어 ON TRUE와 같은 방식으로 표현해줘야 합니다.
- INNER JOIN은 문법상 ON이 필수가 아니기 때문에 ON을 생략할 수 있습니다.

Top N 데이터 찾기

Lateral Derived Table을 이용하면 '카테고리 별로 views수가 높은 데이터를 3개씩 추출하라'와 같은 요구사항을 쉽게 만족시킬 수 있습니다.

SELECT c.name, a.title, a.views
FROM categories c
INNER JOIN LATERAL (
		SELECT category_id, title, views
		FROM articles
		WHERE category_id = c.id
		ORDER BY category_id DESC, views DESC
		LIMIT 3
) a