테이블은 각 테이블마다 그 목적, 특성에 맞게끔 데이터(컬럼) 정보를 가지고 있다. user라는 테이블은 사용자 정보 관련된 컬럼과 필드들로 구성되어 있고, checkin은 출결, 강의 후기, 좋아요 갯수 등 출결과 관련된 정보를 가지고 있다.
이 둘은 별개의 데이터들을 가지고 있지만 user_id라는 교집합 부분이 있다. 이 부분을 통해서 해당 유저 의 출결 등 상황을 확인하고자 할 것이다.
이처럼 테이블마다 이런 연결 고리를 만들게 되는데, 추후 배우게 될 Primary Key, Foreign Key와도 연관이 있다. 일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다.
하지만 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다. 우선 PK, FK를 배우기 전에 두 컬럼의 형식이 같다는 전제(Equal Join)로 JOIN이 되는 상황을 먼저 실습해봤다.
동등 조인은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 대부분 PK<->FK 관계를 기반으로 한다. 그러나 일반적으로 테이블 설계 시에 나타난 PK<->FK의 관계를 이용하는 것이지 반드시 이 관계로만 EQUI JOIN이 성립하는 것은 아니다. 이 기능은 계층형이나 망형 데이터베이스와 비교해서 관계형 데이터베이스의 큰 장점이다. 해당 JOIN의 조건은 WHERE 절에 기술된 '=' 연산자를 사용하여 표현하게 된다.
JOIN이 필요한 기본적인 이유는 정규화에 관련되어있는데, 정규화란 불필요한 데이터의 정합성을 확보하고 이상현상 발생을 피하기 위해, 테이블을 분할하여 생성하는 것이다.
테이블을 정규화하여 데이터를 분할하게 되면 과부하 문제도 있고, 규모가 꽤 커지는 문제점을 해결 할 수 있다. 그렇지만 특정 요구조건을 만족하는 데이터들을 분할된 테이블로부터 조회하기 위해서는 테이블 간에 논리적인 연관관계가 필요하고, 그런 관계성을 통해서 다양한 데이터들을 출력할 수 있는 것이다. 그리고, 이런 논리적인 관계를 구체적으로 표현하는 것이 바로 SQL 문장의 JOIN 조건인 것이다.
관계형 데이터베이스의 큰 장점이면서, SQL 튜닝의 중요 대상이 되는 JOIN을 잘못 기술하게 되면 시스템 자원 부족이나 과다한 응답시간 지연을 발생시키는 중요 원인이 되므로 JOIN 조건은 신중하게 작성해야 한다.
1. Equal Join이란?
두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미. 예) user_id 필드를 기준으로 users 테이블과 checkin 테이블을 연결해서 한 눈에 보고 싶다.
#JOIN 사용해보기
#'오늘의 다짐' 이벤트 당첨자를 선정하여 스타벅스 기프티콘을 지급해야 해요.
select * from users;
select * from checkins;
#두개의 테이블이 합쳐져야 한다. user_id란 부분이 공통부분이 있다.
각 테이블을 조회해보면 다음과 같다.
join을 통해서 두 테이블을 합친 테이블을 조회해본다.
select * from users u
join checkins c
on u.user_id =c.user_id ;
users 테이블 뒤에 checkins 테이블이 합쳐져서 나타나는 것을 확인 할 수 있다.
Equal Join은 또 Left Join, Inner Join 등으로 구분된다 이중 Left Join은 말그대로 합치는 기준을 왼쪽에 먼저 기술한 테이블을 기준으로 하는 것이다. 대상이 되는 테이블에 값이 없으면 Null 값 등 빈 필드로 생성 될 수도 있다.
#Left Join
select * from users u
left join point_users pu
on u.user_id = pu.user_id;
위 부분은 Equal Join 중 Left Join으로 다음과 같은 그림으로 나타낼 수도 있다.
Inner Join은 쉽게 두 테이블의 교집합으로 보면 된다. 둘 다 값이 모두 있는 경우만 나타난다.
#Inner Join
select * from users u
inner join point_users p
on u.user_id = p.user_id;
SQL을 보면, SELECT 구문에 단순히 칼럼명이 오지 않고, "테이블명.칼럼명" 처럼 테이블명과 칼럼명이 같이 나타난다. 이렇게 특정 칼럼에 접근하기 위해 그 칼럼이 어느 테이블에 존재하는 칼럼인지를 명시하는 것은 두 가지 이유가 있다.
1. 모든 테이블에 칼럼들이 유일한 이름을 가지면 괜찮지만, 중복되게 이름을 가질 경우 DBMS의 옵티마이저는 어떤 칼럼을 선택해야할지 모르기 때문에 파싱단계에서 에러가 발생한다. 2. 개발자나 사용자가 조회할 데이터가 어느 테이블에 있는 컬럼을 말하는 것인지 쉽게 알 수 있게 하므로 SQL 에 대한 가독성이나 유지보수성을 높이는 효과가 있다.
2. Non-Equal Join
Non EQUI(비등가) JOIN은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다. 비동등조인의 경우 '=' 연산자가 아닌 다른 (Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행하는 것이다. 두 개의 테이블이 PK-FK로 연관 관계를 가지거나 논리적으로 같은 값이 존재하는 경우에는 '=' 연산자를 이용하여 EQUI JOIN을 사용한다. 그러나 값이 정확하게 일치하지 않는 경우에는 Non-Equal 조인을 사용할 수 없다. 이런 경우 비동등 조인을 시도할 수 있으나 데이터 모델에 따라 비동등조인이 불가능한 경우도 있다.
3. 3개 이상 테이블의 Join
예시
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션,
T.REGION_NAME 연고지, T.TEAM_NAME 팀명,
S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
AND T.STADIUM_ID = S.STADIUM_DI
ORDER BY 선수명;
#'오늘의 다짐' 정보에checkins/ 과목 정보를courses/ 연결course_id해/
#과목별title/ '오늘의 다짐'comment 갯수count를 세어보자!
select * from checkins;
select * FROM courses;
select title, count(comment) from checkins ck
Left Join courses cs
on ck.course_id = cs.course_id
group by title;
#많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
#많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
select * from point_users;
select * from users;
#user_id 겹침 기준
select u.name , pu.`point` from users u
Inner Join point_users pu
on u.user_id = pu.user_id
order by pu.point desc;
네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
#네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
select * from orders;
select * from users;
select u.name, count(u.name) from orders o
Inner Join users u
on o.user_id = u.user_id
group by u.name
#결제 수단payment_method/별 유저 포인트의 평균값 구해보기
select * from orders;
select * from point_users;
#user_id 기준
select payment_method ,round(avg(pu.`point`),1) from orders o
Inner Join point_users pu
on o.user_id = pu.user_id
group by o.payment_method
order by avg(`point`) desc;
결제하고 시작하지 않은 유저들을 성씨별로 세어보기
#결제하고 시작하지 않은 유저들을 성씨별로 세어보기
select * from users;
select * from enrolleds; #is_registered가 1은 등록, 0은 아님
select u.name, COUNT(u.name) from users u
Inner Join enrolleds e
on u.user_id = e.user_id
where e.is_registered = '0'
group by u.name;
#과목 별로 시작하지 않은 유저들을 세어보기
#과목 별로 시작하지 않은 유저들을 세어보기
select * from courses;
select * from enrolleds;
select c.title ,count(e.is_registered) from courses c
Inner Join enrolleds e
on c.course_id = e.course_id
where e.is_registered = 0
group by c.title;
웹개발, 앱개발 종합반의 week 별 체크인 수
#웹개발, 앱개발 종합반의 week 별 체크인 수
select * from courses;
select * from checkins;
select c.title, ck.week ,count(c.title) from courses c
Inner Join checkins ck
on c.course_id = ck.course_id
group by c.title, ck.week
order by c.title, ck.week;
조금 햇갈렸다.
위 문제에서, 8월 1일 이후에 구매한 고객 2020-08-01은 ' '로 감싸야 한다. 안감싸도 에러가 안뜨길레 실수할뻔했다.
#위 문제에서, 8월 1일 이후에 구매한 고객
select * from courses;
select * from checkins;
select * from orders;
select c.title, ck.week ,count(c.title) from courses c
Inner Join checkins ck
on c.course_id = ck.course_id
Inner Join orders o
on ck.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c.title, ck.week
order by c.title, ck.week;
7월10일 ~ 7월19일에 가입한 고객 중,#포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율
#7월10일 ~ 7월19일에 가입한 고객 중,
#포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율
select * from users;
select * from point_users;
select count(*) as TotalMember,
count(point_user_id) as PointUser,
round((count(pu.`point`)/count(*)),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';
enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, # 완료한 강의 수가 많은 순서대로 정렬해보기.
#enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고,
# 완료한 강의 수가 많은 순서대로 정렬해보기.
#user_id도 같이 출력되어야 한다.
select * from enrolleds;
select * from enrolleds_detail; #ed.done <-수강완료된것
select * from users; #e.user_id
#enrolled_id 로 조인
select e.enrolled_id , e.user_id, COUNT(*) as cnt 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 cnt desc;
4 Union 이란?
7월, 8월 각각 select 쿼리문을 작성 했다.
##UNION 사용해보기
##다음 처럼 두개의 정보를 추려 냈다. 기간이 상이하다.
##### 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'
group by c1.title, c2.week
order by c1.title, c2.week;
##### 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 c1.title, c2.week;
각각 실행하면 다음 처럼 데이터를 추출한다.
하지만 select 구문 2개를 임시로 하나로 봐야 하는 경우, 합치기 위해서는 Union을 사용 할 수 있다.
간단하게 (첫번쨰 구문) union all(두번째 구문)으로 진행 할 수 있다. 아래 구문을 실행해보면 7월 다음 8월 테이블이 그대로 들어가게 된다.
#이것을 7, 8월로 연속된 테이블로 나타내고 싶다.
#(첫 sql문)union all(두번째 sql문)
(
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과 Union All의 차이 Union 은 중복 데이터가 출력 되지 않는다. 유실 될 가능성이 있다. Union All은 중복 데이터도 필요한 케이스에 사용하면 된다. 예로 사람 이름으로 합치는 경우 동명이인의 정보가 남아야 되는 경우 무조건 Union All을 사용해야 하고 Union을 사용하면 중복된 데이터가 사라질 수 있다. 2개 이상 테이블에 존재하는 같은 성격의 값을 하나의 쿼리문으로 추출
