[TIL] 내일배움캠프 67일차_[MySQL] WITH절

👀Today I Learn

WITH절

• WITH 절은 공통 테이블 표현식(CTE, Common Table Expression) 을 정의하는 데 사용
  • CTE는 임시적인 결과 집합을 만들고, 이를 메인 쿼리에서 참조할 수 있도록 함
• 기존의 서브쿼리와 유사하지만,
  • 가독성이 높음
  • 재사용이 가능
  • 재귀 쿼리를 작성할 때도 유용

1. WITH절 기본 사용법

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

• cte_name은 임시적인 테이블 이름이며, 메인 쿼리에서 참조할 수 있음
• CTE 내부의 SELECT 문이 실행된 후, 메인 SELECT 문에서 해당 결과를 사용

2. WITH 절 예제

WITH high_salary AS (
    SELECT emp_no, first_name, salary
    FROM employees
    WHERE salary > 70000
)
SELECT * FROM high_salary;

• 결과
  • salaries 테이블에서 salary > 70000인 직원 목록을 high_salary라는 CTE에 저장
  • 최종적으로 SELECT * FROM high_salary를 실행하여 데이터를 조회

3. WITH 절의 장점

• 가독성 증가
  • 서브쿼리보다 코드가 깔끔해지고, 복잡한 쿼리를 쉽게 이해할 수 있음
• 재사용 가능
  • 동일한 서브쿼리를 여러 번 사용할 때 CTE를 정의하면 불필요한 반복을 줄일 수 있음
• 성능 최적화
  • CTE를 사용하면 MySQL의 옵티마이저가 최적화하기 용이하며, 일부 상황에서 쿼리 성능이 향상될 수 있음

CTE 사용하기

1. 다중 CTE 사용하기

• 여러 개의 CTE를 정의하고 사용할 수도 있음

    WITH high_salary AS (
        SELECT emp_no, first_name, salary
        FROM employees
        WHERE salary > 70000
    ),
    low_salary AS (
        SELECT emp_no, first_name, salary
        FROM employees
        WHERE salary <= 70000
    )
    SELECT * FROM high_salary
    UNION ALL
    SELECT * FROM low_salary;
      
  

  • high_salary와 low_salary 두 개의 CTE를 정의하고, 이를 UNION ALL로 합쳐 결과를 조회

2. 재귀 CTE (Recursive CTE)

• MySQL 8.0부터 재귀 쿼리를 지원하는 WITH RECURSIVE를 사용 가능

• ✅ 부모-자식 관계 데이터를 조회하는 예제

    WITH RECURSIVE employee_hierarchy AS (
        -- 기초 단계 (Anchor)
        SELECT emp_no, first_name, manager_id
        FROM employees
        WHERE manager_id IS NULL  -- 최고 관리자 찾기
      
        UNION ALL
      
        -- 재귀 단계 (Recursive)
        SELECT e.emp_no, e.first_name, e.manager_id
        FROM employees e
        JOIN employee_hierarchy eh ON e.manager_id = eh.emp_no
    )
    SELECT * FROM employee_hierarchy;
      
  

  • UNION ALL을 이용하여 최고 관리자부터 시작하여, 그의 하위 직원들을 재귀적으로 찾아가는 구조
  • 조직도의 계층 구조를 나타낼 때 유용

서브쿼리과 WITH 절 비교

비교 항목	서브쿼리	WITH 절 (CTE)
가독성	낮음 (중첩 가능)	높음 (구조적)
재사용성	없음	있음
성능 최적화	경우에 따라 느릴 수 있음	옵티마이저에 의해 최적화될 수 있음
재귀 쿼리	불가능	가능 (WITH RECURSIVE)

WITH 절을 사용할 때 주의할 점

1. MySQL 8.0 이상에서 지원
   • WITH 절은 MySQL 8.0 이상에서만 지원
   • MySQL 5.x에서는 사용할 수 없음
2. 임시 테이블처럼 사용 가능하지만 영구적이지 않음
   • CTE는 쿼리 실행 동안에만 유지되고, 데이터베이스에 저장되지 않음
3. 복잡한 CTE는 성능 이슈를 유발할 수도 있음
   • 너무 많은 CTE를 중첩하면 성능이 저하될 수 있으므로, 옵티마이저의 실행 계획을 확인하는 것이 좋음

정리

• ✅ WITH 절은 공통 테이블 표현식(CTE) 을 정의하는 데 사용됨
• ✅ 가독성이 좋고, 코드 재사용이 가능하며, 재귀 쿼리(WITH RECURSIVE)도 작성 가능
• ✅ MySQL 8.0 이상에서 지원됨
• ✅ 기존 서브쿼리보다 최적화되거나, 더 효율적으로 실행될 가능성이 있음

💡Today I Thought

오늘의 체크리스트

• 알고리즘 코드카타 296-300
• SQL 코드카타 100
• LLM 과제 - 논문 2편 정리
• TIL 작성

회고

  오늘도 야무진 하루! 코드카타가 너무 어려워서 이제 5문제 푸는데 두시간이 걸려버린다.. 쉬운거 있을 때는 많이 풀고 아닌 날은 좀 줄여야 공부 효율이 날 것 같다. 물론 코테 하면서 배우는 함수들이 많기는 하지만, 지금은 해야할 것이 많으니까.. 꾸준히 하는 것에 의미를 둬야겠다.