[SQL 문법]WITH 문법 및 가상테이블 만들기/mysql

목차
1. with 란?
1-1. CTEs?
1-2. CTEs의 장점
2. 사용 예시
2-1. having & with
2-2. from subquery & with as
3. with 옵션
4. 임시 테이블과 유효기간 설정

 

1. WITH 란?

CTEs의 문법이다

 

1-1. CTEs?

withcommon table expressions

쿼리를 통해 만들어낸 임시적인 데이터 세트

WITH 테이블 이름 AS (테이블 만들 쿼리문)

 

1-2. CTEs의 장점

- subquery에 비해 가독성이 높음

- subquery와 달리 재사용이 가능함

- 반복된 쿼리에서 성능 높이기 위해 사용됨

 

- 임시테이블 생성(메모리 사용)

- 장시간 걸리는 쿼리의 결과를 저장해두어 해당 결과(데이터)에 바로 액세스할 수 있도록함

 

+ recursive쿼리에 사용됨

자기 자신을 참조해 실행되는 쿼리

자기 자신을 참조해 실행함으로써 >> 부분적인 결과를 반환 & 최종 결과를 얻어낼 때까지 그 과정을 계속 반복함 

 

 

2. 사용 예시

2-1. having & with

Q. 사원(emp) 테이블에서 직업별 월급 합산분을 출력하는데 직업별 월급 합산분의 평균보다 높은 것만 출력

having

select jon, sum(sal)
from emp
group by job
having sum(sal) > (select avg(sum(sal)) from emp group by (job)

with

with hs as
(    select job, sum(sal) as total
     from emp
     group by job
)


select job, total
from hs
where total > (select avg(total) from hs)

 

 

2-2. from subquery vs with

from + subquery(select ~ from ~ join~ where) as TableName

select e2.emp_id 
from (
		select e.emp_id, e.name, e.salary
        from emplyee as e
        left join department as d
        		on e.dept_num = d.dept_num
        where d.name = 'A'
        ) as e2
order by e2.salary desc
limit 0, 1

- JOIN() 총정리 (INNER, OUTER, LEFT, RIGHT, CROSS, SELF)

- limit offset 옵션

    - 시작값을 지정하고 싶을 때

    - limit (10, 10) >> 11행 ~ 20행까지 출력됨

 

with tablename as (원하는 서브커리를 만들기 위한 쿼리문)

select ~ from tablename ~~~

with e2 (emp_id, name, salary)
as (
		select e.emp_id, e.name, e.salary
        from employee as e
        left join deparment as d
        		on e.dept_num = d.dept_num
        where d.name = 'A'
)

select emp_id from e2
order by salary desc
limit 0, 1

 

 

3. with 절 옵션

- materialize : 기본 with 절 사용 시 적용됨, temp(임시테이블)을 사용함 >> 실행시간 단축

- inline : temp 테이블을 생서어하지 않고 inline view로 수행

 

옵션 실행 방법 : with 절 사용 시 select 뒤에 /*+materialize*/, /*+inline*/를 붙임

with hs as (
	select /*+inline */ job, sum(sal) as total
    from emp
    group bu job
)

select job, total
from hs
where total > (select avg(total) from hs)

 

 

4. 임시 테이블 생성 및 저장기한 설정

ex.1) commit 하면 사라지는 임시 테이블 emp_tmp

create global ltemporary table emp_temp
on commit delete rows
as

(
	select *
    from emp
    where 1=2   # table의 틀만 가져오는 방법
)

- where 1=2는 True가 아님 >> 조건문이 해당되는 데이터가 없으르모 현재 from에 있는 table의 틀(컬럼 수 등)만 가져오겠다는 의미

 

emp_tmp를 만든 뒤, ??의 데이터를 모두 집어넣고 조회하면?

[SQL 문법] INSERT INTO 데이터 삽입

insert into emp_temp
select * from emp

 

 

 

데이터 정상 입력 확인

select * from emp_tmp

commit 후 데이터 삭제된 것 확인

commit
select * from emp_tmp

 

 

 

---

+ extra

subquery (오라클)

- select문 내에서 다시 select문을 사용하는 것

- 인라인뷰 : from 다음(하위)에 select문을 사용하는 서브쿼리

- 스칼라 서브쿼리 : select문에 subquery를 사용하는 형태

- where구에 subquery문을 사용하면 서브쿼리라고 함

- with 순환절 ( + connect by(오라클))

 

 

 

참고 자료

 

https://heodolf.tistory.com/71

[SQL] WITH문

https://jione-e.tistory.com/12

[SQLD] SQL 활용 - 계층형 조회, 서브쿼리

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-WITH-%EC%9E%84%EC%8B%9C-%ED%85%8C%EC%9D%B4%EB%B8%94

[MYSQL] 📚 WITH (임시 테이블 생성)

https://kimsyoung.tistory.com/entry/SQL-CTE%EC%9D%98-%EB%AA%A8%EB%B2%94-%EC%82%AC%EB%A1%80%EB%8A%94-%EB%AC%B4%EC%97%87%EC%9D%BC%EA%B9%8C%EC%9A%94

SQL CTE를 잘 활용하려면?

https://johoonday.tistory.com/199

MySQL) WITH절을 이용한 비재귀적 CTE