[MySQL] With 문

[MySQL] With 문

SQL을 사용하여 대량의 데이터를 다루는 경우, 임의의 결과를 정의하고 사용하는 경우

임시 테이블을 만들어 해당 테이블에 값을 저장할 수 있다.

 

WITH

with절은 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용된다.

실제로는 temp라는 임시테이블에 저장되는 가상의 테이블이다.

with <테이블 명> as (
    <쿼리>
)

with절은 임시테이블을 사용하여 저장해놓은 데이터를 액세스하기 때문에, 메모리를 차지한다.

여러 개의 with절을 동시에 돌리면 임시테이블에 부하가 발생해 다같이 느려진다.

 

with절은 다음과 같은 두가지 방식이 이용된다.

 

1) materialize

기본 with절 사용 시 적용되는 것으로  temp를 사용한다. 

 

2) inline

temp 테이블을 생성하지 않고 inline view로 수행한다.

with절 사용 시 select 뒤에 /*+inline */을 붙여주면 된다.

with cte as (
 select /*+inline */ date, dept_no, dept_name
 from department
)

 

Example

아래는 with절을 사용한 예시다.

 

개발자들의 정보가 담긴 테이블에서, 프로그래밍 스킬에 따라 grade를 나누어 검색해야한다. 

프로그래밍 스킬의 코드는 코드 테이블에서 검색이 가능하다.

with 
f as (
    select sum(code) as code
    from skillcodes 
    where category = 'Front End'
),
p as (
    select code
    from skillcodes
    where name = 'Python'
),
c as (
    select code
    from skillcodes
    where name = 'C#'
)

select
case 
    when skill_code & p.code and skill_code & f.code then 'A'
    when skill_code & c.code then 'B'
    when skill_code & f.code then 'C'    
end as 'grade', id, email
from developers, f, p, c
group by grade, id, email
having grade is not null
order by grade, id

코드 테이블에서 특정 프로그래밍 스킬만 검색한 데이터를 임시 테이블에 저장 후,

select 문에서 가져와서 사용한다. 

 

만약 서브쿼리를 사용했다면 아래와 같이 표현이 됐을 것이다.

case 
    when skill_code & (select code from skillcodes where name = 'Python')
end as 'grade', id, email

 

with절을 사용함으로써 쿼리의 가독성이 좋아진 것을 확인할 수 있다. 

 

참고

https://schatz37.tistory.com/46