Subquery

Select 문 안에 또 다른 Select 문이 포함된 형태의 쿼리

스칼라 서브쿼리(Scalar Subqueries)

• Select 절에 위치, 단일행/단일값이 반환되어야 함
• 형태: select 필드명, 필드명, (subquery) from ..
• 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙일때 주로 사용

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;

인라인 뷰 서브쿼리(Inline View Subqueries)🌟

• From 절에 위치, 서브쿼리 결과는 하나늬 테이블로 반환되어야 함
• 형태: select 필드명 from (subquery)
• 가장 많이 쓰임!
• 내가 만든 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

with절

이런식으로 서브쿼리가 여러개 있으면 join절이 헷갈림ㅠㅠ

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

🚨 주의 🚨 with절은 select문 바로 위에 붙여서 써야함! 엔터 금지!

중첩 서브쿼리(Nested Subqueries)

• Where 절에 위치, 단일행/다중행 서브쿼리 연산자 사용 가능
• 형태: where 필드명 in (subquery)
• Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용
• 단일행 서브쿼리 연산자: >, >=, <, <=, =
• 다중행 서브쿼리 연산자
  • IN: 서브쿼리 결과값을 포함하면 출력
  • EXISTS: 서브쿼리 결과값의 행이 존재하면 출력
  • ANY(SOME): 서브쿼리 결과값을 하나라도 만족하는 값 출력
  • ALL: 서브쿼리 결과값을 모두 만족하는 값 출력

// 일반적인 코드
select * from users u 
join orders o 
on u.user_id = o.user_id 
where o.payment_method = 'kakaopay'

// subquery 사용
select * from users
where user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

실전에 유용한 SQL 문법

부분문자열 추출: substring

SUBSTRING(문자열, 시작 Index, 문자개수)
👉🏻 시작 인덱스에서 문자개수만큼 추출

-- 날짜만 출력하기
select order_no, created_at, substring(created_at,1,10) as date from orders

문자열 쪼개기: substring_index

SUBSTRING_INDEX(문자열, 구분자, 구분자 Index)
👉🏻 구분자에 따라 자른 문자열을 인덱스만큼만 가져와라

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(문자열, 구분자, 구분자 Index), 구분자, -1)
👉🏻 가져온 문자열에서 마지막 요소를 가져와라

-- 이메일에서 아이디만 가져오기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

-- 이메일에서 도메인만 가져오기
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

Case

경우에 따라 원하는 값을 필드에 출력할때 사용

case 
	when 조건문 then 원하는값
	when 조건문 then 원하는값
	else 원하는값
end

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