SUBQUERY

큰 쿼리 안에 들어가는 작은 쿼리

WHERE 절에 사용 된 경우

서브쿼리를 안 쓴 경우

ex) kakaopay로 결제한 유저 조회
SELECT u.user_id, u.name, u.email  
  FROM users u
 inner join orders o
    on u.user_id = o.user_id
 where o.payment_method = 'kakaopay'

서브쿼리를 쓴 경우

ex) kakaopay로 결제한 유저 조회
SELECT user_id, name, email  
  FROM users
 where user_id in (
 	SELECT user_id FROM orders
 	WHERE payment_method = 'kakaopay'
 )

where의 서브쿼리는 직관적으로 이해가 된다.
서브쿼리에서 나온 결과값들안에 큰 쿼리의 user_id가 포함되어있는 값만 조회한다.

SELECT 절에 사용 된 경우

ex) 해당 유저가 쓴 각각의 글 정보와 그 유저의 likes 평균 조회
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

큰 쿼리로 user_id를 불러오고 서브쿼리에 준 후에 서브쿼리는 해당 하는 user_id가 가진 likes의 평균을 반환한다.

FROM 절에 사용 된 경우 (가장 많이 사용됨)

ex) 유저의 총 포인트와 평균 likes
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

이게 왜 from절에 사용 된 경우 인지는 잘 모르겠다.

join은 원래 테이블과 연결짓는데 서브쿼리를 이용해서 테이블인 것 처럼 만들 수 있다.
서브 쿼리 안의 '결과'는 실존하는 테이블이 아니지만 join에 비교군 테이블 처럼 인식 시킬 수 있다.

예제

WHERE 절 예제

ex) 이씨 성을 가진 사람들의 평균 포인트 보다 높은 유저 정보 조회

SELECT * FROM point_users
WHERE point > (
	SELECT AVG(pu.point) FROM point_users pu
	inner join users u on pu.user_id = u.user_id
	WHERE u.name like '이%'
);

SELECT * FROM point_users
WHERE point > (
	SELECT AVG(point)  FROM point_users pu
	WHERE user_id in (
		SELECT user_id FROM users WHERE name like '이%'
	)
);

서브쿼리 안에 서브쿼리 당연히 가능하다

SELECT 절 예제

ex) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
SELECT checkin_id,
	   course_id,
	   user_id,
	   likes,
	   (
	   	SELECT ROUND(avg(likes),1) FROM checkins
	   	WHERE course_id = c.course_id
	   ) as course_avg
  FROM checkins c
  
  ex)checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
  
  SELECT checkin_id,
	     c2.title,
	     user_id,
	     likes,
	     (
	     	SELECT ROUND(avg(likes),1) FROM checkins
	     	WHERE course_id = c.course_id
	     ) as course_avg
    FROM checkins c
   inner join courses c2 on c.course_id = c2.course_id

FROM, JOIN 절 예제

ex) title 별 체크인수와 총수강생수 둘의 비율 조회

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 c
		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

서브쿼리 부분을 따로 떼어냈기 때문에 큰 쿼리 부분을 이해하기 수월하다

결과는 동일