Redshift - JOIN

 

JOIN

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

 

 

SELECT A JOIN B ON ~

 

왼쪽 테이블을 LEFT, 오른쪽 테이블을 RIGHT이라고 가정하면 이해하기 쉽습니다.

위에서는 A가 LEFT, B가 RIGHT 테이블입니다.

 

95%의 경우는 LEFT JOIN과 RIGHT JOIN을 사용합니다. 5%는 FULL JOIN을 사용하고 RIGHT JOIN은 LEFT JOIN에서 테이블들의 순서만 바꾸면 그만이므로 전혀 사용할 일이 없다고 보셔도 좋습니다.

 

 

 

 

다양한 종류의 조인 (The Art of PostgreSQL - What is an SQL JOIN?)

 

CROSS JOIN은 가능한 모든 조합의 수를 테이블로 나타냅니다. 한 테이블의 레코드 수가 3, 다른 테이블의 레코드 수가 3이면 가능한 모든 조합의 수인 9개의 레코드 테이블이 나타납니다.

 

 

 

 

JOIN의 기본 문법

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01';

 

 

JOIN 앞의 ____ 에는 INNER,  FULL, LEFT, RIGHT, CROSS 가 들어가면 됩니다.

 

위와 같이 하나의 필드만이 아닌 두 개의 필드(key1, key2)가 같은 경우만을 JOIN의 조건으로 내걸 수 도 있습니다.

 

 

 

 

 

 

 

JOIN시 고려해야할 점

중요) 반드시 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크해야 합니다.

 

테이블 체크 사항들

https://touchingdata.tistory.com/96

Redshift - CTAS & CTE, 테이블(데이터) 품질 확인 방법

 

 

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

One to one은 조인되는 키가 조인된 두 테이블에서 한번씩만 나오는 경우

완전한 one to one혹은 한쪽이 부분집합인 one to one인 경우가 있습니다.

 

One to many(Many to one) 조인되는 키가 조인된 두 테이블에서 한쪽의 테이블에서만 여러번 나타나는 경우

one to many의 경우 한 테이블에 중복이 있는 상태에서 JOIN을 하면 중복이 있는 수 만큼 레코드들이 반복되어서 출력됩니다. 만일 매출과 같은 테이블이라면 매출이 실제보다 부풀려진 것과 같이 위험한 상황이 발생하며 실제로도 흔히 발생하는 상황이므로 주의하셔야 합니다.

 

Many to many는 경우가 많지 않으며 대개 위험한 상황이므로 가능한 한 one to one이나 one to many로 바꾸는 것을 권장합니다.

 

 

 

 

 

 

 

 

JOIN 실습에 사용할 테이블 두 개

 

 

VitalID를 이용해 JOIN을 합니다.

MissingVital은 유저가 체중을 이틀 이상 측정하지 않는 경우 발생하는 Alert 타입입니다.

 

 

 

Vital 테이블 스키마

DROP TABLE IF EXISTS raw_data.vital;
CREATE TABLE raw_data.vital (
    UserID int,
    VitalID	int,
    Date date,
    Weight int
);
INSERT INTO raw_data.vital VALUES
(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 테이블 스키마

DROP TABLE IF EXISTS raw_data.alert;
CREATE TABLE raw_data.alert (
    AlertID int,
    VitalID	int,
    AlertType varchar(32),
    Date date,
    UserID int
);
INSERT INTO raw_data.alert VALUES
 (1,	4, 'WeightIncrease', '2020-01-01', 101),
 (2, NULL, 'MissingVital', '2020-01-04', 100),
 (3, NULL, 'MissingVital', '2020-01-04', 101);

 

 

 

 

 

 

INNER JOIN

 

1. 양쪽 테이블에서 매치가 되는 레코드들만 리턴함
2. 양쪽 테이블의 필드가 모두 채워진 상태로 리턴됨

 

SELECT * FROM raw_data.Vital v
JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

 

 

 

 

 

LEFT JOIN

 

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

SELECT * FROM raw_data.Vital v
LEFT JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

 

결과에서는 구글 코랩에서 파이썬코드로 실행했기 때문에 NULL 대신 None이 나왔습니다.

 

 

 

 

 

FULL JOIN

 

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

 

SELECT * FROM raw_data.Vital v
FULL JOIN raw_data.Alert a ON v.vitalID = a.vitalID;

 

 

 

 

 

 

CROSS JOIN (CARTESIAN JOIN)

 

1. 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함

 

SELECT * FROM raw_data.Vital v
CROSS JOIN raw_data.Alert a;

 

 

 

 

 

 

SELF JOIN

 

1. 동일한 테이블을 alias를 달리해서 자기 자신과 조인함

 

SELECT * FROM raw_data.Vital v1
JOIN raw_data.Vital v2 ON v1.vitalID = v2.vitalID;