SELECT * FROM users u
inner join orders o
on u.user_id = o.user_id
where o.payment_method = 'kakaopay';
-> 결과 행 수 : 56
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
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 ;
select * from (
서브쿼리1
) 별칭1
inner join (
서브쿼리2
) 별칭2
on 별칭1.key필드 = 별칭2.key필드;
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 ;
case when 조건1 then 출력값1
else 출력값2
end