SQL - 공통 테이블 표현식 (CTE)

안녕하세요, 미래의 SQL 마법사 여러분! 오늘 우리는 공통 테이블 표현식, 즉 CTE에 대한 흥미로운 여정을 시작할 것입니다. 프로그래밍에 새로운 사람이라면 걱정하지 마세요 - 저는 수년 동안 수많은 학생들을 가르친 경험을 바탕으로 단계별로 안내해 드릴 것입니다. 당신의 좋아하는 음료를 가져와 편안하게 앉아 있자고요, 이제 시작해 볼까요!

SQL 공통 테이블 표현식

자, 큰 파티를 준비하는 것을 상상해 보세요 (어떤 사람이 좋은 비유를 좋아하지 않을까요?). 당신은 할 일 목록이 있고, 그것을 더 작은, 관리 가능한 조각으로 나누고 싶습니다. 이것이 SQL에서 CTE가 하는 일입니다 - 복잡한 쿼리를 더 간단하고 읽기 쉽게 나누는 것입니다.

CTE는 SELECT, INSERT, UPDATE, DELETE, 또는 MERGE 문 내에서 참조할 수 있는 임시 명명된 결과 집합입니다. 이는 단일 문장의 실행 범위 내에서 정의됩니다. 이를 임시 테이블을 만드는 것과 같은 것으로 생각할 수 있습니다.

간단한 예를 보겠습니다:

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

이 예제에서:

1. 우리는 WITH 키워드로 CTE의 시작을 알립니다.
2. 우리는 CTE의 이름을 지정합니다: cte_example.
3. 우리는 CTE가 포함할 내용을 정의합니다: 이 경우, 'Hello, CTE!' 값을 가진 'greeting' 열을 만드는 간단한 SELECT 문입니다.
4. CTE 정의 후, 우리는 CTE를 사용하는 주요 쿼리를 작성합니다.

이를 실행하면 다음과 같이 보입니다:

| greeting    |
|-------------|
| Hello, CTE! |

이게 멋지지 않나요? 우리는刚刚 첫 번째 CTE를 만들었습니다!

MySQL WITH 절

이제 MySQL에서의 WITH 절에 대해 이야기해 보겠습니다. 이 절은 우리의 CTE를 살아나게 하는 마법의 지팡이입니다. 일반 문법은 다음과 같습니다:

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

더 실践적인 예를 보겠습니다. 가정하건데, 우리는 직원 테이블이 있습니다:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

이제 CTE를 사용하여 부서별 평균 급여를 찾아보겠습니다:

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

이 쿼리는 다음과 같이 결과를 줍니다:

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

여기서 우리는 CTE를 사용하여 각 부서의 평균 급여를 계산하고, 그 결과를 사용하여 결과를 표시합니다. 마치 임시 테이블을 만들어서 주요 쿼리에서 사용하는 것과 같습니다.

여러 테이블에서의 CTE

CTE는 단일 테이블에 한정되지 않습니다. 우리는 일반 쿼리와 마찬가지로 여러 테이블을 CTE 정의에 사용할 수 있습니다. 예를 추가로 보겠습니다:

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

이제 두 테이블의 정보를 결합하는 CTE를 사용해 보겠습니다:

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

이는 다음과 같이 결과를 줍니다:

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

이 예제에서 우리는 CTE를 사용하여 직원과 부서 테이블을 결합하고, 평균 급여와 위치 정보를 포함합니다.

재귀 CTE

이제 재귀 CTE로 넘어가 보겠습니다! 이 것은 SQL 세계의 러시아 nesting dolls와 같습니다. 재귀 CTE는 자신을 참조하여 히에라르키적이거나 트리 구조 데이터를 처리할 수 있습니다.

간단한 직원 계층 구조 예제를 만들어 보겠습니다:

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 3', 3);

이제 전체 계층 구조를 표시하는 재귀 CTE를 사용해 보겠습니다:

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

이 쿼리는 다음과 같이 결과를 줍니다:

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

이 재귀 CTE는 최상위 직원(Big Boss)으로 시작하여 각 매니저와 그들의 직원을 반복적으로 찾습니다.

CTE의 장점

CTE는 다음과 같은 여러 가지 장점을 가지고 있습니다:

장점	설명
가독성	CTE는 복잡한 쿼리를 명명된 서브쿼리로 나누어 더 읽기 쉽게 만듭니다.
재사용성	CTE는 쿼리 내에서 여러 번 참조할 수 있습니다.
재귀	CTE는 히에라르키적 데이터에 대한 재귀 쿼리를 작성할 수 있습니다.
단순화	CTE는 복잡한 조인과 서브쿼리를 단순화할 수 있습니다.
유지보수성	CTE는 쿼리를 더 쉽게 유지하고 수정할 수 있게 합니다.

CTE의 단점

CTE는 강력하지만, 몇 가지 제한이 있습니다:

단점	설명
성능	어떤 경우에는 CTE가 파생 테이블이나 뷰보다 성능이 떨어질 수 있습니다.
범위	CTE는 정의된 단일 문장의 범위 내에서만 유효합니다.
복잡성	매우 간단한 쿼리에서는 CTE가 불필요한 복잡성을 추가할 수 있습니다.
데이터베이스 지원	모든 데이터베이스 시스템이 CTE를 지원하는 것은 아닙니다. 하지만 대부분의 최신 시스템은 지원합니다.

이렇게 우리는 공통 테이블 표현식의 세계를 여행했습니다. 기본 개념에서 재귀 쿼리까지! CTE를 마스터하려면 연습이 필요합니다. 따라서 자신의 쿼리에서 CTE를 실험해 보지 마세요. 언제쯤에는 CTE를 마스터하고, 동료들을 놀라게 할 만큼 데이터베이스 쿼리를 깨끗하고 효율적으로 작성할 수 있을 것입니다. 즐겁게 쿼리 작성하시길 바랍니다!

Credits: Image by storyset