[PostgreSQL, Greenplum] JOIN 으로 여러개의 테이블, 뷰 연결하기
Greenplum and PostgreSQL Database 2021. 5. 30. 21:47이번 포스팅에서는 PostgreSQL, Greenplum Database에서 여러개의 테이블을 Key 값을 기준으로 JOIN 구문을 사용하여 연결하는 다양한 방법을 소개하겠습니다. 그리고 두 테이블 내 관측치 간의 모든 가능한 조합을 반환해주는 CROSS JOIN 에 대해서도 마지막에 소개하겠습니다. (DB 종류에 상관없이 join SQL query는 거의 비슷합니다.)
(1) INNER JOIN
(2) LEFT JOIN
(3) RIGHT JOIN
(4) FULL JOIN
(5) 3개 이상 복수개의 테이블을 JOIN 으로 연결하기
(6) CROSS JOIN
먼저 예제로 사용한 간단한 2개의 테이블을 만들어보겠습니다. 두 테이블을 연결할 수 있는 공통의 Key값으로서 'id'라는 이름의 칼럼을 두 테이블이 모두 가지고 있습니다.
'tbl1' 과 'tbl2'는 Key 'id'를 기준으로 id = [2, 3, 4] 가 서로 동일하게 존재하며, 'tbl1'의 id = [1]은 'tbl1'에만 존재하고, 'tbl2'의 id = [5] 는 'tbl2'에만 존재합니다. 각 JOIN 방법 별로 결과가 어떻게 달라지는지 유심히 살펴보시기 바랍니다.
-- Creating two sample tables
-- sample table 1
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
id int
, x text
) DISTRIBUTED RANDOMLY;
INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');
SELECT * FROM tbl1 ORDER BY id;
--id x
--1 a
--2 b
--3 c
--4 d
-- sample table 2
DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
id int
, y text
) DISTRIBUTED RANDOMLY;
INSERT INTO tbl2 VALUES (2, 'e'), (3, 'f'), (4, 'g'), (5, 'h');
SELECT * FROM tbl2 ORDER BY id;
--id y
--2 e
--3 f
--4 g
--5 h
(1) INNER JOIN
INNER JOIN 은 두 테이블의 Key 값을 기준으로 교집합에 해당하는 값들만 반환합니다. 두 테이블에서 Key 값이 겹치지 않는 값들은 제거되었습니다.
--------------
-- INNER JOIN
--------------
SELECT a.id, a.x, b.y
FROM tbl1 AS a
INNER JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--2 b e
--3 c f
--4 d g
(2) LEFT OUTER JOIN
LEFT OUTER JOIN 은 왼쪽 테이블을 기준으로 Key값이 서로 같은 오른쪽 테이블의 값들을 왼쪽 테이블에 연결해줍니다. 아래의 예에서는 왼쪽의 'tbl1'의 값들은 100% 모두 있고, LEFT OUTER JOIN 으로 연결해준 오른쪽 'tbl2' 테이블의 경우 id = [5] 의 값이 제거된 채 id = [2, 3, 4] 에 해당하는 값들만 'tbl1'과 연결이 되었습니다. 그리고 왼쪽 'tbl1'에는 있지만 오른쪽 'tbl2'에는 없는 id = [1] 에 해당하는 값의 경우 y = [NULL] 값을 반환하였습니다.
-------------------
-- LEFT OUTER JOIN
-------------------
SELECT a.id, x, y
FROM tbl1 AS a
LEFT OUTER JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--1 a [NULL]
--2 b e
--3 c f
--4 d g
(3) RIGHT OUTER JOIN
RIGHT OUTER JOIN 은 LEFT OUTER JOIN 과 정반대라고 생각하면 이해하기 쉽습니다. 이번에는 오른쪽 테이블을 기준으로 Key 값이 같은 왼쪽 테이블의 값을 오른쪽 테이블에 연결해줍니다.
아래 RIGHT OUTER JOIN 예에서는 오른쪽 테이블은 'tbl2'는 100% 모두 있고, 왼쪽 테이블 'tbl1'의 경우 'tbl2'와 Key 값이 동일한 id = [2, 3, 4] 에 해당하는 값들만 'tbl2'에 연결이 되었습니다. 'tbl2'에만 존재하고 'tbl1'에는 없는 id = [5] 의 경우 'tbl1'의 'x' 칼럼 값은 [NULL] 값이 됩니다.
--------------------
-- RIGHT OUTER JOIN
--------------------
SELECT a.id, x, y
FROM tbl1 AS a
RIGHT OUTER JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--2 b e
--3 c f
--4 d g
--5 [NULL] h
(4) FULL JOIN
FULL JOIN은 양쪽 테이블 모두를 기준으로 Key 값이 같은 값들을 연결시켜 줍니다. 이때 한쪽 테이블에만 Key 값이 존재할 경우 다른쪽 테이블의 칼럼 값에는 [NULL] 값을 반환합니다.
제일 위에 있는 도식화 그림을 참고하시면 위의 INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN에 대해서 좀더 이해하기 쉬울 거예요.
---------------
-- FULL JOIN
---------------
SELECT a.id, x, y
FROM tbl1 AS a
FULL JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--1 a [NULL]
--2 b e
--3 c f
--4 d g
--5 [NULL] h
(5) 3개 이상의 복수개의 테이블을 JOIN 으로 연결하기
위의 (1)~(4) 는 2개의 테이블을 Key 값을 기준으로 JOIN 문으로 연결한 것이었습니다. 만약 3개 이상의 복수개의 테이블을 JOIN 으로 연결하고자 한다면 아래의 예처럼 JOIN 문과 연결 Key 값을 ON 으로 이어서 써주면 됩니다.
아래의 예는 'tbl1' 테이블을 기준으로 'tbl2', 'tbl3'를 'id' Key를 기준으로 LEFT OUTER JOIN 한 것입니다.
--------------------------------------------------------
-- LEFT OUTER JOIN with Multiple Tables
--------------------------------------------------------
-- creating the 3rd table
DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3 (
id int
, z text
) DISTRIBUTED RANDOMLY;
INSERT INTO tbl2 VALUES (2, 'i'), (4, 'j'), (6, 'k'), (8, 'l');
SELECT * FROM tbl3 ORDER BY id;
--id z
--2 i
--4 j
--6 k
--7 l
-- LEFT OUTER JOIN with 3 tables
SELECT a.id, x, y
FROM tbl1 AS a
LEFT OUTER JOIN tbl2 AS b
ON a.id = b.id
LEFT OUTER JOIN tbl3 AS c
ON a.id = c.id
ORDER BY a.id;
--id x y z
--1 a [NULL] [NULL]
--2 b e i
--3 c f [NULL]
--4 d g j
(6) CROSS JOIN
위의 (1)~(5)까지의 JOIN은 두 테이블에 동일하게 존재하는 Key값을 기준으로 두 테이블을 연결하여 주었다면, 이제 CROSS JOIN 은 두 테이블의 모든 값들 간의 조합을 반환하며, 이때 Key 값은 필요없습니다. 가령 왼쪽 테이블에 m 개의 행이 있고, 오른쪽 테이블에 n 개의 행이 있다면 두 테이블의 CROSS JOIN은 m * n 개의 조합(combination)을 반환합니다.
실수로 행의 개수가 엄청나게 많은 두 테이블을 CROSS JOIN 하게 될 경우 시간도 오래 걸리고 자칫 memory full 나서 DB가 다운되는 경우도 있습니다. 따라서 CROSS JOIN 을 할 때는 지금 하려는 작업이 CROSS JOIN 요건이 맞는 것인지 꼭 한번 더 확인이 필요하며, 소요 시간이나 메모리가 여력이 되는지에 대해서도 먼저 가늠해볼 필요가 있습니다.
----------------
-- CROSS JOIN
----------------
SELECT a.id AS id_a, a.x, b.id AS id_b, b.y
FROM tbl1 AS a
CROSS JOIN tbl2 AS b
ORDER BY a.id, b.id;
--id_a x id_b y
--1 a 2 e
--1 a 3 f
--1 a 4 g
--1 a 5 h
--2 b 2 e
--2 b 3 f
--2 b 4 g
--2 b 5 h
--3 c 2 e
--3 c 3 f
--3 c 4 g
--3 c 5 h
--4 d 2 e
--4 d 3 f
--4 d 4 g
--4 d 5 h
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
