[SQL] 임시 테이블 생성 - WITH

2024. 9. 11. 16:00Language/SQL

 기존에 원하는 결과 테이블을 얻기 위해 'SUBQUERY(하위 쿼리)' 를 자주 사용하였는데, 하위 쿼리를 여러 개 사용하다보면 쿼리 작성이 복잡해지는 경우 겪었고 이 문제를 해결하기 위해 'WITH' 을 사용하게 되었다.

 

WITH 은 'Commont Table Expressions(CTE, 공통 테이블 표현식)' 이라고도 말하는 함수이다. 'CTE' 는 단일 문의 범위 내에 존재하며, 해당 단일 문 안에서 여러 번 참조 가능한 '임시 결과 집합(임시 테이블, 가상 테이블)' 이라 한다.

 

1. 기본 문법

// 임시 테이블(= 가상 테이블)
WITH 임시테이블명1 AS (
    SELECT 컬럼명...
    FROM 테이블명
), 임시테이블명2 AS (
    SELECT 컬럼명...
    FROM 테이블명
)

// 결과 테이블
SELECT 컬럼명
FROM 임시테이블명

 

 'WITH' 은 문법을 보면 알겠지만 기존의 'SUBQUERY' 와는 다르게 '결과 테이블' 을 작성하는 본 쿼리 밖에 '임시 테이블' 을 생성코드를 작성한다. 생성된 '임시 테이블' 들은 서로 참조가 가능하고 당연하게도 본 쿼리에서도 참조가 가능하다. 그리고 무엇보다 코드가 깔끔해져서 'SUBQUERY' 를 사용할 때보다 코드 파악이 수월해 보인다.

 

'임시 테이블' 은 '임시테이블명 AS ( 임시 테이블 작성 쿼리 )' 형식을 통해 생성이 가능하고 하나의 'WITH' 에 다수의 임시 테이블을 생성할 수 있다.

 

 

2. 사용 예시

2-1. 읽기 쉬운 코드

 'city' 테이블에는 나라별 ID, 이름, 인구수에 대한 데이터가 있을 때, 인구수가 전체 나라의 평균 인구수보다 많은 나라를 조회하는 쿼리를 작성한다. 정렬은 나라의 이름을 오름차순으로 정렬한다. (단, 인구 평균은 소수점 첫 째 자리에서 반올림한다.)

WITH
avg_population AS (
    SELECT ROUND(AVG(population), 0) avg_population
    FROM city
) 

SELECT name, population
FROM city
WHERE population > (SELECT avg_population FROM avg_population)
ORDER BY 1

결과 테이블 일부

 

참고로 테이블의 모든 레코드에 대한 인구수(population)의 평균은 '350468' 이었다. 만약 'WHERE' 에 서브 쿼리로 솔루션을 작성했다면 간단한 문제임에도 상당히 복잡해보이는 쿼리가 작성 되었을 것이다. 하지만 'WITH' 을 사용해 파악하기 쉬운 쿼리(코드)를 작성할 수 있었다.


참고 문서