[SQL] 기본개념 4

개념활용 : left join

left join

ex1)

:7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율

SELECT COUNT(p.point_user_id) as pnt_user_cnt, 
	   COUNT(*) as tot_user_cnt, 
       ROUND((COUNT(p.point_user_id)/COUNT(*)),2) as ratio 
       FROM users u 
left join point_users p on u.user_id = p.user_id 
WHERE u.created_at BETWEEN '2020-07-10' and '2020-07-20'

7월10일 ~ 7월19일에 가입한 고객 중
= Between '2020-07-10' and '2020-07-20'

전체 숫자
= count(*)

---

기본개념 : union

union

:Select를 두 번 하지 않고, 한번에 모아서 보여줌

(
select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)
UNION ALL 
(
select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at < '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
)

(
합칠 쿼리 1
)
union all
(
합칠 쿼리 2
)
여기서, order by는 정렬이기 때문에 합친데다 해야한다.
= 서브쿼리에서 해줄 수 있음

---