[SQL 인터뷰] CTE와 서브쿼리의 차이

데이터 분석가 면접에서는 데이터 분석 및 SQL 쿼리 작성 능력을 평가하기 위해 다양한 질문이 나올 수 있습니다. 이번에는 CTE(Common Table Expressions)와 서브쿼리(Subqueries)의 차이에 대해 물어보는 질문에 대해 다뤄보겠습니다.

질문: CTE와 서브쿼리의 차이를 설명하고, 실제 비즈니스 사례에서 이 두 기능을 어떻게 사용할 수 있는지 예를 들어 설명하세요.

CTE (Common Table Expressions)

CTE는 일시적인 결과 세트를 정의하고 이를 나중에 쿼리에서 참조할 수 있게 하는 방법입니다. 주로 쿼리의 가독성을 높이고 복잡한 쿼리를 단순화하는 데 사용됩니다.

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

서브쿼리 (Subqueries)

서브쿼리는 하나의 쿼리 안에 포함된 또 다른 쿼리를 의미합니다. 주로 WHERE 절, SELECT 절, 또는 FROM 절에서 사용되어 메인 쿼리의 데이터를 필터링하거나 변형합니다.

SELECT column1, column2
FROM table_name
WHERE column1 IN (
    SELECT column1
    FROM another_table
    WHERE condition
);

CTE와 서브쿼리의 차이점

• 가독성: CTE는 복잡한 쿼리를 여러 부분으로 나누어 가독성을 높입니다. 서브쿼리는 종종 쿼리 안에 중첩되어 가독성이 떨어질 수 있습니다.
• 재사용성: CTE는 동일한 쿼리 내에서 여러 번 재사용될 수 있지만, 서브쿼리는 각기 별도로 실행됩니다.
• 성능: CTE와 서브쿼리의 성능 차이는 데이터베이스 시스템과 쿼리의 복잡도에 따라 달라질 수 있습니다. 일반적으로 CTE는 *재귀적 쿼리에 유리합니다.

참고로 “재귀적 쿼리”란 쿼리가 자기 자신을 참조하여 반복적으로 데이터를 처리하는 쿼리를 의미합니다. 재귀적 쿼리는 트리 구조 또는 계층적 데이터를 처리할 때 유용합니다.

■ 예제: 제품 판매량 분석

한 회사에서 제품의 월별 판매량 데이터를 분석하고 있습니다. 가장 많이 팔린 제품의 이름과 해당 월의 총 판매량을 구하여 CTE와 서브쿼리의 차이를 확인해보겠습니다.

CTE 사용 예제

WITH MonthlySales AS (
    SELECT 
        product_name,
        MONTH(sale_date) AS sale_month,
        SUM(sales) AS total_sales
    FROM 
        dataset.sales
    GROUP BY 
        product_name, 
        MONTH(sale_date)
), TopSellingProduct AS (
    SELECT 
        sale_month,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY total_sales DESC) AS row_num
    FROM 
        MonthlySales
)
SELECT 
    sale_month,
    product_name,
    total_sales
FROM 
    TopSellingProduct
WHERE 
    row_num = 1;

CTE 사용 예제에서는 월별 총 판매량을 계산하는 MonthlySales CTE와, 그 결과를 이용해 각 월의 가장 많이 팔린 제품을 구하는 TopSellingProduct CTE를 사용하여 쿼리의 가독성을 높였습니다. CTE를 사용하면 각 부분을 분리하여 쿼리를 단계별로 이해하고 디버깅하기 쉽습니다.

서브쿼리 사용 예제

SELECT 
    sale_month,
    product_name,
    total_sales
FROM (
    SELECT 
        MONTH(sale_date) AS sale_month,
        product_name,
        SUM(sales) AS total_sales,
        ROW_NUMBER() OVER (PARTITION BY MONTH(sale_date) ORDER BY SUM(sales) DESC) AS row_num
    FROM 
        dataset.sales
    GROUP BY 
        product_name, 
        MONTH(sale_date)
) AS Subquery
WHERE 
    row_num = 1;

서브쿼리 사용 예제에서는 서브쿼리에서는 동일한 작업을 하나의 중첩된 쿼리로 처리합니다. 서브쿼리는 하나의 큰 쿼리 안에 포함되어 있어 가독성이 떨어질 수 있지만, 특정 상황에서는 서브쿼리 사용이 더 효율적일 수가 있습니다.

참고로 두 쿼리의 결과는 다음과 같이 동일합니다.

서브쿼리의 사용이 더 효율적인 경우를 예를 들어, 특정 제품의 평균 판매량을 구하고, 그 평균 판매량보다 높은 판매량을 가진 제품들을 찾는 쿼리인 경우가 있습니다.

SELECT 
    product_name,
    sales
FROM 
    dataset.sales
WHERE 
    sales > (
        SELECT 
            AVG(sales)
        FROM 
            dataset.sales
        WHERE 
            product_name = 'Product A'
    );

위의 예제에서 서브쿼리는 Product A의 평균 판매량을 계산하여 단일 값을 반환합니다. 이 값은 메인 쿼리의 WHERE 절에서 사용되어 전체 데이터셋을 필터링합니다. 이런 경우 서브쿼리는 직관적이고 간단하게 필요한 조건을 충족시키는 데이터를 찾는 데 유용합니다. 이렇게 특정 조건을 만족하는 데이터를 필터링하여 그 결과를 바탕으로 추가적인 계산을 할 때 서브쿼리는 CTE보다 더 효율적일 수 있습니다.

면접 시 다루는 방법

1. 기본 개념 이해 확인: CTE와 서브쿼리의 기본 정의와 차이점을 정확히 설명합니다.
2. 구문 설명: 각 기능의 구문을 설명하고, WHERE 절, SELECT 절, FROM 절에서의 사용을 강조합니다.
3. 실제 사례 적용: 비즈니스 문제에서 CTE와 서브쿼리를 사용한 사례를 설명하고, 이를 통해 얻을 수 있는 인사이트를 강조합니다.
4. 코드 예제: 직접 쿼리 예제를 작성하고, 예상 결과를 설명합니다.
5. 추가 질문 대비: 왜 CTE와 서브쿼리가 중요한지, 이들을 사용함으로써 얻을 수 있는 장점과 단점을 설명할 준비를 합니다.