SQL에서 CTEs (공통 테이블 표현) 이해

Key Takeaways

1. CTE는 SQL 쿼리 가독성과 구성을 향상시킵니다.
2. 재귀적 CTE는 계층적 데이터를 쉽게 처리하는 데 도움이 됩니다.
3. CTE는 널리 지원되지만 항상 성능을 향상시키지는 않을 수 있습니다.

Introduction

구조적 쿼리 언어(SQL)는 관계형 데이터베이스를 관리하고 쿼리하기 위한 강력한 도구입니다. 많은 기능 중에서 공통 테이블 식(CTE)은 복잡한 쿼리를 구성하는 우아하고 효과적인 방법으로 두드러집니다. SQL:1999에서 소개되었으며 Microsoft SQL Server, PostgreSQL, MySQL(버전 8.0부터) 및 Oracle과 같은 최신 데이터베이스 시스템에서 널리 지원되는 CTE는 가독성과 유연성을 모두 제공합니다.

What is a CTE?

공통 테이블 식(CTE)은 단일 SQL 문의 실행 범위 내에서 정의된 임시 결과 집합입니다. SELECT, INSERT, UPDATE 또는 DELETE 문 내에서 참조할 수 있는 명명된 쿼리라고 생각하십시오. 서브쿼리와 달리 CTE는 가독성을 향상시키고 재귀를 지원하며 복잡한 작업을 더 간단하고 유지 관리 가능한 부분으로 나눌 수 있습니다.

Syntax of a CTE

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

WITH cte_name (column1, column2, ...)
AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

• WITH 키워드는 CTE를 소개합니다.
• cte_name은 임시 결과 집합에 부여하는 이름입니다.
• 괄호 안에는 선택적으로 열 이름을 지정할 수 있습니다.
• 괄호 안의 SELECT 문은 CTE에 대한 데이터를 정의합니다.

Example: Using a Simple CTE

Employees라는 테이블이 있고 평균 급여보다 더 많이 버는 모든 직원을 찾고 싶다고 가정합니다. CTE를 사용하여 다음과 같이 작성할 수 있습니다.

WITH AverageSalary AS (
    SELECT AVG(Salary) AS AvgSalary
    FROM Employees
)
SELECT Name, Salary
FROM Employees, AverageSalary
WHERE Employees.Salary > AverageSalary.AvgSalary;

이 접근 방식은 기존 서브쿼리에 비해 쿼리를 읽고 유지 관리하기 더 쉽게 만듭니다.

Recursive CTEs

CTE의 가장 강력한 용도 중 하나는 재귀입니다. 재귀적 CTE를 사용하면 조직 차트에서 관리자의 모든 부하 직원을 찾는 것과 같은 계층적 또는 트리 구조 쿼리를 수행할 수 있습니다.

다음은 간단한 예입니다.

WITH RecursiveCTE AS (
    SELECT EmployeeID, ManagerID, Name
    FROM Employees
    WHERE ManagerID IS NULL  -- 최상위 관리자부터 시작
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    INNER JOIN RecursiveCTE r
    ON e.ManagerID = r.EmployeeID
)
SELECT * FROM RecursiveCTE;

이 쿼리는 모든 직원을 관리 계층 구조에 따라 구성하여 반환합니다.

Advantages of Using CTEs

1. Improved Readability: 서브쿼리에 이름을 지정하여 SQL 코드를 훨씬 더 읽기 쉽고 유지 관리할 수 있습니다.
2. Modularization: 복잡한 쿼리를 논리적 빌딩 블록으로 나눌 수 있습니다.
3. Recursive Queries: 재귀적 CTE는 계층적 데이터로 작업하는 데 매우 유용합니다.
4. Reuse: 쿼리 내에서 동일한 CTE를 여러 번 참조할 수 있습니다.

Limitations and Considerations

• Scope: CTE는 정의된 문 내에서만 볼 수 있습니다.
• Performance: CTE는 가독성을 향상시키지만 경우에 따라 서브쿼리나 파생 테이블보다 성능상의 이점을 제공하지 않을 수 있습니다. 항상 쿼리 계획을 확인하고 필요에 따라 최적화하십시오.
• Unsupported Features: 일부 구형 데이터베이스 시스템 또는 이전 버전에서는 CTE를 지원하지 않을 수 있습니다.

Conclusion

CTE는 최신 SQL의 다재다능하고 필수적인 기능입니다. 복잡한 쿼리를 단순화하고 재귀를 활성화하며 SQL 코드의 전체 구성을 개선하는 데 도움이 됩니다. CTE를 마스터하면 더 효율적이고 읽기 쉽고 유지 관리 가능한 SQL 쿼리를 작성할 수 있습니다.

FAQs

What is a CTE in SQL?

A CTE is a temporary result set for organizing complex queries.

Why use a CTE instead of a subquery?

CTEs make SQL queries easier to read and maintain.

Can every database use CTEs?

Most modern databases support CTEs, but older versions may not.

We are Leapcell, your top choice for hosting backend projects.

Leapcell is the Next-Gen Serverless Platform for Web Hosting, Async Tasks, and Redis:

Multi-Language Support

• Develop with Node.js, Python, Go, or Rust.

Deploy unlimited projects for free

• pay only for usage — no requests, no charges.

Unbeatable Cost Efficiency

• Pay-as-you-go with no idle charges.
• Example: $25 supports 6.94M requests at a 60ms average response time.

Streamlined Developer Experience

• Intuitive UI for effortless setup.
• Fully automated CI/CD pipelines and GitOps integration.
• Real-time metrics and logging for actionable insights.

Effortless Scalability and High Performance

• Auto-scaling to handle high concurrency with ease.
• Zero operational overhead — just focus on building.

Explore more in the Documentation!

Follow us on X: @LeapcellHQ