[SQL] Subquery

Subquery

하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것

 

kakaopay로 결제한 유저들의 정보 보기

-- 1 --
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'

-- 2 --
select u.user_id
     , u.name
     , u.email 
  from users u
 where u.user_id in (
 	select user_id 
      from orders
     where payment_method = 'kakaopay'
)

 

---

where 안에 들어가는 subquery

select * 
  from users u
 where u.user_id in (
                    select o.user_id 
                      from orders o 
                     where o.payment_method = 'kakaopay'
                    );

쿼리 실행 순서

1. from 실행: users 데이터를 가져와줌

2. subquery 실행: 해당되는 user_id의 명단을 뽑아줌

3. where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌

4. 조건에 맞는 결과 출력

 

---

select 에 들어가는 subquery

select c.checkin_id
     , c.user_id
     , c.likes
     , (select avg(likes) 
          from checkins c2
         where c2.user_id = c.user_id) as avg_like_user
from checkins c;

 

쿼리가 실행되는 순서

1. 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서

2. select 안의 subquery가 매 데이터 한줄마다 실행되는데

3. 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서

3. 함께 출력해준다.

 

---

from 에 들어가는 subquery (가장 많이 사용)

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

 

쿼리 실행 순서

1. 서브쿼리가 먼저 select 되고

2. 이를 테이블처럼 사용하여 밖의 select가 실행

 

 

---

with 절

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

 

---

실전에서 유용한 SQL 문법(문자열)

이메일에서 아이디만 가져와보기

select user_id
     , email
     , SUBSTRING_INDEX(email, '@', 1) 
  from users

 

이메일에서 도메인만 가져와보기

select user_id
     , email
     , SUBSTRING_INDEX(email, '@', -1) 
  from users

 

orders 테이블에서 날짜까지 출력하게 해보기

select order_no
     , created_at
     , substring(created_at,1,10) as date 
  from orders

 

 

실전에서 유용한 SQL 문법(Case)

포인트 보유액에 따라 다르게 표시해주기

select pu.point_user_id
     , pu.point
     , case 
       when pu.point > 10000 then '잘 하고 있어요!'
       else '조금 더 달려주세요!'
       END as '구분'
  from point_users pu;

 

with table1 as (
     select pu.point_user_id
          , pu.point
	      , case 
            when pu.point > 10000 then '1만 이상'
            when pu.point > 5000 then '5천 이상'
            else '5천 미만'
            END as level
       from point_users pu
)

select level
     , count(*) as cnt
  from table1
 group by level