MySQL 8.0에서 추가된 모든 기능 – SQL DML

MySQL 8.0에서 추가된 모든 기능들과 변경된 기능들에 대해 정리한 시리즈 문서로, 이 문서는 SQL DML에 대한 내용을 담고있습니다.

1. 비재귀적 공통 테이블 표현식 (Non-recursive Common Table Expressions)

[원본제공링크 1]

비재귀적 CTE(Common Table Expression, 공통 테이블 식)는 WITH 절을 사용하여 쿼리 내에서 임시 결과 집합을 정의하고 이를 메인 쿼리에서 참조할 수 있게 합니다. 복잡한 쿼리를 더 명확하고 간결하게 작성할 수 있으며, 서브쿼리나 임시 테이블의 사용을 줄일 수 있습니다. 비재귀 CTE는 자기 자신을 참조하지 않는 CTE로, 재귀적인 처리가 필요 없는 경우에 사용합니다.

예시:

WITH top_customers AS (
    SELECT customer_id, SUM(order_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING total_spent > 1000
)
SELECT c.customer_id, c.name, tc.total_spent
FROM customers c
JOIN top_customers tc ON c.customer_id = tc.customer_id;

이 예시에서는 top_customers라는 CTE를 정의하여 총 구매 금액이 1,000 이상인 고객들을 선택합니다. 이후 메인 쿼리에서 이 CTE를 참조하여 고객의 상세 정보와 함께 총 구매 금액을 조회합니다.

2. 재귀적 공통 테이블 표현식 (Recursive Common Table Expressions)

[원본제공링크 1]

재귀적 CTE(Common Table Expression, 공통 테이블 식)는 WITH RECURSIVE 구문을 사용하여 CTE 내에서 자기 자신을 참조함으로써 계층적 데이터나 트리 구조의 데이터를 처리할 수 있게 합니다. 이는 재귀적인 자기 조인(self join)을 사용하여 계층형 쿼리를 작성하던 방식을 대체하며, 쿼리를 더욱 간결하고 이해하기 쉽게 만듭니다. 윈도우 함수는 순위나 누적 합계 등의 계산에 유용하지만, 계층 구조를 재귀적으로 탐색하는 작업에는 재귀 CTE가 더 적합합니다.

예시:

WITH RECURSIVE employee_hierarchy AS (
    -- 앵커 멤버: 최상위 관리자 선택
    SELECT employee_id, manager_id, full_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 멤버: 하위 직원들 선택
    SELECT e.employee_id, e.manager_id, e.full_name, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_id;

이 예시에서는 employees 테이블에서 재귀 CTE를 사용하여 조직도의 계층 구조를 조회합니다. employee_hierarchy CTE는 자기 자신을 참조하여 각 직원과 그들의 관리자를 계층적으로 연결하며, level 컬럼을 통해 계층의 깊이를 나타냅니다. 이렇게 하면 조직 내 모든 직원의 계층 정보를 한 번의 쿼리로 손쉽게 가져올 수 있습니다.

참고
원문에서 용도별로 '비재귀CTE', '재귀CTE'로 나누어 설명을 해놓아서 그 양식을 따랐으나, 
퉁쳐서 보면 MySQL 8.0에서 부터 CTE라는 기능이 추가 되었다고 한줄 요약 가능하겠습니다. 

- CTE는 일시적인 뷰(View)와 유사합니다. 상당의 SQL 예시에서 처럼, 특정 SELECT문의 결과를 임시적인 이름을 가진 결과 집합으로 정의하여, 복잡한 쿼리를 더 간결하고 이해하기 쉽게 작성할 수 있게 해주는 기능입니다. 하지만, 뷰와는 달리 데이터베이스에 저장되지는 않고 쿼리 실행 시에만 존재합니다.
- 다른 DBMS에서도 CTE를 지원하지만, MySQL에서는 8.0 버전부터 도입되었습니다.
- 복잡한 로직을 단계별로 쿼리할 수 있어 유지보수성이 향상됩니다.

참고2
비재귀CTE vs 재귀CTE
- 비재귀 CTE는 임시 view 처럼 사용한 CTE의 기본 형태라고 보시면 됩니다. 서브쿼리를 쿼리 본문 밖으로 빼내어서, 더 보기 편하고, 수정하기 좋고, 반복 사용에 용이해집니다. 
- 재귀CTE가 조금 특수 케이스인데, 기존의 재귀 조인(self join)이라고 하여 동일한 테이블을 2번 사용해서 서로 조인하는 경우를 대체하는 기능힙니다. 위의 예시에서는 '직원테이블'을 '직원테이블'과 조인했습니다. 왜 이런 쿼리가 필요하냐하면, 직원테이블내에 '나의사원번호'와 '상위권자의사원번호'가 들어있기 때문입니다. 즉, 직원테이블끼리 조인을하여 직원 관계를 계층형으로 표현할 수 있습니다. 
예를들면 이를 통해 아래와 같은 계층형의 결과를 만들어 낼 수 있습니다. 

김사원 - 박대리
민사원 - 박대리 
강사원 - 이대리
최사원 - 이대리 
         박대리 - 서팀장
         이대리 - 서팀장
                  서팀장 - 송본부장
                           송본부장 - 신사장 

이런식으로 인접한 관계를 하나의 테이블내에 담아놓은 구조를 '인접 목록 테이블(Adjacency List Table)'이라 하고, 인접 목록 테이블을 다시 같은 테이블에 재귀적(self)으로 조인하여 계층형 구조를 표시했었습니다. 
다만 이 재귀 조인 쿼리는 한눈에 알아보기 너무 복잡하기 때문에 WITH RECURSIVE 기능이 추가되기를 기대하는 사람이 많았었고, MySQL 8.0에서 이 기능이 도입된 것입니다. 
다만 MySQL 8.0에서는 후술할 Window 함수 기능도 같이 추가되었기 때문에, WITH RECURSIVE 기능을 사용하지 않고, Window 함수를 사용하여도 됩니다. 

정리

• 비재귀 CTE는 쿼리를 단순화하고 임시 결과를 재사용할 때 유용합니다.
• 재귀 CTE는 부모-자식 관계와 같이 계층적이며 반복적인 데이터 구조를 처리할 때 적합합니다.
• 비재귀 CTE는 복잡한 쿼리의 단순화와 가독성 향상을 위해 사용하며, 재귀 처리가 필요 없는 상황에서 활용됩니다.
• 재귀 CTE는 계층 구조 데이터의 탐색과 처리를 위해 사용되며, 재귀적인 연산이 필요한 경우에 적합합니다.

3. 윈도우 함수 (Window Functions)

[원본제공링크 1]

윈도우 함수(Window Function)는 각 행에 대해 집계 함수나 순위 함수를 적용할 수 있게 해주는 기능입니다. OVER 절과 함께 사용하여 파티션(partition)과 정렬(order by)을 지정함으로써 누적 합계, 이동 평균, 순위 계산 등 다양한 분석 작업을 수행할 수 있습니다. 그룹화 없이도 각 행에 대한 추가 계산이 가능하여 데이터 분석과 리포팅에 매우 유용합니다.

예시:

SELECT
    order_id,
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS cumulative_total,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
FROM orders
WHERE customer_id IN (123, 456)
ORDER BY customer_id, order_date;

이 예시에서는 customer_id별로 주문 금액의 누적 합계와 각 주문에 대한 순위를 계산합니다. SUM 윈도우 함수를 사용하여 누적 금액을, RANK 함수를 사용하여 주문 금액에 따른 순위를 계산할 수 있습니다.

4. ORDER BY 및 DISTINCT와 ROLLUP

[원본제공링크 1]

MySQL 8.0에서는 GROUP BY 절과 함께 사용하는 ROLLUP 연산자와 ORDER BY, DISTINCT를 함께 사용할 수 있게 되었습니다. ROLLUP 기능 자체는 5.7에서도 지원 하던 기능었으나, 8.0.12 이전에는 ROLLUP과 DISTINCT, ORDER BY를 같이 사용할 수 없었습니다. 즉, ROLLUP 기능 자체가 이번에 추가된 것이 아니라, 기능이 확장 되었다고 보시면 되겠습니다.

예시:

SELECT department, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY department, job_title WITH ROLLUP
ORDER BY department, job_title;

이 예시에서는 부서와 직책별로 급여 합계를 계산하고, WITH ROLLUP을 사용하여 각 부서 및 전체의 총합계를 추가로 제공합니다. ORDER BY를 통해 결과를 부서와 직책 순으로 정렬합니다.

5. LATERAL 파생 테이블

[원본제공링크 1]

LATERAL 파생 테이블은 FROM 절에서 사용하는 서브쿼리가 앞서 정의된 테이블의 컬럼을 참조할 수 있도록 해줍니다. MySQL 8.0.14부터 지원되며, LATERAL 키워드를 사용하여 정의합니다. 이를 통해 서브쿼리에서 이전 테이블의 데이터를 활용할 수 있어 복잡한 쿼리를 간결하게 작성할 수 있습니다.

예시:

SELECT e.employee_id, e.name, p.project_count
FROM employees e
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS project_count
    FROM projects p
    WHERE p.employee_id = e.employee_id
) p ON TRUE;

이 예시에서는 각 직원이 참여한 프로젝트 수를 계산하기 위해 LATERAL 파생 테이블을 사용합니다. 서브쿼리에서 외부 테이블 e의 employee_id를 참조합니다.

6. 파생 테이블에서 외부 테이블 참조

[원본제공링크 1]

파생 테이블은 일반적으로 동일한 FROM 절 내의 다른 테이블 열을 참조할 수 없습니다. 그러나 MySQL 8.0.14부터, 파생 테이블(derived table)이 외부 테이블(outer query)의 열을 참조할 수 있도록 허용하는 기능입니다. 이 기능은 이전까지 MySQL에서 제한되었지만, SQL 표준에서는 허용되는 개념입니다. 이 기능의 도입은 MySQL이 SQL 표준을 더 충실히 따르게 만든 중요한 변화로 볼 수 있습니다.

예시:

SELECT a, 
       (SELECT b FROM tab_b WHERE tab_b.a = tab_a.a LIMIT 1) AS derived_b
FROM tab_a;

참고 - LATERAL 파생 테이블 vs (LATERAL없는)파생 테이블에서 외부 테이블 참조
5,6번이 결국 같은 소리를 하고 있는 것 같아서 왜 두개로 나눠놓은 것인지 조금 헷갈리긴 합니다만.. 
각각에 링크되어있는 레퍼런스 매뉴얼 페이지를 참고해봤을 때에, 

5번은 JOIN LATERAL 구문을 이용하고 / 파생 테이블이 온전히 테이블 형태를 띄고 있는데 / 파생 테이블 바깥에 있는 테이블을 WHERE 조건등에서 참조하여 사용하는 경우를 보여주는 것 같고,

6번은 LATERAL 구문이 없어 사용된 경우인데 / 대신 파생테이블은 스칼라 서브쿼리의 형태를 띕니다 / 이때 이 스칼라 서브쿼리가, 서브쿼리 바깥에 있는 다른 테이블을 참조할 수 있음을 보여주는 것 같습니다. 

둘 다 MySQL 8.0.14에서 추가된 동작 형태이며, 기존의 MySQL은 파생테이블 내에선, 바깥의 테이블을 아예 참조할 수가 없었습니다. 
이를 가능하게 해준 추가 기능을 두가지 형태로 나누어 설명한 것입니다.

7. VALUES

[원본제공링크 1]

MySQL 8.0.19부터 도입된 VALUES 문은 하나 이상의 행을 테이블 형태로 반환하는 데이터 조작 언어(DML) 문입니다. 이는 테이블 값 생성자로서 독립적인 SQL 문으로 기능하며, 각 행은 ROW() 생성자를 통해 정의됩니다. 선택적으로 ORDER BY 및 LIMIT 절을 사용하여 결과를 정렬하거나 출력할 행 수를 제한할 수 있습니다.

예시:

SELECT * FROM (VALUES ROW(1, 'Alice'), ROW(2, 'Bob'), ROW(3, 'Charlie')) AS t(id, name);

이 예시에서는 VALUES 문을 사용하여 임시 테이블 t를 생성하고, id와 name 컬럼을 정의합니다. 이렇게 생성된 데이터는 쿼리에서 조인이나 필터링에 활용할 수 있습니다.

실제로 테이블을 생성하거나 값을 insert하지 않고도 마치 아래와 같은 테이블이 있는 것 처럼 작업하는 것입니다.

8. INSERT … ON DUPLICATE KEY UPDATE에서 기존/신규 행 참조

[원본제공링크 1]

MySQL의 INSERT ... ON DUPLICATE KEY UPDATE 구문은 중복 키 충돌 시 기존 행을 업데이트합니다. 이때, 업데이트 절에서 기존 행의 값을 직접 참조할 수 있으며, 새로운 행의 값은 VALUES() 함수를 통해 참조할 수 있습니다. 그러나 VALUES() 함수는 더 이상 권장되지 않으며, 대신 행 및 열 별칭을 사용하여 새로운 행의 값을 참조하는 것이 좋습니다. (8.0.20에서 부터 deprecated 됨)

예시:

INSERT INTO inventory (product_id, quantity)
VALUES (1, 10)
AS new
ON DUPLICATE KEY UPDATE
quantity = inventory.quantity + new.quantity;

이 예시에서는 inventory 테이블에 product_id가 1인 제품의 수량을 10만큼 추가하려고 합니다. 만약 product_id가 1인 행이 이미 존재한다면, 기존 quantity에 새로운 quantity 값을 더하여 업데이트합니다. 여기서 new는 삽입하려는 새로운 행의 별칭이며, new.quantity를 통해 새로운 수량 값을 참조합니다

9. 재귀적 CTE에서의 LIMIT

[원본제공링크 1]

MySQL 8.0.19부터 재귀적 공통 테이블 표현식(CTE)에서 LIMIT 절을 사용하여 반환할 최대 행 수를 지정할 수 있습니다. 이를 통해 재귀 쿼리의 결과를 원하는 개수만큼 제한할 수 있으며, 무한 재귀로 인한 성능 저하를 방지하는 데 유용합니다.

예시:

WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 100
)
SELECT * FROM cte LIMIT 10;

이 예시에서는 1부터 시작하여 100까지 숫자를 생성하는 재귀 CTE를 정의하고, LIMIT 10을 사용하여 처음 10개의 숫자만 선택합니다. 이를 통해 필요한 결과만 효율적으로 가져올 수 있습니다.