서브쿼리

• 쿼리문 안에 들어가는 쿼리문
• SELECT, FROM, WHERE에 들어감

select * 
from users
where user_id in (
	select user_id from orders
    where payment_method = "kakaopay"
)
    
    
select 
	c.checkin_id,
	c.user_id,
	c.likes,
	(
		select avg(likes) from checkins
		where user_id = c.user_id
	) as avg_likes_user
from checkins c


select 
	pu.user_id, 
	pu.point, 
	a.avg_likes
from point_users pu 
	inner join (
		select user_id, round(avg(likes), 1) as avg_likes
		from checkins
		group by user_id 
	) a
	on pu.user_id = a.user_id

• 다중으로도 사용 가능함

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

With

• 서브쿼리로 만들어진 결과물을 테이블처럼 사용할 수 있게 만들어 줌

WITH table1 as (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id 
)

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id