SQL 공부 💡 4주차 SUBQUERY 서브쿼리 with절 case문

알아두면 쿼리 짤때 큰 도움이 되는 Subquery(서브쿼리)

 

 Subquery (서브쿼리)란?

: 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미합니다.

 

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

 

- kakaopay 로 결제한 user_id 직관적으로 모두 가져오기

select user_id from orders
where 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'
)

---

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 절에서 서브쿼리의 결과에 해당되는 user_id 조건으로 가지고 온다.

 

---

SELECT에  들어가는 Subquery 

 

- 평균값을 구하는 쿼리를 먼저 짜고

select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'

 

- c.checkin_id, c.user_id, c.likes  필요한 필드로 가지고 온다.

그다음에 평균값을 구하는 위의 쿼리를 SELECT 절에 넣어 매 데이터 한줄마다 실행된다.

그 데이터 한줄의 user_id에 해당하는 데이터의 평균 좋아요 값을 서브쿼리에서 데이터 가지고 온다.

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;

※ '4b8a10e6' 값자리에 서브쿼리에서는 c.user_id 값을 가지고 오는 것

'4b8a10e6' 데이터만 가지고 올수는 없으니!

 

---

FROM에  들어가는 Subquery 

 

- 유저별 좋아요 평균값 구하기

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

 

-여기서 해당 유저별 포인트를 추가로 보고 싶다고 가정해서 쿼리를 짜보면

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

여기서 a는 서브쿼리에 해당한다!!!!

(하나의 테이블이라 생각!)

 

- 순서 -

1.먼저 서브쿼리의 select 가 실행

2. 밖의 select 가 실행

---

SQL 에서 DISTINCT() 는 중 복 제 거!

---

 

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

이와 같이 상단에 with으로 table1 과 table2로 변수처럼 담아서 아래의 쿼리에 대입해 사용한다.

훨씬 한눈에 보기 좋고 깔끔하게 정리된 쿼리를 짤 수가 있다.

 

---

 

CASE문

Q : 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 쿼리를 짜야할때? 어떻게 해야할까?

 

A :

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

: 포인트 보유액에 따라 다르게 데이터를 보여주는 쿼리문 CASE문 사용

CASE 문이 끝날때는 END를 사용해서 마무리

then을 사용해서 원하는 '문자열 사용'

else 는 모든 조건이 아닐 때 출력된다

 

※ when then 은 쌍으로 이루어 사용된다.

 

---

4주차에서 배운 실무에서 더 많이 쓰일 문법을 배웠는데 유용하게 쓰이는 만큼 조금 복잡한 쿼리들이였다.

어렵다 하지만 배워두면 언젠간 아니 곧 쓰일날이 올거같은 느낌이다:)