[MySQL] With절 이란? select 말고 with으로 시작하는 구문? CTE

안녕하세요~ jju_developer 입니다.

 

비가 많이 오는데 건강 유의 하시길 바라며,

 

오늘도 어김 없이 퇴근 후 복습을 하러 돌아왔습니다.

 

근무하다가 복잡하고 중복되는 select 문을 만들어야 하는 부분이 있었습니다.

 

이에 가독성과 유지보수를 쉽게 하기 위해서 with절을 사용하게 되었습니다.

 

CTE란? Common Table Expressions

CTE는 쿼리 내에서 일시적으로 사용할 수 있는 이름 있는 결과 집합입니다.

 

즉, 일시적으로만 사용한다가 키 포인트 입니다!

 

복잡한 쿼리를 분해하고 재사용 가능하게 만드는 데 도움을 줍니다.

 

평소 서브쿼리가 복잡하게 있는 쿼리는 가독성도 떨어져서 with 절을 알아보고 적용하게 되었습니다.

 

꼬리에 꼬리를 물어 해결하고자 하는 의지!

지금부터 시작합니다~

 

WITH 절을 꼭 써야할까? 이유는?

1. 가독성 및 유지보수성

2. 중복 제거

3. 성능 최적화

---

1. 가독성 및 유지보수성

서브쿼리를 중첩해서 사용하면 쿼리가 복잡해지고 읽기 어려워질 수 있다고 합니다.

쿼리를 보기에도 쉽고 논리적인 블록으로 나누어 가독성과 유지보수성을 높일 수 있습니다.

 

2. 중복 제거

동일한 서브쿼리를 여러 번 사용해야 하는 경우,

WITH 절을 사용하여 한 번 정의하고 여러 번 참조가 가능하다고 합니다.

이게 바로 위에서 말한 재사용 가능하다는 것이죠!

 

말이 좀 이해가 안가서 예시를 찾아보았습니다.

 

아래 예시는 동일한 서브쿼리를 여러 번 사용해야 하는 상황입니다.

 

AS IS

SELECT 
    e.EmployeeID,
    e.EmployeeName,
    (SELECT AVG(Salary) FROM Salaries s WHERE s.EmployeeID = e.EmployeeID) AS AvgSalary,
    (SELECT COUNT(*) FROM Projects p WHERE p.EmployeeID = e.EmployeeID) AS ProjectCount
FROM Employees e
WHERE e.DepartmentID = 1;

이 예제에서는 각 직원의 평균 급여와 프로젝트 수를 구하기 위해 동일한 서브쿼리를 두 번 사용하고 있습니다.
- 동일한 서브쿼리를 각각의 열에 대해 두 번 작성해야 합니다.
- 서브쿼리를 각각의 열에서 중복하여 사용하기 때문에 코드가 중복되고 가독성이 떨어집니다.

 

 

TO BE

WITH EmployeeData AS (
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        AVG(s.Salary) AS AvgSalary,
        COUNT(p.ProjectID) AS ProjectCount
    FROM Employees e
    LEFT JOIN Salaries s ON e.EmployeeID = s.EmployeeID
    LEFT JOIN Projects p ON e.EmployeeID = p.EmployeeID
    WHERE e.DepartmentID = 1
    GROUP BY e.EmployeeID, e.EmployeeName
)
SELECT 
    ed.EmployeeID,
    ed.EmployeeName,
    ed.AvgSalary,
    ed.ProjectCount
FROM EmployeeData ed;

WITH 절을 사용하여 EmployeeData CTE를 정의했습니다.
+ CTE에서는 필요한 데이터를 한 번에 계산하고, 이를 여러 번 참조하여 메인 쿼리를 작성합니다.
+ 이렇게 하면 쿼리의 중복도 없애고, 유지보수도 쉽고 가독성이 향상되죠?

 

 

3. 성능 최적화

WITH 절을 사용하면 복잡한 쿼리에서 특정 부분을 한 번만 계산하도록 하여 성능을 최적화할 수 있습니다.
특히, 동일한 서브쿼리가 여러 번 사용될 때, WITH 절을 사용하면 불필요한 중복 계산을 피할 수 있습니다.

 

 

그렇다면 VIEW랑 비슷해 보이죠?

 

차이점을 한번 알아보겠습니다~!

 

 

※ VIEW 와 WITH절의 차이점

 

1. 지속성:

CTE (WITH 절): CTE는 쿼리 실행 시에만 존재하며, 쿼리가 완료되면 사라집니다. 임시 테이블과 같은 역할을 하지만, 일시적인 것입니다.
VIEW: 뷰는 데이터베이스에 영구적으로 존재합니다. 한 번 생성되면 삭제하거나 변경하기 전까지 지속됩니다.
성능:

CTE: CTE는 매 쿼리 실행 시마다 생성되고 사용됩니다. 따라서 큰 데이터셋을 처리할 때 성능에 영향을 줄 수 있습니다.
VIEW: 뷰는 데이터베이스에 저장된 쿼리로, 인덱스를 사용할 수 있습니다. 이는 동일한 쿼리를 반복 실행할 때 성능을 향상시킬 수 있습니다.

 

 

2. 사용 범위:

CTE: 단일 쿼리 내에서만 사용할 수 있습니다. 하나의 SELECT, INSERT, UPDATE, 또는 DELETE 문 내에서만 참조 가능합니다.
VIEW: 데이터베이스의 다른 쿼리나 트랜잭션 내에서도 참조할 수 있습니다. 한 번 정의되면 여러 쿼리에서 재사용할 수 있습니다.

 

 

3. 정의 및 관리:

CTE: 정의가 간단하며, 특정 쿼리 내에서만 사용되므로 별도의 관리가 필요하지 않습니다.
VIEW: 생성 및 관리를 위해 CREATE VIEW, ALTER VIEW, DROP VIEW 등의 명령어가 필요합니다. 뷰의 정의가 변경되면 이를 사용하는 모든 쿼리에 영향을 미칩니다.

 

 

4. 차이점 예시!

with절

WITH SalesData AS (
    SELECT
        EmployeeID,
        SUM(SaleAmount) AS TotalSales
    FROM Sales
    WHERE SaleDate BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY EmployeeID
)
SELECT 
    e.EmployeeName,
    s.TotalSales
FROM Employees e
JOIN SalesData s ON e.EmployeeID = s.EmployeeID;

 

 

view

-- VIEW 생성
CREATE VIEW AnnualSales AS
SELECT
    EmployeeID,
    SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE SaleDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY EmployeeID;

-- VIEW 사용
SELECT 
    e.EmployeeName,
    a.TotalSales
FROM Employees e
JOIN AnnualSales a ON e.EmployeeID = a.EmployeeID;

 

 

정리 : 

WITH 절은 쿼리 내에서 임시적으로 사용되는 테이블로, 단일 쿼리 내에서만 참조 가능하지만,
VIEW는 데이터베이스에 영구적으로 저장됩니다.

 

오늘도 수고하셨습니다~!~!~!~!