Lateral Derived Table

2. 정의 및 동작 방식

 

Lateral Derived Table이란?

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

 

• Derived Table(파생 테이블)은 쿼리의 FROM 절에서 서브쿼리를 통해 생성되는 임시 테이블을 의미합니다.
• 일반적으로 Derived Table은 선행테이블의 컬럼을 참조할 수 없으나, Lateral Derived Table은 참조가 가능합니다.
• 정의된 Dervied Table 앞부분에 LATERAL 키워드를 추가해서 사용할 수 있습니다.
• 참조한 값을 바탕으로 동적으로 결과 생성합니다.

 

동작 방식

SELECT e.emp_no, s.sales_count, s.total_sales
FROM employees e
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS sales_count,
           IFNULL(SUM(total_price),0) AS total_sales
    FROM sales
    WHERE emp_no=e.emp_no
) s ON TRUE;

 

예시 쿼리처럼 JOIN문 뒤에 LATERAL을 명시하면 됩니다.

선행 테이블의 실행에 의존적이므로 실행계획도 dependent derived로 나옵니다.

 

정의와 동작 방식만 봐서는 이게 어디 쓰는건지 와닿지 않을 것이라 생각합니다. 따라서 실제 사용 예시를 보며 어떤 쓰임새가 있는지 자세히 살펴보도록 하겠습니다.

 

3. 사용 예제

예제 1: 종속 서브 쿼리의 다중 값 반환

SELECT d.dept_name,
       (SELECT e.hire_date AS earliest_hire_date,
               CONCAT(e.first_name,' ',e.last_name) AS full_name
        FROM dept_emp de
        INNER JOIN employees e ON e.emp_no=de.emp_no
        WHERE de.dept_no=d.dept_no
        ORDER BY e.hire_date LIMIT 1)
FROM departments d

 

위 쿼리의 경우 에러를 발생시킵니다. SELECT 절에서의 서브 쿼리를 사용하는 경우 하나의 컬럼 값만 반환이 가능하기 때문입니다.

다음과 같은 방법으로 하면 에러를 발생시키지 않을 수는 있습니다.

SELECT
    d.dept_name,
    (SELECT e.hire_date
     FROM dept_emp de
     INNER JOIN employees e ON e.emp_no=de.emp_no
     WHERE de.dept_no=d.dept_no
     ORDER BY e.hire_date LIMIT 1) AS earliest_hire_date,
    (SELECT CONCAT(e.first_name,' ',e.last_name)
     FROM dept_emp de
     INNER JOIN employees e ON e.emp_no=de.emp_no
     WHERE de.dept_no=d.dept_no
     ORDER BY e.hire_date LIMIT 1) AS full_name
FROM departments d

 

음.. 누가 봐도 비효율적인 쿼리임을 알 수 있습니다. 동일한 데이터를 가져오는 서브 쿼리가 중복으로 실행되겠군요.

이럴 때 LATERAL 키워드를 활용하면 하나의 서브쿼리로 원하는 값들을 모두 조회해올 수 있습니다!

 

SELECT d.dept_name,
       x.earliest_hire_date,
       x.full_name
FROM departments d
INNER JOIN LATERAL (
    SELECT e.hire_date AS earliest_hire_date,
           CONCAT(e.first_name,' ',e.last_name) AS full_name
    FROM dept_emp de
    INNER JOIN employees e ON e.emp_no=de.emp_no
    WHERE de.dept_no=d.dept_no
    ORDER BY e.hire_date LIMIT 1) x

 

예제 2: SELECT 절 내 연산 결과 반복 참조

SELECT (total_sales * margin_rate) AS profit,
       ((total_sales * margin_rate) / total_sales_number) AS avg_profit,
       (expected_sales * margin_rate) AS expected_profit,
       ((total_sales * margin_rate) / (expected_sales * margin_rate) * 100)
           AS sales_achievement_rate
FROM daily_revenue
WHERE sales_date='2023-12-01';

동일한 연산을 중복해서 사용하는 것이 비효율적입니다. 쿼리 가독성도 별로고 실수 유발 가능성도 높습니다.

먼저 계산하고 별명을 참조하려면? LATERAL을 사용하면 됩니다.

 

SELECT profit,
       avg_profit,
       expected_profit,
       sales_achievement_rate
FROM daily_revenue,
    LATERAL (SELECT (total_sales * margin_rate) AS profit) p,
    LATERAL (SELECT (profit / total_sales_number) AS avg_profit) ap,
    LATERAL (SELECT (expected_sales * margin_rate) AS expected_profit) ep,
    LATERAL (SELECT (profit / expected_profit * 100) AS sales_achievement_rate) sar
WHERE sales_date='2023-12-01';

순서상 먼저 계산된 것에 대해 추후 쿼리에서 참조할 수 있습니다. 선행 테이블에 대한 column을 참조할 수 있게 되기 때문에 가능해진 일입니다.

 

그 외에 이전 결과에 기초하여 데이터를 분석하거나 카테고리별 TOP N 데이터를 조회해야할 때 더 최적화되고 깔끔한 쿼리로 작성할 수 있습니다 :)