join

공통된 정보( key값)으로 두 테이블을 합치는 것!

• inner join : 교집합-테이블 순서가 바뀌어도 상관 없음
• left join : 특정 테이블을 중심으로 합치는 것=순서가 중요, 데이터가 더 많을 경우 null값이 나올 수 있음

예제>

1.'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!
-내가 쓴 것

select title, count(comment) as cnt from checkins c
inner join courses co 
on co.course_id=c.course_id
group by title

➔ 아래 강의자료 답안처럼 어디 테이블의. 칼럼인지 명시해줄것

-강의자료 답안

select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id 
group by co.title

-강의시 답안

select c1.course_id, c2.title, count(*) as cnt from chekins c1
inner join courses c2 on c1.course_id=c2.course_id
group by c1.course_id

2.주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!

-내가 쓴 것

select u.name as last_name, count(*) as cnt from orders o 
inner join users u on o.email = u.email 
where u.email like '%naver.com'
group by u.name

➔ 또 u.email을 안하고 email이라고만 써서 오류남😠
❗️공통되는 필드가 아니면 꼭 붙여주지 않아도 오류나지 않음 헷갈리지 않으니까!

-강의자료 답안

select u.name, count(u.name) as count_name from orders o
inner join users u
on o.user_id = u.user_id 
where u.email like '%naver.com'
group by u.name

⭐쿼리 실행 순서⭐

from ➔ join ➔ where ➔group by ➔ select

퀴즈>

select o.payment_method , round(avg(pu.point),1) as average from orders o 
inner join point_users pu on o.user_id = pu.user_id 
group by o.payment_method

select u.name, count(e.is_registered)as cnt_name from enrolleds e
inner join users u on e.user_id =u.user_id 
where e.is_registered =0
group by u.name 
order by count(e.is_registered) desc

➔order by가 desc랑 같이 사용하는건데 group by랑 같이 쓸 뻔했다..
갯수 순으로 내림차순 하는거니까 order by 뒤에 count를 쓰면 된당
❗️ count(e.is_registered) 대신에 별칭 써줘도 ok

select e.course_id, c.title, count(*) as cnt_notstart from enrolleds e 
inner join courses c on e.course_id =c.course_id 
where e.is_registered =0
group by e.course_id 

➔ 이번엔 다 출처를 밝혔다 후후😊

select c1.title, c2.week, count(c2.checkin_id) as cnt from courses c1
inner join checkins c2 on c1.course_id =c2.course_id 
group by c1.title, c2.week
order by c1.title //,c2.week

➔ 나는 제목만 정렬해도 week이 정렬이 제대로 돼서 그냥 안했는데 c2.week 까지 붙여도 되고, c2.week만 내림차순으로 정렬할 수도 있다. 뒤에 desc붙이면 됨!

5. 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

-내가 쓴 쿼리

select c1.title, c2.week, count(c2.checkin_id) as cnt from courses c1
inner join checkins c2 on c1.course_id =c2.course_id 
inner join orders o on c2.course_id =o.course_id 
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title

괜히 날짜라서 >= 못쓰는가 해서 헤맸다..
그리고.... course_id로 묶어서 틀림.. 근데 공통되는거면 다 되는 게 아닌가??

❌단순히 같은 컬럼이 존재한다는 이유만으로 join 에 사용하면 원하지 않는 결과물을 보게 될 수 있습니다.
이 내용은 join 의 on 이 무슨 의미를 가지는지 제대로 이해해야만 알 수 있는 내용입니다. 한 번 사용한 필드를 이용해서 다시 join 을 진행하면 안되는 것이 아닌 checkins 테이블과 orders 테이블을 course_id 가 동일하다고 같은 특징을 가진 데이터로 보면 안된다는 것입니다. join 을 수행할 때, on 에 들어가는 조건은 서로 다른 두 테이블에서 데이터를 연결해줄 수 있는 기준을 제시합니다. on 의 조건이 참이면 두 행을 연결하고 거짓이면 연결하지 않는 방식으로 동작합니다. 그럼 checkins 테이블과 orders 테이블의 데이터 내용을 보았을 때, course_id 가 같으면 이 두 행은 같은 특징을 가진 데이터라고 판단하고 연결할 수 있을까요? 아닙니다. 그 이유는 사용자가 남긴 checkin 내역의 과목이 같다고 주문정보인 order 가 연결될 수 있다고 판단할 수 없기 때문입니다. orders 테이블에는 무수히 많은 사람들이 동일한 과목을 주문한 내역이 있고 단순히 과목이 같은 데이터를 하나의 checkin 데이터에 연결하기에는 판단 조건이 부적합합니다. 그래서 checkin을 남긴 사용자와 그 사람의 주문내역인 order 를 찾아 연결해야합니다. 그래서 course_id 가 아닌 user_id 를 이용한 것입니다. 이 내용을 예시를 들어가며 좀 더 추가적인 설명을 FAQ 14번에 남겨두었습니다. 이 내용을 참고하시면 많은 도움이 될거라 생각합니다.
FAQ 14내용 발췌 ➡
course_id 를 가진 주문정보는 무수히 많습니다. course_id 는 애초에 수업에 대한 식별자여서 ‘앱개발 종합반', ‘웹개발 종합반' 과 동일한 의미를 가진 데이터입니다. 그래서 ‘앱개발 종합반’ 을 주문한 모든 주문 정보가 가져와지고, 이를 join 하면 위에서 설명했던 것처럼 모든 데이터가 중복연결되어 생성되기 때문에 오른쪽 첫번째와 같이 무수히 많은 데이터가 생성됩니다. 그래서 course_id 로 join 한 경우에는 count 수가 급증하는 것입니다. 반면 user_id 를 이용하는 경우에는 checkin 데이터에 user_id 가 동일한 주문 정보는 하나이기 때문에 다수의 주문 정보 없이 하나의 데이터만 구분되어 연결되는 것을 확인할 수 있습니다.

-강의 보고 수정한 쿼리

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.course_id =o.course_id 
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title

➔한쪽엔 있는데 한쪽엔 없는 걸로 통계를 내고자 할 때 left join!
ex)가입은 했으나 강의 시작을 안한 사람들은 포인트가 없다

left join도 해조바

⚠️쿼리 작성시 NULL 대문자로~!
⚠️count는 필드 값이 null이면 세지 않는당

select count(pu.point_user_id) as pnt_user_cnt, 
	   count(u.user_id) as tot_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'

➔round((pnt_user_cnt/tot_user_cnt),2) as ratio 요렇게 하면 필드 리스트에 얘네가 없다고 나온다.. 왜죠????
아까 group by에서는 별칭을 써도 됐는데 왜 여기선 별칭을 몬쓰지?

❌실제 컴퓨터에서 동작을 할 때, alias 는 그 구문이 끝나는 마지막에 동작을 합니다. select 에서 alias를 했다면 select 문이 완전히 마무리 되었을 때, 최종적으로 alias 로 지정한 별칭을 지정해주고 동작을 마무리 한다는 의미입니다. 그래서 select 문 안에서 정의하면서 동시에 사용하는 것은 안됩니다. 우리가 보기에는 이미 앞서 count 한 결과를 pnt_user_cnt나 tot_user_cnt 로 alias 한 것 같지만 컴퓨터의 입장에서는 select 에 나온 모든 동작을 마친 후에야 pnt_user_cnt 가 무엇인지 알 수 있기 때문입니다. 그래서 세번째 줄의round(pnt_user_cnt/tot_user_cnt,2) as ratio 에서 pnt_user_cnt 는 아직 select 를 실행중인 컴퓨터에서는 정의되기 전이기에 사용할 수 없는 것입니다.
추가적인 내용으로 group by 에서는 alias 된 별칭 사용이 가능한데 원래 이전에는 동작하지 않았습니다. 이는 쿼리 실행 순서와 관련이 있는 내용인데 group by 가 select 보다 먼저 실행되는 문법이라 select 에서 alias 한 별칭은 group by 에서 아직 정의되지 않아 인식하지 못하는 문제가 있었습니다. 지금에 와서는 버전이 올라가면서 자동으로 인식되어 문제가 없이 동작하지만 다른 데이터베이스 환경에서는 이 또한 오류를 발생시킬 수 있음을 참고하시면 좋을거 같습니다. 컴퓨터 입장에서의 쿼리 실행 순서가 궁금하시다면 FAQ 11 번에 설명해두었으니 참고하시면 도움이 될거라 생각합니다.
FAQ 11 발췌 ➡
FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT > ...

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

➔쿼리를 싹 블럭처리해서 실행시키지 않으면 오류가 난다.. 정말..?
union all 로 합쳐주는 두개의 쿼리는 order by가 있어도 소용이 없다
합친 후! order by해서 정렬해줘야함! ➔ 다음 주 배울 subquery

3주차 숙제

select e.enrolled_id, e.user_id, count(ed.done) from enrolleds e 
inner join enrolleds_detail ed on e.enrolled_id  =ed.enrolled_id 
where ed.done =1
group by e.enrolled_id //,e.user_id
order by count(ed.done) DESC 

group by 두개 들어가나 하나들어가나 차이가 없는 것 같아서 안했는데 여하간 나중에 두개로 묶는게 쓸모 있는 때가 있겠지?

기억해두잡😊😊😊

• 커서 올려둔 부분의 쿼리를 실행한다는 것! 여러개를 써놔도 ok!
• 쿼리 실행 순서!
• 같은 결과에 도달할 수 있는 방법은 여러가지~!