05. SQL- Join의 활용 (A.K.A 3주차 수업 정리)

안녕하세요 한소희입니다. 공부를 통해 배운 내용을 작성하고 있습니다. 혹여 해당 포스팅에서 잘못된 부분이 있을 경우, 알려주시면 빠르게 수정 조치하도록 하겠습니다. 감사합니다.

 

목차

Join이란

Inner Join과 Left Join

다양한 Join 활용법

퀴즈

과제

 

---

 

 

01. Join이란

Join이란, 여러 개의 테이블을 조건(Key)에 맞게 테이블을 연결한 것이다.

 

Join의 예시는 위와 같다.

 

테이블 A와 테이블 B에는 동일한 '아이디'라는 속성이 있다. 이들을 기준으로, 우리가 원하는 데이터만 끌어 와 새로운 테이블을 생성할 수 있는데, 이것을 Join이라고 한다.

---

 

02. Inner Join과 Left Join

 

가장 많이 쓰이는 방법이 바로 inner join과 left join이다.

 

inner join이란, 테이블 A와 테이블 B가 있다고 가정할 때, 이 둘의 교집합만을 join 하는 것을 말한다.

left join이란, 테이블 A와 테이블 B가 있다고 가정할 때, 왼쪽 테이블을 중심으로 join 하는 것을 말한다.

 

 

그림으로 그리면 위와 같다. 

 

 

2-1. Inner Join

 

Inner Join의 간단한 예시는 아래와 같다.

on 뒤에, Join 하는 Key(기준) 값을 작성해주면 된다.

select * from orders o 
inner join users u
on o.user_id = u.user_id 

 

 

2-2. Left Join

 

Left Join의 간단한 예시는 아래와 같다.

on 뒤에 Join할 Key(기준) 값을 작성하면 되는데, 이때는 순서도 Join의 영향을 미친다.

Left Join이므로, on 뒤에 작성해야 할 Key값의 순서에 유의하도록 하자.

 

select * from point_users
left join users
on point_users.user_id = users.user_id

 

---

 

03. 다양한 Join 활용법

 

3-1. Join과 Group By 활용

 

Join을 한 상태에서도 Group By가 가능하다. 예시는 아래와 같다.

# 1. 오늘의 다짐 정보에 과목 정보를 연결해 과목별 오늘의다짐 갯수를 세어보기
select title, count(*) as cnt from checkins c1
inner join courses c2 
on c1.course_id = c2.course_id 
group by c2.title

 

3-2. Join과 Order By 활용

 

Group By가 가능하듯, Join 된 테이블에서 Order By도 가능하다. 

코드에서 `를 사용한 이유
명령어와 속성값을 구분하기 위함이다. `이 없어도 정상적으로 동작하지만 혹시 모르니 작성하는 것을 습관화하도록 한다.

# 2. 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
select pu.user_id, u.name, u.email, pu.point from point_users pu 
inner join users u 
on pu.user_id =u.user_id 
order by pu.`point` desc

 

 

3-3. Join과 Where 활용

 

Join한Join 한 테이블에서는 Where 절도 가능하다. (즉, 일반 테이블에 명령할 수 있는 대부분의 명령은 Join 한 뒤에 생성된 테이블에도 적용이 가능하다고 볼 수 있다.)

# 3. 네이버 이메일 사용하는 유저의 성씨 별 주문건수 세어보기
select u.name, count(*) as cnt from users u
inner join orders o 
on u.user_id = o.user_id 
where u.email like '%@naver.com'
group by u.name

 

 

 

 

---

 

04. 퀴즈

 

퀴즈를 풀어보았다. 퀴즈 문제 같은 경우, 주서울 통해 각 코드 별로 달아두었다.

 

# 퀴즈1. 결제수단 별 유저 포인트의 평균값 구해보기
select o.payment_method, round(avg(pu.point)) from orders o 
inner join point_users pu on pu.user_id = o.user_id
group by o.payment_method 

 

 

퀴즈 1번의 정답은 위의 코드와 같다. 하지만 나는 아래의 코드처럼 작성했고, 결과적으로는 아래와 같은 에러를 마주했다. 에러코드/에러를 살펴보자.

select o.payment_method, avg(*) from orders o 
inner join point_users pu on pu.user_id = o.user_id
group by o.payment_method

❓❗ 에러 살펴보기
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*)

avg(*) 근처에서 에러가 난다는 것을 알 수 있다. 이유는, 평균을 낼만한 대상을 지정하지 않았기 때문이다. count(*)는 인스턴스의 수를 세는 것이기 때문에 에러가 나지 않지만, 평균의 경우에는 평균 낼 속성 값(반드시 속성이 속한 원래 테이블도 올바르게 지정해주어야 한다.)을 작성해야 한다.

 

 

# 퀴즈2. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
select name, count(*) as cnt 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 cnt desc

 

# 퀴즈3. 과목 별로 시작하지 않은 유저들을 세어보기
select c.course_id, c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id 

 

# 퀴즈4. 웹개발, 앱개발 종합반의 week별 체크인 수를 세어보기
select c1.title, c2.week, count(*) 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

 

# 퀴즈5. 연습 4번에서, 8월 1일 이후에 구매한 고객들만 골라 보기
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 date(o.created_at) >= '20200801'
group by c1.title, c2.week 
order by c1.title, c2.week

 

# 퀴즈6. 7월 10일 - 7월 19일에 가입한 고객(created_at) 중, 포인트를 가진(left_join) 고객(on user_id)의 숫자, 그리고 전체 숫자, 그리고 비율을 보기
select count(pu.`point`) as null_cnt,
	   count(u.user_id) as total_cnt,
	   count(pu.`point`)/count(u.user_id) as ratio
 from users u 
 left join point_users pu
 on u.user_id = pu.user_id
where (date(u.created_at) BETWEEN '20200710' and '20200719')

 

퀴즈 6번을 풀 때의 참고해야 할 점은, 'Count는 null값의 수를 세지 않는다'는 것이다.

따라서, 같은 테이블을 중심으로 개수를 세는 것임에도 null_cnt는 null값을 제외한 값이 세어지고, total_cnt는 point가 null인 사람들도 user_id를 모두 갖고 있으므로, 전체 값이 세어지는 것이다.

 

퀴즈 6번을 풀며 몇 가지의 의문증이 생겼다. 의문증에 대한 정리는 아래 해두었다.

 

🔥 count(*)과 count(user_id)의 속도(성능) 차이가 존재하는가?

약간의 성능 차이는 발생할 수 있으나 실질적으로 차이가 없다는 이야기도 있기는 하다고 한다. 그래도 조금이라도 성능을 줄일 수 있는 방향이 있다면 되도록 그 방향으로 가는 것이 좋기 때문에 count(*)보다는 count(u.user_id)를 이용하시는 것을 권장하며, 특정 칼럼이 지정되지 않아도 되는 경우에는 많은 경우 count(1)을 이용한다고 하니 참고해야 할 것 같다.

 

🔥 ratio를 null_cnt/total_cnt as ratio로 지정해주었을 때 에러가 나는 이유는 무엇인가?

강사님께 문의한 결과, SELECT 안에서 지정한 alias는 동일한 SELECT 문 안에서 이용할 수 없다고 한다. 이는 실행 순서와 관계된 내용으로 SELECT 가 동작할 때, alias 가 가장 마지막에 동작하여 정의되기 전에 미리 사용하는 것과 같아 정의되지 않는 값이라는 에러를 발생시키는 것이라고 한다.

MySQL 최근 버전에서는 일부 자동으로 미리 인식하여 동작하는 기능이 추가된 것 같지만 완벽하지 않으니 주의할 필요가 있다고 한다.

 

 

 

# 퀴즈7. Union 사용

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

🔥 Union이란?

두 개 이상의 쿼리문을 하나로 합쳐 보여줄 수 있는 방법이다. 
위의 예시를 보자. 7월인 테이블을 정의하고 8월인 테이블을 정의해, union all 명령어를 이용해 합쳐준다. 그러면 한 결괏값에서 두 테이블이 세로로 합쳐져 보인다.

Union 이전 테이블에 Order By를 하는 것이 의미가 있을까?

의미 없다. 왜냐하면 새로 데이터셋이 합쳐졌기 때문이다. 따라서 Order By를 원한다면, union 된 테이블에 Order By를 명령해야 한다.

 

---

 

05. 과제

 

3주 차 수업을 마무리하는 과제를 수행해보았다. 수행한 코드는 아래와 같다.

과제 주제: enrolled_id 별 수강 완료 한 강의 개수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기

select ed.enrolled_id, e.user_id, count(*) as cnt from enrolleds_detail ed 
inner join enrolleds e 
on ed.enrolled_id = e.enrolled_id
where ed.done = 1
group by ed.enrolled_id
order by cnt desc

 

 

---

 

 

이렇게 3주 차의 수업을 마무리했다.

전공 수업 때 들었던 SQL 수업이 기억이 나기도 했고, 중간중간 잃어버렸던 개념을 보완할 수 있는 소중한 시간이었다.

이제 스파르타 코딩 클럽에서의 SQL 수업이 한 주 밖에 남지 않았다.

배운 내용을 잘 정리해 모두 소화해내는 것을 목표로 수업에 임해야겠다. 마지막까지 파이팅해야지!

오늘의 SQL 학습은 여기서 마무리하도록 하겠다.