[SQL] 오라클 WITH절 사용방법

WITH절

오라클 WITH 절은 쿼리 내에서 반복적으로 사용되는 서브쿼리를 임시 테이블처럼 정의하여 코드 간결성을 높이고 가독성을 향상시키는 기능입니다. 쿼리 실행 시 임시 테이블을 생성하고 결과를 저장하며, 쿼리 종료 후 삭제됩니다.

WITH 절의 주요 기능

• 서브쿼리 결과 재사용: WITH 절을 사용하여 서브쿼리 결과를 임시 테이블에 저장하고, 쿼리 내에서 여러 번 참조할 수 있습니다.
• 쿼리 단순화: 반복적인 서브쿼리를 가상 테이블로 대체하여 쿼리 코드를 간결하게 작성할 수 있습니다.
• 가독성 향상: 쿼리 구조를 명확하게 만들어 이해하기 쉽게 합니다.
• 성능 향상: 중복된 데이터 처리를 줄여 쿼리 실행 속도를 높일 수 있습니다.
• 코드 재사용성: 임시 테이블을 통해 코드를 재사용할 수 있습니다.

WITH과 VIEW의 차이점

구분	WITH 절	VIEW
정의	쿼리 내 임시 테이블	스키마 객체
데이터 저장	임시 테이블	실제 테이블 참조
성능	쿼리 속도 향상	쿼리에 따라 다름
유지 관리	간편	별도 관리 필요
사용 범위	쿼리 내	쿼리, INSERT, UPDATE, DELETE
기타	CTE(Common Table Expression)	가상 뷰, 쿼리 뷰

 

WITH 절 기본 구조

WITH [임시테이블이름] AS (
  SELECT ...
  FROM ...
  WHERE ...
)

SELECT ...
FROM [임시테이블이름]
WHERE ...

WITH EXAMPLE AS
(
 SELECT 'WITH절' AS STR1
 FROM DUAL
)

SELECT * FROM EXAMPLE

 

 

 

다중 WITH문

WITH [임시테이블이름1] AS (
  SELECT ...
  FROM ...
  WHERE ...
),
[임시테이블이름2] AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT ...
FROM ...
WHERE ...

WITH EXAMPLE1 AS ( --첫번째 WITH문
SELECT 'EX1' A FROM DUAL 
UNION ALL
SELECT 'EX2' FROM DUAL
), 
EXAMPLE2 AS ( --두번째 WITH문
SELECT 'EX3' A FROM DUAL 
UNION ALL
SELECT A FROM EXAMPLE1 --첫번째 WITH문 참조
)
 
SELECT * FROM EXAMPLE2

 

WITH절 반복사용

WITH EX AS (
SELECT * FROM PRODUCTS 
WHERE 
STANDARD_COST > 1000
)
 
SELECT * FROM EX WHERE EX.CATEGORY_ID = '1'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '2'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '3'

WITH절에 정의된 쿼리는 여러번 사용할수록 효율이 증가합니다.

WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는것과 크게 성능차이가 나지 않습니다. WITH문의 가장 큰장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용한다는것에 큰 의미가 있기에 WITH절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가합니다.

 

 

 

출처: https://coding-factory.tistory.com/445