[DB] CTE(Common Table Expression)와 파생테이블(derived table) (feat. with)

 

Recursive Common Table Expressions

 

서브 쿼리 (Subquery)

정의

서브 쿼리는 하나의 큰 쿼리 내에 포함된 또 다른 쿼리입니다. 이는 주로 SELECT, INSERT, UPDATE, DELETE 문 내에서 데이터를 필터링하거나 조건을 제공하는 데 사용됩니다.

예시

SELECT * FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Name = 'IT')

 

파생 테이블 (Derived Table)

정의

쿼리의 FROM 절 안에서 정의되는 임시 테이블

쿼리 실행 시 생성되고, 쿼리 실행이 끝나면 사라집니다.

예시

SELECT a.* FROM (SELECT * FROM Employees) AS a WHERE a.Salary > 50000

 

뷰 (View)

정의

데이터베이스의 논리적인 표현으로, 하나 이상의 테이블에서 유도된 데이터를 포함

뷰는 데이터를 저장하지 않고, 데이터에 대한 참조를 제공

예시

CREATE VIEW HighSalaryEmployees AS SELECT * FROM Employees WHERE Salary > 50000;

SELECT * FROM HighSalaryEmployees;

 

CTE (Common Table Expressions)

정의

임시의 결과 집합을 정의하는 SQL 구문

복잡한 쿼리를 간단하게 만드는 데 도움이 됩니다. 선언된 후, 바로 이후의 쿼리에서만 사용할 수 있습니다.

예시

WITH CTE_Employees AS (SELECT * FROM Employees WHERE Salary > 50000)
SELECT * FROM CTE_Employees

CTE는 주로 복잡한 쿼리를 단순화하고 가독성을 높이는 데 사용됩니다.

 

Recursive CTE (Recursive  Common Table Expressions)

정의

Recursive CTE (Common Table Expression)는 CTE 중 자기 자신을 반복적으로 호출하는 CTE

흔히 조직도와 같은 계층적 데이타의 처리나 BOM (Bill Of Materials, 부품재료표) 등을 쿼리하는데 많이 사용된다. Recursive CTE는 특별한 구문을 가지고 있는데, 처음 부분에는 BASE 케이스에 해당하는 부분이고, UNION ALL (혹은 UNION, EXCEPT, INTERSECT) 다음 부분은 Recursive 반복 부분에 해당된다.

예시

-- RECURSIVE CTE
WITH cte (...) AS 
(
   -- Base 부분
   SELECT ... 
   
   UNION ALL
   
   -- Recursive 부분
   SELECT ....
)
SELECT * 
FROM cte;

 

 

차이점

• 범위와 지속성: CTE와 파생 테이블은 쿼리 내에서만 존재하며, 쿼리가 종료되면 사라집니다. 반면, 뷰는 데이터베이스에 저장되어 지속적으로 사용할 수 있습니다.
• 사용 목적: 서브 쿼리는 데이터 필터링이나 조건 제공에 주로 사용되며, CTE와 파생 테이블은 복잡한 쿼리를 단순화하는 데 유용합니다. 뷰는 데이터의 논리적 표현을 제공하고, 데이터 보안 및 접근 제어에도 사용됩니다.
• 복잡도: 서브 쿼리는 단순한 쿼리에 적합하지만, CTE는 복잡하고 반복적인 쿼리에 더 적합합니다. 파생 테이블은 중간 결과를 처리하는 데 유용합니다.

이러한 각각의 기술은 데이터베이스 쿼리를 작성할 때 특정 상황과 요구 사항에 맞게 선택하여 사용할 수 있습니다. 데이터베이스 관리의 효율성을 높이는 데 큰 도움이 됩니다.

 

참조

https://hyeyun133.tistory.com/197

[SQL] CTE, 뷰 그리고 임시 테이블

https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/

What's Better, CTEs or Temp Tables? - Brent Ozar Unlimited®

https://learnsql.com/blog/what-is-common-table-expression/

What Is a Common Table Expression (CTE) in SQL?