공통 테이블 식(CTE)

공통 테이블 식(Common Table Expression, CTE)은 WITH 절을 사용해 정의하는 임시 결과 집합입니다.

CTE를 활용하면 복잡한 쿼리를 더 읽기 쉽게, 구조화하기 쉽게, 그리고 유지보수하기 쉽게 만들 수 있습니다.

이번 수업에서는 비재귀 CTE를 다룹니다.

즉, CTE가 자기 자신을 반복적으로 호출하지 않고 한 번만 사용하는 경우입니다.

CTE의 기본 문법은 다음과 같습니다.

복사
WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name;

• WITH: CTE 블록을 정의
• cte_name: 메인 쿼리에서 참조할 수 있는 임시 테이블명
• 메인 쿼리에서 CTE를 일반 테이블처럼 사용 가능

다음 쿼리는 2개 이상 강의를 선택한 사용자를 반환합니다.

복사
WITH multi_course_users AS (
  SELECT user_id, COUNT(*) AS course_count
  FROM user_courses
  GROUP BY user_id
  HAVING COUNT(*) >= 2
)
SELECT u.name, m.course_count
FROM multi_course_users m
JOIN users u ON u.user_id = m.user_id
ORDER BY u.name;

이 쿼리는 다음과 같이 동작합니다.

1. multi_course_users CTE에서 두 개 이상 강의를 수강한 사용자 ID와 강의 개수를 계산
2. 메인 쿼리에서 users 테이블과 Join하여 사용자 이름과 수강 강의 수를 조회

• 가독성: 복잡한 쿼리를 작은 단위로 나눠 보기 쉽게 만듦
• 모듈성: 쿼리 일부를 재사용하거나 개별적으로 테스트 가능
• 유지보수성: 로직이 분리되어 있어 변경이 용이함

쉼표(,)로 구분해 여러 개의 CTE를 한 번에 정의할 수 있습니다.

복사
WITH active_users AS (
  SELECT DISTINCT user_id FROM user_courses
),
recent_users AS (
  SELECT * FROM users WHERE name LIKE 'S%'
)
SELECT *
FROM active_users a
JOIN recent_users r ON a.user_id = r.user_id;

• active_users: 강의를 수강한 사용자 목록
• recent_users: 이름이 S로 시작하는 사용자 목록

메인 쿼리에서 두 CTE를 Join하여 교집합을 조회합니다.

CTE를 활용하면 복잡한 Join이나 중첩 서브쿼리를 깔끔하게 정리할 수 있어, 데이터 분석이나 리포트 작성에서 특히 유용합니다.