SQL _Join, Union

Join

두 테이블의 공통된 정보 = key를 기준으로 테이블을 연결한다.

Left Join

• 어떤 테이블에 무엇을 연결할 것인지 순서가 중요

//orders 테이블에 users 테이블을 join 한다.
//공통된정보인 'key' : on 사용하여 함께사용하는 user_id로 묶겠다.
select * from orders o
left join users u
on o.user_id = u.user_id

• is NULL, is not NULL : NULL 대문자로 작성
  count는 NULL 데이터의 갯수를 세지 않는다.

//user테이블과 point_user테이블을 연결 했을 때
//pu.point_user_id가 null인 것만 이름(성씨별)으로 묶어 갯수를 출력한다.
//강의 미시작으로 포인트가 없는사람들
select u.name, count(*) as cnt from users u
left join point_user pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by u.name

//pu.point_user_id가 is not null인 것만 이름(성씨별)으로 묶어 갯수를 출력한다.
//강의 시작 후 포인트 있는 사람들
select u.name, count(*) as cnt from users u
left join point_user pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by u.name

// 7월 10일~ 7월 19일 가입 고객 중, 포인트 가진 고객 숫자, 전체숫자, 비율 출력
select count(pu.point_user_id) as pnt_user_cnt, 
	   count(u.user_id) as total_user_cnt,
       round(count(pu.point_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'

Inner Join

• 두 테이블의 교집합이라고 생각하면 된다.

select * from orders o
inner join users u
on o.user_id = u.user_id
//null 없는 교집합만 보여준다.

• 함께쓰기 (복습)
  

//checkins 테이블과 course 테이블을 course_id로 합친다.
//course_id 별로 묶어서 course의 타이틀과 갯수를 출력한다.
select ch.course_id, co.title, count(*) as cnt from checkins ch
inner join courses co
on ch.course_id = co.course_id
group by ch.course_id


//users테이블과 point_users테이블을 user_id를 기준으로 합친다.
//point의 내림차순으로 정렬하여 userId,name,email,point를 출력한다.
select u.user_id, u.name, u.email, pu.point from users u
inner join point_users pu
on u.user_id = pu.user_id
order by pu.point desc


//orders테이블과 users테이블을 user_id를 기준으로 합친다.
//naver이메일을 사용하는 user들을 name별로 묶고 name과 갯수를 출력한다.
select u.name, count(*) as cnt from orders o
inner join users u
on o.user_id = u.user_id
where u.email like '%naver.com'
group by u.name


//orders 테이블과 point_users테이블을 user_id기준으로 합친다.
//결제수단별로 묶고, point의 평균을 출력한다.
select o.payment, round(avg(pu.point)) as avg_point from orders o
inner join point_users pu on o.user_id = pu.user_id
group by o.payment


//enrolleds테이블과 users 테이블을 user_id로 합친다.
//수업을 시작하지 않은 사람 (1=시작,0=미시작)들의 같은 성씨별로 묶고 내림차순으로 갯수를 출력한다.
select u.name, count(*) as cnt from enrolleds e
inner join users u on e.user_id = u.user_id
where is_registers = 0
group by u.name
order by cnt desc

//courses테이블과 checkins테이블을 course_id로 합치고,
//checkins테이블과 orders테이블을 user_id로 합친다.
//orders테이블의 created_at으로 주문일이 8월 이후 인 것만 골라내고,
//course title과 week 별 체크인 수를 출력할 때,
//정렬을 course title순 -> week 순으로 정렬한다.
select co.title, ch.week, count(*) as cnt from courses co
inner join checkins ch on co.course_id = ch.course_id
inner join orders o on ch.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by co.title, ch.week
order by co.title, ch.week

Union

union all select 한번에 모아서 보고싶은 경우

select 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' //8월 이후 주문
group by c1.title, c2.week //과목별,주차별로 묶는다.
order by ci.title, c2.week //과목순,주차순 정렬

//1) 위 상태에서 'month'라는 필드(열_Column)를 추가하여 전체 8월이라고 입력한다면?
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 ci.title, c2.week 

//2) 위 상태에서 7월 주문을 같은 방식으로 출력한다면?
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' //8월 이전 주문
group by c1.title, c2.week
order by ci.title, c2.week

// 1)번의 결과와 2)의 결과를 붙여서 한 눈에 보고싶은 경우 'union all'

(
	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 ci.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 ci.title, c2.week 
)