개요

쿼리를 작성하다 보면, 때때로 JOIN 문을 여러 개 사용해야 하는 상황이 발생합니다. 이런 경우 가독성이 저하될 수 있습니다. CTE(Common Table Expression)를 활용하면 쿼리의 가독성을 높일 수 있습니다.

CTE란 무엇인가

CTE(Common Table Expression)는 SQL에서 일시적인 결과 집합을 정의하는 데 사용되는 기능입니다. CTE는 복잡한 쿼리를 더 쉽게 읽고 관리할 수 있는 여러 논리적 단계로 분할할 수 있습니다. CTE는 WITH 구문을 사용해 정의되며, 쿼리 내에서 일시적으로 생성되어 해당 쿼리 실행 동안에만 존재합니다.

CTE는 특히 재귀 쿼리를 작성하거나 데이터의 계층적 구조와 복잡한 조인 연산을 단순화할 때 유용합니다.

활용 예

CTE는 쿼리문을 논리적으로 쉽게 이해할 수 있도록 도와줍니다.

기본 사용

1WITH CTE_Name AS (
2    SELECT 컬럼명1, 컬럼명2
3    FROM 테이블명
4    WHERE 조건
5)
6SELECT *
7FROM CTE_Name;

위와 같이 간단하게 사용할 수 있으며, WITH 구문은 쿼리 수행 동안 참조할 수 있습니다.

재귀문에 사용

 1WITH RECURSIVE SubCategories AS (
 2    SELECT
 3        CategoryId,
 4        ParentCategoryId,
 5        Name,
 6        1 AS Level -- 시작 레벨을 1로 설정
 7    FROM
 8        Categories
 9    WHERE
10        CategoryId = 1 -- 시작 카테고리 ID
11
12    UNION ALL
13
14    SELECT
15        c.CategoryId,
16        c.ParentCategoryId,
17        c.Name,
18        sc.Level + 1 -- 하위 레벨 증가
19    FROM
20        Categories c
21            JOIN SubCategories sc ON c.ParentCategoryId = sc.CategoryId
22)
23SELECT *
24FROM SubCategories
25ORDER BY Level, CategoryId;

위 예제에서는 CategoryId가 1인 카테고리를 시작점으로 하여, 해당 카테고리 및 모든 하위 카테고리를 조회합니다. RECURSIVE 키워드로 시작하는 CTE는 두 부분으로 구성됩니다. 기본 케이스와 재귀적 케이스입니다.

  • 기본 케이스: 최초의 SELECT 문은 재귀의 시작점을 정의합니다. 여기서는 CategoryId가 1인 카테고리를 선택합니다.
  • 재귀적 케이스: UNION ALL 다음의 SELECT 문은 재귀적으로 하위 카테고리를 조회합니다. 이 쿼리는 자기 자신을 참조하는 SubCategories CTE를 사용하여, 각 단계에서 부모 카테고리의 하위 카테고리를 찾습니다.

Level 열은 각 카테고리가 최초 카테고리로부터 얼마나 깊이 있는지를 나타내는 계층 레벨을 보여줍니다. 이를 통해 각 카테고리의 계층 구조를 쉽게 이해할 수 있습니다.

실제수행

  1. DDL, 테스트 데이터 삽입

     1CREATE TABLE Categories (
 2    CategoryId INT AUTO_INCREMENT PRIMARY KEY,
 3    ParentCategoryId INT NULL,
 4    Name VARCHAR(255),
 5    FOREIGN KEY (ParentCategoryId) REFERENCES Categories(CategoryId)
 6);
 7
 8INSERT INTO Categories (CategoryId, ParentCategoryId, Name) VALUES
 9(1, NULL, 'Electronics'),
10(2, 1, 'Computers'),
11(3, 2, 'Laptops'),
12(4, 2, 'Desktops'),
13(5, 1, 'Cameras'),
14(6, 5, 'DSLR'),
15(7, 5, 'Point & Shoot');

  2. 쿼리수행

     1+------------+------------------+---------------+-------+
 2| CategoryId | ParentCategoryId | Name          | Level |
 3+------------+------------------+---------------+-------+
 4|          1 |             NULL | Electronics   |     1 |
 5|          2 |                1 | Computers     |     2 |
 6|          5 |                1 | Cameras       |     2 |
 7|          3 |                2 | Laptops       |     3 |
 8|          4 |                2 | Desktops      |     3 |
 9|          6 |                5 | DSLR          |     3 |
10|          7 |                5 | Point & Shoot |     3 |
11+------------+------------------+---------------+-------+

복잡한 JOIN문

  • Products 테이블: 제품 정보를 저장합니다.
  • Sales 테이블: 각 판매 거래의 세부 사항을 저장합니다.
  • Employees 테이블: 직원 정보를 저장합니다.
 1-- ProductSales CTE
 2WITH ProductSales AS (
 3    SELECT
 4        p.ProductId,
 5        p.ProductName,
 6        SUM(s.Quantity) AS TotalQuantity
 7    FROM
 8        Products p
 9            JOIN
10        Sales s ON p.ProductId = s.ProductId
11    GROUP BY
12        p.ProductId, p.ProductName
13),
14
15-- EmployeeSales CTE
16     EmployeeSales AS (
17         SELECT
18             e.EmployeeId,
19             e.EmployeeName,
20             s.ProductId,
21             SUM(s.Quantity) AS QuantitySold
22         FROM
23             Sales s
24                 JOIN
25             Employees e ON s.EmployeeId = e.EmployeeId
26         GROUP BY
27             e.EmployeeId, e.EmployeeName, s.ProductId
28     )
29
30-- 메인 쿼리
31SELECT
32    ps.ProductName,
33    ps.TotalQuantity,
34    es.EmployeeName,
35    es.QuantitySold
36FROM
37    ProductSales ps
38        JOIN
39    EmployeeSales es ON ps.ProductId = es.ProductId
40ORDER BY
41    ps.ProductName, es.EmployeeName;
  • ProductSales CTE: Products와 Sales 테이블을 JOIN하여 각 제품별 총 판매량(TotalQuantity)을 계산합니다.
  • EmployeeSales CTE: Sales와 Employees 테이블을 JOIN하여 각 직원별로 판매한 제품과 그 수량(QuantitySold)을 계산합니다.
  • 최종 SELECT 문에서 ProductSales와 EmployeeSales CTE를 JOIN하여, 각 제품별로 총 판매량과 해당 제품을 판매한 직원의 이름 및 판매량을 포함하는 결과를 생성합니다.

실제수행

  1. DDL, 테스트 데이터 삽입

     1CREATE TABLE Products (
 2    ProductId INT PRIMARY KEY,
 3    ProductName VARCHAR(255)
 4);
 5
 6INSERT INTO Products (ProductId, ProductName) VALUES
 7(1, 'Laptop'),
 8(2, 'Smartphone'),
 9(3, 'Tablet');
10
11CREATE TABLE Employees (
12    EmployeeId INT PRIMARY KEY,
13    EmployeeName VARCHAR(255)
14);
15
16INSERT INTO Employees (EmployeeId, EmployeeName) VALUES
17(1, 'John Doe'),
18(2, 'Jane Smith'),
19(3, 'Emily Jones');
20
21CREATE TABLE Sales (
22    SaleId INT PRIMARY KEY,
23    ProductId INT,
24    EmployeeId INT,
25    Quantity INT,
26    FOREIGN KEY (ProductId) REFERENCES Products(ProductId),
27    FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId)
28);
29
30INSERT INTO Sales (SaleId, ProductId, EmployeeId, Quantity) VALUES
31(1, 1, 1, 10),
32(2, 1, 2, 5),
33(3, 2, 1, 8),
34(4, 2, 3, 6),
35(5, 3, 2, 3),
36(6, 3, 3, 2),
37(7, 1, 3, 7),
38(8, 2, 2, 4);

  2. 쿼리수행

     1+-------------+---------------+--------------+--------------+
 2| ProductName | TotalQuantity | EmployeeName | QuantitySold |
 3+-------------+---------------+--------------+--------------+
 4| Laptop      |            22 | Emily Jones  |            7 |
 5| Laptop      |            22 | Jane Smith   |            5 |
 6| Laptop      |            22 | John Doe     |           10 |
 7| Smartphone  |            18 | Emily Jones  |            6 |
 8| Smartphone  |            18 | Jane Smith   |            4 |
 9| Smartphone  |            18 | John Doe     |            8 |
10| Tablet      |             5 | Emily Jones  |            2 |
11| Tablet      |             5 | Jane Smith   |            3 |
12+-------------+---------------+--------------+--------------+

CTE 지원여부

DBMSWITH 지원WITH RECURSIVE 지원비고
PostgreSQL예예
MySQL예일부지원버전 8.0 이상에서 WITH RECURSIVE 지원
SQLite예예
SQL Server예예
Oracle예예
IBM DB2예예
MariaDB예일부지원버전 10.2.2 이상에서 WITH RECURSIVE 지원

정리

이 포스팅에서는 CTE의 개념과 활용 예제를 통해 CTE가 SQL 쿼리의 가독성과 구조를 개선하는 방법을 살펴보았습니다. CTE는 복잡한 쿼리 작성 시 강력한 도구이지만, 사용 시 데이터 셋의 크기나 재귀 쿼리의 무한 루프 가능성 등 성능과 관련된 사항을 유의해야 합니다.