SQL Join / Union

송은·2023년 9월 20일
sql정보처리기사
0

Join

데이터를 한 테이블에 모든 정보를 담을 수도 있지만, 불필요하게 테이블의 크기가 커져 불편해진다.
그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만든다.

예를 들어, users 와 checkins 테이블에 동시에 존재하는 user_id 처럼, 서로 다른 두 테이블의 동일한 필드를 연결시켜준다는 의미로 "key"라고 부른다.

위 사진에서처럼 빨간색에 해당하는 필드가 모두 key값에 해당하는 필드이다.

이렇게 두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 Join 이라고 한다.


Left Join

어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있다.

select * from users u
left join point_users p
on u.user_id = p.user_id;
  • 꽉찬 데이터 - point_users 테이블에 존재해서 연결한 경우
  • 비어있는 데이터 - 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우
    (회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우)

Inner Join

select * from users u
inner join point_users p
on u.user_id = p.user_id;

여기에서는 비어있는 필드가 있는 데이터가 없다.
A와 B 테이블의 교집합처럼 같은 user_id 를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이다.


테이블 연결해보기

✨ 연결의 기준이 되고 싶은 테이블을 from 절에
기준이 되는 테이블에 붙이고 싶은 join 절에 위치해 놓는다.

주문 정보에 유저 정보를 연결해 분석하려고 한다. 주문 정보에 유저 정보를 붙여서 확인해보자.

주문을 하기 위해서는 회원정보가 있어야 하고, orders 테이블에 담긴 user_id 는 모두 users 테이블에 존재 한다. → Inner Join

  • orders 테이블에 users 테이블 연결하기
select * from orders o 
inner join users u 
on o.user_id = u.user_id;

'오늘의 다짐' 테이블에 유저 정보를 연결해 분석하려고 한다. '오늘의 다짐' 테이블에 유저 테이블을 붙여서 확인해보자.

  • checkins 테이블에 users 테이블 연결하기 
SELECT * FROM checkins c 
inner join users u 
on c.user_id = u.user_id;

'수강 등록' 테이블에 과목 정보를 연결해 분석하려고 한다. '수강 등록' 테이블에 과목 테이블을 붙여서 확인해보자.

  • enrolleds 테이블에 courses 테이블 연결하기
SELECT * FROM enrolleds e
inner join courses c 
on e.course_id = c.course_id;

테이블 연결 활용

  • '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수 세기
select co.title as '과목명', count(co.title) as '오늘의 다짐' from checkins c
inner join courses co
on c.course_id = co.course_id
group by co.title
  • 유저의 포인트 정보가 담긴 테이블에서 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저 데이터 가져오기
select * from point_users pu 
inner join users u 
on pu.user_id = u.user_id
order by pu.point desc
  • 주문 정보에 유저 정보를 연결해 네이머 이메일을 사용하는 유저 중, 성씨별 주문건수 세기
select name, count(u.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
*from과 join은 항상 붙어다닌다!

  • 결제 수단 별 유저 포인트의 평균값 구해보기
    join 테이블: point_users 에, orders 붙이기
# 반올림 round(숫자, 자릿수)

select o.payment_method, round(avg(pu.point), 0.1) as '유저 포인트 평균' from point_users pu
inner join orders o 
on pu.user_id = o.user_id
group by o.payment_method;

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

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

  • 웹개발, 앱개발 종합반의 week 별 체크인 수 세어보기
select title, week, count(*) from checkins c 
inner join courses c2 
on c.course_id = c2.course_id 
group by c2.title, c.week
order by c2.title, c.week;

  • 앱개발, 앱개발 종합반의 week 별 체크인 수 중에 8월 1일 이후에 구매한 고객들만 확인하기
select c.title, c2.week, count(*) 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

  • enrolled_id 별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬하기 (이 때, user_id 도 같이 출력되어야 한다.)
select e.user_id, e.enrolled_id, count(*) as '수강 완료' 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(*) desc;


테이블 연결 Left Join 활용

  • 유저 중에 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계

users 테이블과 point_users 테이블 left join 하기

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

is NULL 과 is not NULL
*count 는 NULL을 세지 않는다!

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name
select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by name

  • 7월 10일 ~ 7월 19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 확인하고 싶을 때
select count(point_user_id) as '고객 수', count(*) as '전체', round(count(point_user_id/count(*)), 2) as '비율' 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-19"


Union

보고싶은 모습

그림처럼 select를 두 번 하지 않고, 7월과 8월의 데이터를 한 번에 모아서 보고 싶은 경우 union 을 활용할 수 있다.

*이 때, union 을 사용할 경우에는 내부 정렬이 적용되지 않는다. (대신, 서브쿼리를 이용)

예시)

# 8월 데이터
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'
group by c1.title, c2.week
order by c1.title, c2.week
  • month 붙여주기 ('7월' as month,)
select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week
  • 위아래로 union all 붙여주기
(
	select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at < '2020-08-01'
	group by c2.course_id, c2.week
  # order by c2.course_id, c2.week
)
union all
(
	select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at > '2020-08-01'
	group by c2.course_id, c2.week
  # order by c2.course_id, c2.week
)

union 에서는 order by 가 적용되지 않기 때문에 주석처리 해주었다.

profile
송은
개발자
이전 포스트

SQL Group by / Order by

다음 포스트

SQL - IN, NOT IN, EXISTS, NOT EXISTS 차이점 정리

0개의 댓글