[SQL] WITH문으로 임시 테이블 처럼 사용하기

 

SQL을 공부하다 보면 어느 순간 너무 복잡한 서브쿼리에 정신이 혼미해질 때가 있다.

그리고 필자가 회사에서 업무 할 때도 마찬가지로 여러개의 테이블을 조인해야하고 이것들을 재활용하는 과정에서 SQL내에 temp테이블을 만들기 어려울때가 많이 있다.

이럴 때 사용하는 강력한 도구, 바로 WITH문, 즉 공통 테이블 식 (CTE) 

필자가 사용했을 때 쿼리를 조금 더 가독성있게 작성 할 수 있었고 정리되는 느낌이었다. SQL을 자주 활용한다면 정리해둬도 좋을 듯 하다.
이번 포스팅에서 WITH문을 처음 접한 분들도 쉽게 이해할 수 있도록 개념, 문법, 활용 예제까지 깔끔하게 정리해 보려고 한다.

 

📌 WITH문이란? (CTE: Common Table Expression)

WITH문은 복잡한 SQL 쿼리를 임시 테이블처럼 분리해서 사용할 수 있게 해주는 기능을 한다.

✅ 주로 서브쿼리를 깔끔하게 정리하거나,
✅ 가독성을 높이고,
✅ 유지보수를 쉽게 하기 위해 사용

 

📌 WITH문의 기본 문법

WITH문은 마치 임시 테이블을 만드는 것처럼 사용한다. 아래는 기본적인 문법이다. 

WITH temp_table AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM temp_table
WHERE ...;

• temp_table은 우리가 임시로 정의하는 테이블 이름
• 괄호 안에는 일반적인 SELECT 쿼리가 들어간다.
• 이후의 메인 쿼리에서는 이 temp_table을 일반 테이블처럼 사용할 수 있다.

💡 한 번 정의한 CTE는 해당 쿼리 안에서만 유효하며, 쿼리 실행이 끝나면 사라진다.

 

📌 WITH문 예제 (단일 CTE)

필자는 보통 많은 예시로 쓰이는 employees 라는 테이블을 활용하겠다.

예를 들어, 어떤 회사의 사원 테이블에서 연봉이 6000 이상인 사원만 골라낸 후, 그 중에서 부서별 평균 연봉을 구하고 싶다.

WITH high_salary AS (
    SELECT emp_id, dept_id, salary
    FROM employees
    WHERE salary >= 6000
)
SELECT dept_id, AVG(salary) AS avg_salary
FROM high_salary
GROUP BY dept_id;

 

 

• high_salary라는 CTE를 만들어서 고연봉자만 추출
• 그 결과를 기반으로 부서별 평균을 구한다.

→ 복잡한 서브쿼리 없이 한 눈에 흐름을 파악할 수 있다.

 

📌 여러 개의 WITH문 (다중 CTE)

CTE는 하나만 정의할 필요는 없다. 쉼표(,)로 구분하여 여러 개의 CTE를 정의할 수 있다.

WITH dept_total AS (
    SELECT dept_id, COUNT(*) AS total
    FROM employees
    GROUP BY dept_id
),
high_salary AS (
    SELECT dept_id, COUNT(*) AS high_count
    FROM employees
    WHERE salary >= 6000
    GROUP BY dept_id
)
SELECT dt.dept_id, 
       dt.total, 
       hs.high_count,
       ROUND(hs.high_count * 1.0 / dt.total, 2) AS ratio
FROM dept_total dt
LEFT JOIN high_salary hs ON dt.dept_id = hs.dept_id;

 

• 첫 번째 CTE: 부서별 전체 인원 수
• 두 번째 CTE: 부서별 고연봉자 수
• 메인 쿼리: 두 결과를 조인해서 비율 계산

이렇게 하면 중간 결과를 각각의 CTE로 분리해서 이해하기 쉬운 쿼리를 만들 수 있다.

 

📌 정리

• WITH문은 가독성, 유지보수성, 재활용 측면에서 매우 유용한 도구
• 복잡한 서브쿼리를 명확하게 정리할 수 있는 강력한 도구로, 자주 쓰는 쿼리 구조라면 적극적으로 활용하는 것이 좋디.