SQL - Subquery (스파르타코딩 - 엑셀보다 쉬운 SQL 4주차)

Subquery란? 

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

 

사용하고 싶은 조건을 따로 만들어 준 후 () 괄호 안에 넣어준다

ex)

1. 쿼리 틀 작성
select * from point_users pu 
where point > (

)


2. 넣어주고 싶은 조건 쿼리 작성
select avg(point) from point_users pu 


3. 조건 쿼리를 끼워 넣는다.
select * from point_users pu 
where point > (
	select avg(point) from point_users pu 
)

 

자주 쓰이는 Subquery 유형

: where / select / from 절

 

1. where 

# Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.
# where 필드명 in (subquery) 

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

 

2. select 

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

 

3. from - 가장 많이 사용됨

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


select pu.user_id, pu.point, a.avg_like 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절 - 쿼리문 깔끔하게 정리

1번 코드 -> 2번코드 

# 1

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

# 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

 

** 실전 SQL (문자열, Case)

1. 문자열 - SUBSTRING_INDEX

: SUBSTRING_INDEX(문자열, 기준 문자, 위치)  *위치 0 : 기준문자 앞(왼쪽), 1: 기준문자 뒤(오른쪽)

SUBSTRING_INDEX(a,b,c) a를 b기준으로 잘라서 1은 앞, -1은 뒤 출력
# 이메일에서 아이디만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

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

2. 문자열 - substring

: SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

substring(a,b,c) a를 b부터 c까지만 출력
# orders 테이블에서 날짜까지 출력
select order_no, created_at, substring(created_at,1,10) as date from orders