[SQL] 서브쿼리문, Subquery 사용하기

1) Subquery란?

쿼리 안의 쿼리라는 의미입니다. 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해지는 장점이 있따.

 

ex)

• kakaopay로 결제한 유저들의 정보 보기
  • → 우선, 이렇게 볼 수 있겠죠? users 와 orders 의 inner join으로!

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'

 

같은 결과를 서브쿼리를 이용해서 더 직관적으로 보기

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

---

2) 서브쿼리 사용법

1. Select 에 들어가는 서브쿼리

테이블의 결과값으로 서브쿼리를 사용할 때,

ex) 결과값 row의 각 평균값을 같이 조회하고 싶은 경우

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;

👉 이런식으로 서부쿼리의 결과값을 같이 조회할 수 있다.(반올림은 round(value,소수점 자리)를 이용)

 

2. From 에 들어가는 Subquery(가장 많이 사용되는 유형이라고 한다)

👉 From은 언제 사용하면 좋을까요? "내가 만든 Select"와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!

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가 실행!

=> select 된 user id 와 기존 테이블과 조인

---

3) With절 사용하기

with절은 일정의 임시테이블이다.

사용하고자 하는 서브쿼리의 결과를 임시 테이블로 만들어 놔서, 조금 더 깔금하고 단조롭게 사용가능하게 하는 것이 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 절을 사용하면 훨씬 보기 좋게 사용할 수 있다.

with절 에 임시 테이블 2개를 생성해서 사용하는 방법이다.

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

---

4) SQL 문자열 다뤄보기

1. 문자열 나누기 (SUBSTRING_INDEX )

👉 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져와 보자

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

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

 

 

2. 문자열 일부만 추출하기 (SUBSTRING )

Substring을 이용해서 원하는 문자열 부분만 추출할 수 있다.

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

응용 ) 일별 주문량 계산

select substring(created_at,1,10) as date, count(*) as cnt_date from orders group by date

---

5) CASE 문법 - SQL if문

쿼리문에서 if문 처럼, 특정 조건에 따른 결과값을 반화해주고 싶을 때, CASE 문법을 이용할 수 있다.

select lv, count(*) as cnt from (
	select pu.point_user_id, pu.point,
	(case 
      when pu.point > 10000 then '1만 이상'
      when pu.point > 5000 then '5천 이상'
      else '5천 미만'
      END) as lv
	from point_users pu
) a
group by lv;