::: IT인터넷 :::

PostgreSQL의 JOIN 사용하기

곰탱이푸우 2022. 5. 19. 08:20
오픈소스 데이터베이스인 PostgreSQL의 JOIN 사용법에 대해 정리한다.
 
PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.
PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.
GitHub과 DockerHub 사이트는 다음과 같다.
 
PostgreSQL은 다음과 같은 구조를 가지고 있다.
각 기능은 다음과 같다.
  • Users/Groups - 사용자 정보 관리
  • Databases - 실제 레코드가 저장되는 Database 관리
  • Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
  • Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
  • Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
  • Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간
 
 

JOIN 사용하기

JOIN 개념

테이블이 두 개 이상인 경우 테이블을 합치거나 정보를 조합해야 할 때 사용하는 방법이다.
 
JOIN 자체가 테이블의 전체 데이터를 사용하는 연산이므로 메모리와 연산 부담이 큰 편이다. 따라서 JOIN 전략에 대한 이해가 필요하다.
 
다양한 JOIN 기법과 전략을 이해하고 적절한 방법을 사용하면 다음과 같은 이점이 있다.
  • Out of Memory 방지
  • 데이터 처리 시간 단축
  • 사용하는 자원 JO최소화
 
JOIN을 위해서는 다음 내용이 정의 되어야 한다.
  • 2개의 테이블 (left, right)
  • 값을 비교하기 위한 1개 또는 그 이상의 컬럼 (2개의 테이블에 공통 존재)
  • 원하는 결과를 도출하기 위한 JOIN Expression
  • JOIN Type (방법)
 
Join 결과로 새로운 테이블이 생성된다.
  • 기준 컬럼을 기준으로 원본과 대상 테이블의 컬럼과 레코드들이 하나의 테이블에 모두 표시된다.
  • JOIN 기법에 따라 한쪽에만 컬럼이 존재하는 경우, 없는 레코드는 null로 표시된다.
 

JOIN 기법

JOIN 기법을 간단하게 표현하면 다음과 같다.
 
Join 기법은 크게 다음과 같이 구분한다.
구분
내용
비고
Inner
2개의 테이블에서 Key 컬럼의 레코드가 일치
양쪽 테이블의 Column 모두 포함
(Full) Outer
2개의 테이블에서
Key 컬럼의 레코드가 일치하거나한쪽에만 존재
한쪽에만 존재하는 컬럼의 레코드는 null로 표시
Left Outer
왼쪽 테이블을 기준으로
Key Column의 레코드가 오른쪽 테이블에 존재
오른쪽 테이블에만 존재하는 레코드는 제외
오른쪽 테이블에 없는 레코드는 null로 표시
Right Outer
Left Outer외 동일하며 방향만 반대
왼쪽 테이블에 없는 레코드는 null로 표시
Cross
(or Cartesian)
양쪽 테이블의 모든 Row 매치
(데이터 양의 explosion 발생)
1,000 Row * 500 Row = 500,000 Row
(가급적 사용 지양)
 
 

예제 데이터 생성하기

JOIN을 확인하기 위한 테이블을 두 개 생성한다.
# 첫번째 테이블
postgres=# CREATE TABLE team (
    num integer PRIMARY KEY,
    name varchar(25)
);

# 두번째 테이블
postgres=# CREATE TABLE team_code (
    num integer PRIMARY KEY,
    value varchar(5)
);
 
생성한 테이블에 값을 입력한다.
# 첫번째 테이블
postgres=# INSERT INTO team (num, name) VALUES
    (1, 'Human Resource'),
    (2, 'Finance'),
    (3, 'Infra Service');

num  | name      // team 테이블
-----+------
  1  | Human Resource
  2  | Finance
  3  | Infra Service

postgres=# INSERT INTO team_code (num, value) VALUES
    (1, 'HR'),
    (2, 'FIN'),
    (5, 'DEV');

num  | value      // team_code 테이블
-----+------
  1  | HR
  2  | FIN
  5  | DEV

 

 

INNER JOIN

2개의 테이블에서 Key 컬럼의 레코드가 일치하는 결과를 반환한다.
양 쪽 테이블의 컬럼이 모두 포함된다.
postgres=# SELECT * FROM team
  INNER JOIN team_code 
  ON team.num = team_code.num;

# 결과는 다음과 같다.
  num | name               | num  | value
------+--------------------+------+-------
    1 | Human Resource     |    1 | HR
    2 | Finance            |    2 | FIN
(2 rows)
 
JOIN 구문 다음에 있는 테이블 명에 SELECT 쿼리를 사용해서 사용할 컬럼만 선택할 수 있다.
postgres=# SELECT * FROM team
  INNER JOIN (SELECT num, value FROM team_code) AS code
  ON team.num = code.num;

# 결과는 동일하다.
  num | name               | num  | value
------+--------------------+------+-------
    1 | Human Resource     |    1 | HR
    2 | Finance            |    2 | FIN
(2 rows)
 

LEFT OUTER JOIN

왼쪽 테이블을 기준으로 Key Column의 레코드가 오른쪽 테이블에 존재하는 결과만 반환한다.
오른쪽 테이블에만 존재하는 레코드는 제외된다.
postgres=# SELECT * FROM team 
  LEFT JOIN team_code 
  USING (num);

# 결과는 다음과 같다.
  num | name               |  value 
------+--------------------+---------+------- 
    1 | Human Resource     |  HR 
    2 | Finance            |  FIN
    3 | Infra Service      |  null
(3 rows)
 
JOIN 구문 다음에 있는 테이블 명에 SELECT 쿼리를 사용해서 사용할 컬럼만 선택할 수 있다.
postgres=# SELECT * FROM team
  LEFT JOIN (SELECT num, value FROM team_code) AS code
  USING (num);

# 결과는 동일하다.
  num | name               |  value 
------+--------------------+---------+------- 
    1 | Human Resource     |  HR 
    2 | Finance            |  FIN 
    3 | Infra Service      |  null
(3 rows)
 
 

RIGHT OUTER JOIN

오른쪽 테이블을 기준으로 Key Column의 레코드가 왼쪽 테이블에 존재하는 결과만 반환한다.
왼쪽 테이블에만 존재하는 레코드는 제외된다.
postgres=# SELECT * FROM team 
  RIGHT JOIN team_code 
  USING (num);

# 결과는 다음과 같다.
  num | name               | value 
------+--------------------+---------+------- 
    1 | Human Resource     |  HR 
    2 | Finance            |  FIN
    5 | null               |  DEV
(3 rows)
 
JOIN 구문 다음에 있는 테이블 명에 SELECT 쿼리를 사용해서 사용할 컬럼만 선택할 수 있다.
postgres=# SELECT * FROM team
  RIGHT JOIN (SELECT num, value FROM team_code) AS code
  USING (num);

# 결과는 동일하다.
  num | name               | value 
------+--------------------+------+------- 
    1 | Human Resource     |  HR 
    2 | Finance            |  FIN 
    5 | null               |  DEV
(3 rows)
 
 

FULL OUTER JOIN

2개의 테이블에서 Key 컬럼의 레코드가 일치하거나 한쪽에만 존재하는 결과를 포함하여 반환한다.
한쪽에만 존재하는 컬럼의 레코드는 null로 표시된다.
postgres=# SELECT * FROM team 
  FULL OUTER JOIN team_code 
  ON team.num = team_code.num;

# 결과는 다음과 같다.
  num | name               | num  | value 
------+--------------------+------+------- 
    1 | Human Resource     |    1 | HR 
    2 | Finance            |    2 | FIN
 null | null               |    5 | DEV
    3 | Infra Service      | null | null
(4 rows)
 
JOIN 구문 다음에 있는 테이블 명에 SELECT 쿼리를 사용해서 사용할 컬럼만 선택할 수 있다.
postgres=# SELECT * FROM team
  FULL OUTER JOIN (SELECT num, value FROM team_code) AS code
  ON team.num = code.num;

# 결과는 동일하다.
  num | name               | num  | value 
------+--------------------+------+------- 
    1 | Human Resource     |    1 | HR 
    2 | Finance            |    2 | FIN 
 null | null               |    5 | DEV
    3 | Infra Service      | null | null
(4 rows)

 

 

CROSS JOIN

양쪽 테이블의 모든 레코드를 매치한 결과를 반환한다.
데이터 양의 explosion 발생하기 때문에 가급적 사용을 지양한다.
 
예를 들어 한쪽 테이블에 1,000 Row, 다른 쪽에 500 Row가 있다고 가정한다.
CROSS JOIN으로 생성되는 총 레코드는 1000 Row * 500 Row = 500,000 Row가 된다.
postgres=# SELECT * FROM team 
  CROSS JOIN team_code; 

# 결과는 다음과 같다.
  num |      name      | num | value
------+----------------+-----+-------
    1 | Human Resource |  1  | HR
    2 | Finance        |  1  | HR
    3 | Infra Service  |  1  | HR
    1 | Human Resource |  2  | FIN
    2 | Finance        |  2  | FIN
    3 | Infra Service  |  2  | FIN
    1 | Human Resource |  5  | DEV
    2 | Finance        |  5  | DEV
    3 | Infra Service  |  5  | DEV
(9 rows)
 
JOIN 구문 다음에 있는 테이블 명에 SELECT 쿼리를 사용해서 사용할 컬럼만 선택할 수 있다.
postgres=# SELECT * FROM team
  FULL OUTER JOIN (SELECT num, value FROM team_code) AS code;

# 결과는 동일하다.
  num |      name      | num | value 
------+----------------+-----+------- 
    1 | Human Resource |  1  | HR 
    2 | Finance        |  1  | HR 
    3 | Infra Service  |  1  | HR 
    1 | Human Resource |  2  | FIN 
    2 | Finance        |  2  | FIN 
    3 | Infra Service  |  2  | FIN 
    1 | Human Resource |  5  | DEV 
    2 | Finance        |  5  | DEV 
    3 | Infra Service  |  5  | DEV 
(9 rows)