Join과 Union

Join(여러테이블 연결)
key 값(공통된 정보, 기준)으로 테이블을 연결

1. Left Join

SELECT * from users u
left join point_users pu
on u.user_id = pu.user_id
/*left에 point_users 붙이기 없으면 null로 보여줌*/

 

2. Inner Join

SELECT * from users u
inner join point_users pu
on u.user_id = pu.user_id
/*교집합만 출력*/

Quiz
1. orders 테이블에 users 테이블을 연결해서 네이버 이메일을 사용하는 유저의 성씨별 주문건수가 많은 순으로 배열

SELECT u.name, COUNT(*) as cnt, o.email from orders o
inner join users u on o.user_id = u.user_id 
where o.email like '%naver%'
group by u.name 
order by cnt desc

/*쿼리 실행 순서: from → join → where → group by → select*/

2. 결제하고 시작하지 않은 유저들(is_registered가 0) 성씨를 세어 내림차순으로 정렬

SELECT u.name, COUNT(*) as cnt_name from enrolleds e 
inner join users u on e.user_id = u.user_id
where is_registered = 0
GROUP by u.name 
order by cnt_name desc

3.  course에 checkins+orders를 붙이고 8월 1일 이후 구매한 고객들만 선별하여 반별, 주차별로 세어 정렬하기

SELECT c.title, c2.week, COUNT(*) as cnt  from courses c 
inner join checkins c2 on c.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 c.title, c2.week
order by c.title, c2.week

4. users에 poin_users를 left join 하여 7/10 ~7/19 가입한 고객중 포인트를 가징 고객의 수, 전체 수, 비율

SELECT COUNT(pu.user_id) as pnt_user_cnt, 
       COUNT(u.user_id) as tot_user_cnt, 
       ROUND(COUNT(pu.user_id)/COUNT(u.user_id), 2) as ratio
 from users u 
 left join point_users pu on u.user_id = pu.user_id
 where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

 

UNION
형태 :  ( ) union all ( )
Union과 Join의 차이
Union은 수직결합, Join은 수평결합
ex) Union은 7, 8, 9월등의 자료를 결합할 때
     Join은 추가적인 데이터를 결합할 때

(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)

팁! 
Count는 Null을 제외하고 셈
Union의 괄호 안에서 order by를 해도 union으로 결합하면 적용X