SubQuery & CTE

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

아래의 글과 함께 보시면 좋습니다.

2025.07.16 - [Programming/DataBase] - Sub-Query

SubQuery

서브 쿼리를 사용할 때는 () 괄호로 묶어줘야한다.

---

Independent SubQueries, UnCorrelated SubQuery

외부 쿼리에서 row를 몇 번 탐색하던 상관없이 값이 일정한 서브 쿼리

SELECT
    count(*)
FROM movies
WHERE
    rating > (
        SELECT AVG(rating) -- 평균 평점은 항상 일정함
        FROM movies);

1. 외부 쿼리에서 모든 영화에 대해 평균 평점보다 높은 평점을 가진 영화를 찾는다.
2. 이때, 서브 쿼리의 결과 값은 항상 일정하므로 쿼리 플래너에 의해 1번만 실행하고 그 값을 기억한다.
3. 이러한 서브쿼리를 Independent SubQueries라고 한다.

---

CTE - Common Table Expression

Independent SubQueries를 재사용 할 수 있게 해준다.

• WITH {table_name} AS (Query), {table_name} AS (Query), …
• SELECT 문 이전에 선언해야한다.
• CTE를 호출할 경우 설정한 이름의 임시 테이블을 return한다.
• CTE를 통해 생성한 임시 테이블은 해당 Context내에서만 사용되고 버려진다.

---

CTE를 사용하지 않을 경우

SELECT
    title,
    director,
    revenue,
    ( -- 코드가 반복된다.
    SELECT AVG(revenue)
    FROM movies) AS Avg_revenue
FROM movies
WHERE
    revenue > ( -- 코드가 반복된다.
        SELECT AVG(revenue)
        FROM movies);

---

CTE를 사용할 경우

-- WITH을 사용하여 쿼리를 통해 조회한 데이터를 저장한 임시 테이블을 만든다
WITH avg_Table AS (
    SELECT
        AVG(revenue) AS Avg_Revenue,
        avg(rating) AS Avg_Rating
    FROM movies
)
SELECT
    title,
    director,
    revenue,
    -- 임시 테이블에서 원하는 레코드를 가져와 쓴다.
    round((SELECT Avg_revenue FROM avg_Table), 2) AS Avg_Revenue 
FROM movies
WHERE
    revenue > (SELECT Avg_revenue FROM avg_Table);

---

CTE 여러 개 선언

• SQLite의 경우 CTE를 여러개 선언할 경우 위치에 상관없이 다른 테이블을 참조할 수 있다.
• MySQL이나 Postgresql의 경우 세번째의 CTE가 상위 (1번째, 2번째 CTE)를 참조할 수 있지만,
  하위 (4번째 부터 일반 쿼리문)은 참조할 수 없다.

WITH avg_revenue AS (
    SELECT
        AVG(revenue)
    FROM movies
), avg_rating AS ( -- , 를 사용하여 다른 테이블을 추가로 생성할 수 있다.
    SELECT
        AVG(rating)
    FROM movies
)
SELECT
    title,
    director,
    revenue,
    round((SELECT * FROM avg_revenue), 2) AS Avg_Revenue,
    round((SELECT * FROM avg_rating), 2) AS Avg_Rating
FROM movies
WHERE
    revenue > (SELECT * FROM avg_revenue)
    AND rating > (SELECT * FROM avg_rating);

---

Correlated SubQuery

실행 될 때 외부 쿼리의 row를 참조하는 서브 쿼리

SELECT
    main_movies.title,
    main_movies.director,
    main_movies.rating
FROM
    movies AS main_movies
WHERE
    main_movies.rating > (
    SELECT
        AVG(inner_movies.rating)
    FROM
        movies AS inner_movies
    WHERE
	-- 외부 쿼리의 개봉된 연도를 참조함
        inner_movies.release_date = main_movies.release_date);

해당 코드를 실행할 경우 대략 8시간 30분 정도 걸림

최적화의 필요성

SELECT
    main_movies.title,
    main_movies.director,
    main_movies.rating
FROM
    movies AS main_movies
WHERE
    release_date = 2023 AND -- 최대한 불필요한 행을 덜어내는 최적화가 필요
    main_movies.rating > (
    SELECT
        AVG(inner_movies.rating)
    FROM
        movies AS inner_movies
    WHERE
        inner_movies.release_date = main_movies.release_date);

CTE 사용

-- MySQL, Postgresql의 경우 해당 코드는 실행이 안됨
WITH movie_avg_per_year AS (
    SELECT
        AVG(inner_movies.rating)
    FROM
        movies AS inner_movies
    WHERE
	-- main_movies는 아래 쿼리에 있다.
	-- 하위 쿼리의 main_movies를 참조하는 경우는 SQLIte에서만 가능
        inner_movies.release_date = main_movies.release_date
)
SELECT
    main_movies.title,
    main_movies.director,
    main_movies.rating,
    (SELECT * FROM movie_avg_per_year) AS year_avg
FROM
    movies AS main_movies
WHERE
    main_movies.release_date = 2023
    AND rating IS NOT NULL
    AND director IS NOT NULL
    AND main_movies.release_date > (
    SELECT
        *
    FROM
        movie_avg_per_year);