2023.03.26 - SQL

subquery

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

1. WHERE절에 쓰이는 subquery

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

1. join

SELECT * FROM users u
inner join orders o 
on u.user_id = o.user_id 
where o.payment_method = 'kakaopay';

-> 결과 행 수 : 56

2. subquery

SELECT * FROM users u 
WHERE user_id in (
	SELECT user_id FROM orders o 
	where payment_method = 'kakaopay'
);

-> 결과 행 수 : 51

※ 결과 행 수가 다른 이유
1. join 사용 시, 앱개발 or 웹개발 구매 user는 2개의 데이터(user 중복o)
2. subquery 사용 시, 앱개발 or 웹개발 둘 중 하나라도 kakaopay를 이용한 user를 걸러냄 (user 중복x)
-> join 시, user_id에 distinct를 걸어주면 결과 행 수 동일

SELECT DISTINCT u.user_id  FROM users u
inner join orders o 
on u.user_id = o.user_id 
where o.payment_method = 'kakaopay';

-> 결과 행 수 : 51

2. select 절에 쓰는 subquery

ex) checkin_id 별, 평균 like

select 절에 쓰는 subquery
-> select가 될 때마다, 하나하나의 행에 대해 subquery라 실행하게 되는 것

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

3. from 절에 쓰는 subquery (가장 많이 사용)

• 방금 만든 select 문을, 마치 이전에 있었던 테이블처럼 사용하는 것

4. 2개의 subquery inner join하기

select * from (
	서브쿼리1
	) 별칭1
inner join (
	서브쿼리2
	) 별칭2
on 별칭1.key필드 = 별칭2.key필드;

5. with절

• 쿼리 결과를 하나의 테이블처럼 사용
• from 안에 서브쿼리 사용 시, with 절 사용
• 예시

SELECT c2.title, vi1.cnt_checkins, vi2.cnt_total,
		ROUND(vi1.cnt_checkins/vi2.cnt_total, 4) AS ratio
FROM (
	select course_id , count(DISTINCT user_id) AS cnt_checkins FROM checkins c 
	GROUP BY course_id 
	) vi1
INNER JOIN (
	select course_id , COUNT(*) as cnt_total  from orders o 
	group by course_id
	) vi2
ON vi1.course_id = vi2.course_id
INNER JOIN courses c2 
ON vi1.course_id = c2.course_id ;

with table1 as (
	select course_id , count(DISTINCT user_id) AS cnt_checkins FROM checkins c 
	GROUP BY course_id 
), table2 as (
	select course_id , COUNT(*) as cnt_total  from orders o 
	group by course_id
)
SELECT c2.title, vi1.cnt_checkins, vi2.cnt_total,
		ROUND(vi1.cnt_checkins/vi2.cnt_total, 4) AS ratio
FROM table1 vi1
INNER JOIN table2 vi2
ON vi1.course_id = vi2.course_id
INNER JOIN courses c2 
ON vi1.course_id = c2.course_id ;

6. 실전에서 유용한 SQL 문법들

1. 문자열 데이터 다루기
   1) 문자열 쪼개기 : substring_index(필드, 쪼갤 문자열, 출력할 대상(숫자))
   2) 문자열 일부만 출력하기 : substring(필드, 시작 숫자, 자를 개수)
   ㄴ 시작 숫자: 1부터 시작
2. case문 : 경우에 따라 다른 값을 출력

   case when 조건1 then 출력값1
   else 출력값2
   end