with statement in SQL

with 문을 사용하면 SQL 문장을 매우 직관적으로 사용할 수 있는데, 다음과 같이 몇 가지 형태로 사용할 수 있다.

 

1. sub query를 with 문 으로 

with 
list1 as ( 
	select id, area
    from tbl_address
    where area = 'seoul'
	), 
 list2 as (
 	select subject, avg(score)
    from tbl_score 
    where id in (select id from list1)
    group by subject
 	)
  select *
  from list2;

물론 위의 쿼리는 with문을 사용하지 않고 처리할 수도 있지만,

with 문안의 가상테이블에서 다른 가상테이블을 조건으로 사용할 수도 있으며 복잡한 쿼리를 단순하게 직관적으로 표현할 수 있다.

 

2. 엑셀등의 외부 텍스트 데이터를 붙여 넣어서 사용하고 싶을때 

-- Postgres
with 
list (id, name) as (
	values 
	(1004007, '홍길동'), 
	(1002147, '장동건'), 
	(1004493, '현빈'), 
	(1007978, '이서진'), 
	(1005218, '원빈'), 
	(1005507, '이병헌')
    ) 
 select l.id
 	, l.name 
    , a.address 
    , a.post_code 
 from list l 
 left join address a on a.id = l.id;
 
 -- Mysql (8.0 이후)
with 
list (id, name) as (
	values 
	ROW (1004007, '홍길동'), 
	ROW (1002147, '장동건'), 
	ROW (1004493, '현빈'), 
	ROW (1007978, '이서진'), 
	ROW (1005218, '원빈'), 
	ROW (1005507, '이병헌')
    ) 
 select l.id
 	, l.name 
    , a.address 
    , a.post_code 
 from list l 
 left join address a on a.id = l.id;

데이터가 몇 개 안될때는 그냥 여러 subquery를 union으로 묶어서 사용할 수도 있지만,

몇 백 또는 몇 천건의 데이터라면 엑셀등에서 한 row의 값을 (1004007, '홍길동'),  와 같은 형태로 만들어서 

사용하면 편리하다.