[SQL]JOIN이란? INNER, LEFT, FULL, CROSS JOIN

JOIN이란?

두 개 혹은 그 이상의 테이블을 공통 필드를 가지고 머지하는데 사용된다. 이는 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용된다.

FULL JOIN : 양 쪽 테이블 정보 모두 저장. 

CROSS JOIN: 모든 테이블에 대해서 조인하여 n*m 개의 컬럼을 갖게 됨

 

JOIN 문법

SELECT A.*, B.*
FROM table1 A (INNER, FULL, LEFT, CROSS) JOIN table2 B 
ON A.key1 = B.key2
WHERE A.ts >= '2019-01-01';

 

JOIN시 고려해야할 점

중복 레코드가 없고, PK의 uniqueness가 보장되어야 함

조인하는 테이블간의 관계를 명확하게 정의

- one to one

완전한 one to one: user_session_channel & session_timestamp

한쪽이 부분집합이 되는 one to one: user_session & session_transaction

- one to many : order(주문자체) vs order items (주문한 아이템)-> 중복이 문제가 발생할 수 있음 

- many to one

- many to many : 드문 경우이며 one to one 이나 one to many 로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 나음

 

JOIN 실습

Vital 테이블

userid - vitalid - date - weight

100	1	2020-01-01	75
100	3	2020-01-02	78
101	2	2020-01-01	90
101	4	2020-01-02	95

 

 

Alert 테이블

alertid - vitalid - alerttype - date - userid

1	4	WeightIncrease	2020-01-01	101
2	None	MissingVital	2020-01-04	100
3	None	MissingVital	2020-01-04	101

 

서버에 Vital 테이블 정보가 들어오면 기존 환자의 최종 정보와 현재 환자의 정보 비교 -> 머신러닝으로 문제 있는지 확인

문제가 있다고 확인되면 Alert 경보 발동, 며칠 이상 체중을 재지 않으면 MissingVital 경보 발동 -> 환자에게 메세지 전송 

 

INNER JOIN 실습

양쪽 테이블에서 매치되는 레코드들만 리턴함

%%sql

SELECT * FROM raw_data.Vital v

JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

 

userid vitalid date weight alertid vitalid_1 alerttype date_1userid_1

101

4

2020-01-02

95

1

4

WeightIncrease

2020-01-01

101

 

LEFT JOIN 실습

왼쪽 테이블의 모든 레코드들을 리턴함. 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴됨 

매칭이 안된 것들은 NULL로 채워져서 리턴됨.

%%sql

SELECT * FROM raw_data.Vital v

LEFT JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

userid vitalid date weight alertid vitalid_1 alerttype date_1 userid_1

100	1	2020-01-01	75	None	None	None	None	None
100	3	2020-01-02	78	None	None	None	None	None
101	2	2020-01-01	90	None	None	None	None	None
101	4	2020-01-02	95	1	4	WeightIncrease	2020-01-01	101

 

FULL JOIN

왼쪽, 오른쪽 테이블의 모든 레코드들을 리턴함. 매칭되는 경우에만 양쪽 테이블의 모든 필드들이 채워진 상태로 리턴됨.

%%sql

SELECT * FROM raw_data.Vital v

FULL JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

userid vitalid date weight alertid vitalid_1 alerttype date_1 userid_1

100	1	2020-01-01	75	None	None	None	None	None
100	3	2020-01-02	78	None	None	None	None	None
101	4	2020-01-02	95	1	4	WeightIncrease	2020-01-01	101
None	None	None	None	2	None	MissingVital	2020-01-04	100
None	None	None	None	3	None	MissingVital	2020-01-04	101
101	2	2020-01-01	90	None	None	None	None	None

 

CROSS JOIN

%%sql

SELECT * FROM raw_data.Vital v

CROSS JOIN raw_data.Alert a;

useridvitaliddateweightalertidvitalid_1alerttypedate_1userid_1

100	1	2020-01-01	75	1	4	WeightIncrease	2020-01-01	101
100	3	2020-01-02	78	1	4	WeightIncrease	2020-01-01	101
101	2	2020-01-01	90	1	4	WeightIncrease	2020-01-01	101
101	4	2020-01-02	95	1	4	WeightIncrease	2020-01-01	101
100	1	2020-01-01	75	2	None	MissingVital	2020-01-04	100
100	3	2020-01-02	78	2	None	MissingVital	2020-01-04	100
101	2	2020-01-01	90	2	None	MissingVital	2020-01-04	100
101	4	2020-01-02	95	2	None	MissingVital	2020-01-04	100
100	1	2020-01-01	75	3	None	MissingVital	2020-01-04	101
100	3	2020-01-02	78	3	None	MissingVital	2020-01-04	101
101	2	2020-01-01	90	3	None	MissingVital	2020-01-04	101
101	4	2020-01-02	95	3	None	MissingVital	2020-01-04	101