PostgreSQL Tutorial 03. Joining Multiple Tables

jwKim·2023년 2월 13일
0

🎯 PostgreSQL Tutorial

목록 보기
3/9

01. Joins

01-01. 개요 및 샘플 데이터 설정

  • Join은 관련 테이블 간의 공통 열 값을 기반으로 하나 이상의 테이블에서 열을 결합하는 데 사용됨
  • 공통 열은 일반적으로 첫 번째 테이블의 primary key 열과 두 번째 테이블의 foreign key 열임


-- [ 샘플 테이블 설정 ]
CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

01-02. inner join

  • fruit_a와 fruit_b 열의 값을 일치시켜 첫 번째 테이블(basket_a)을 두 번째 테이블(basket_b)과 조인
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

  1. inner join은 첫 번째 테이블(basket_a)의 각 행을 검사한다.
  2. fruit_a 열의 값과 두 번째 테이블(basket_b)의 fruit_b 열의 값을 각 행 별로 비교한다.
  3. 값이 같으면 inner join은 두 테이블의 열을 포함하는 새 행을 만들고 이 새 행의 결과 집합에 추가한다.

inner join을 벤다이어그램으로 표현하면 다음과 같다.

01-03.left join

  • 첫 번째 테이블(basket_a)을 왼쪽 테이블이라고 하고 두 번째 테이블(basket_b)을 오른쪽 테이블이라고 한다.
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
   ON fruit_a = fruit_b;

  1. left join은 왼쪽 테이블에서 데이터 선택을 시작한다. (fruit_a 열의 값을 오른쪽 테이블의 fruit_b 열 값과 비교)
  2. 이 값이 같으면 left join은 두 테이블의 열을 포함하는 새 행을 만들고 이 새 행을 결과 집합에 추가한다.
  3. 값이 같지 않을 경우에도, 두 테이블의 열을 포함하는 새 행을 만들고 결과 집합에 추가한다. 그러나 오른쪽 테이블의 열은 null로 채운다.

left join을 벤다이어그램으로 표현하면 다음과 같다.



  • 오른쪽 테이블에 일치하는 행이 없는 왼쪽 테이블의 행을 선택하려면 where 절과 함께 left join을 사용
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
    ON fruit_a = fruit_b
WHERE b IS NULL;

오른쪽 테이블에 일치하는 행이 없는 왼쪽 테이블의 행을 반환하는 left join을 벤다이어그램으로 표현하면 다음과 같다.

01-04. right join

  • right join은 left join의 반대 버전
  1. right join은 오른쪽 테이블에서 데이터를 선택 (오른쪽 테이블에 있는 fruit_b 열의 모든 행 각각의 값을 왼쪽 테이블에 있는 fruit_a 열의 모든 행의 값들과 비교)
  2. 값이 같으면 두 테이블의 열을 포함하는 새 행을 생성
  3. 값이 같지 않은 경우에도 두 테이블의 열을 포함하는 새 행을 만듦. 하지만 왼쪽 테이블의 열을 NULL로 채움
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;

right join을 벤다이어그램으로 표현하면 다음과 같다.

  • left join과 마찬가지로 where 절을 이용하여 왼쪽 테이블에서 일치하는 행이 없는 오른쪽 테이블의 행을 가져올 수 있음
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL;

왼쪽 테이블에 일치하는 행이 없는 오른쪽 테이블의 행을 반환하는 right join을 벤다이어그램으로 표현하면 다음과 같다.

01-05.full outer join

  • full outer join (full join)은 왼쪽 및 오른쪽 테이블의 모든 행을 포함하는 결과 집합을 반환 (일치하는 행이 있는 경우 매칭)
  • 일치하는 항목이 없는 경우 테이블의 열은 NULL로 채워짐
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;

full outer join을 벤다이어그램으로 표현하면 다음과 같다.

  • 다른 테이블에 일치하는 행이 없는 테이블의 행을 반환하려면 where 절과 함께 full outer join을 사용
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;

다른 테이블에 일치하는 행이 없는 테이블의 행을 반환하는 full outer join을 벤다이어그램으로 표현하면 다음과 같다.

02. Table aliases

Table alias는 쿼리를 실행하는 동안 테이블에 새 이름을 임시로 할당

table_name AS alias_name;
  • table_name 이 alias_name 이라는 별칭으로 할당됨
  • column alias와 유사하게 AS 는 선택사항 (써도 되고, 안 써도 됨)

02-01. Table alias의 사용

1) 쿼리를 더 읽기 쉽게 만들기 위해 긴 테이블 이름에 Table alias 사용

긴 테이블 이름으로 열 이름을 부여해야 하는 경우 Table alias를 이용하여 쿼리를 더 읽기 쉽게 만들 수 있음

Table alias 사용 X :

a_very_long_table_name.column_name

Table alias 사용 : 

a_very_long_table_name AS alias

alias.column_name

2) join 절에서 Table ailas 사용

  • join 절을 사용하여 동일한 열 이름을 가진 여러 테이블의 데이터를 쿼리
  • 여러 테이블의 동일한 열 이름을 지정하지 않고 사용하면 오류가 발생
  • 다음 문법을 사용하여 열의 이름을 부여하면 오류 방지
table_name.column_name

**FROM 절과 INNER JOIN 절에 나열된 테이블 이름에 대해 Table alias를 사용하여 쿼리를 짧게 만듦 :**

SELECT
	c.customer_id,
	first_name,
	amount,
	payment_date
FROM
	customer c
INNER JOIN payment p 
    ON p.customer_id = c.customer_id
ORDER BY 
   payment_date DESC;

3) self-join 시 Table alias 사용

테이블을 자체에 조인할 때 위와 마찬가지로 쿼리 내에서 동일한 테이블을 여러 번 참조하기 때문에 Table alias 사용 (사용하지 않으면 오류 발생)

**employee 테이블에 alias을 부여하여 동일한 쿼리에서 테이블을 두 번 참조 :**

SELECT
    e.first_name employee,
    m .first_name manager
FROM
    employee e
INNER JOIN employee m 
    ON m.employee_id = e.manager_id
ORDER BY manager;

03. Self-join

03-01. 개요

self-join은 자기 자신을 조인한다. 일반적으로 self-join을 사용하여 계층적 데이터를 쿼리하거나 동일한 테이블 내의 행을 비교한다.

self-join을 하려면 동일한 테이블을 다른 table alias로 두 번 지정하고, ON 뒤에 조인 조건자를 제공한다.

03-02. self-join 예제

1) 계층적 데이터 쿼리 예제

다음과 같은 조직 구조가 있다고 가정한다.(직원들 정보는 Employee 테이블에 저장)

https://www.postgresqltutorial.com/wp-content/uploads/2018/03/PostgreSQL-Self-Join-Reporting-Structure.png

employee 테이블에서, manager_id 열은 employee_id 열을 참조한다. manager_id 열의 값은 직원이 직접 보고하는 관리자를 나타낸다. manager_id 열의 값이 null인 경우, 해당 직원은 누구에게도 보고하지 않는다. (최고 관리자라는 뜻)

다음 쿼리는 self-join을 사용하여 누구에게 보고하는지 찾는다.

SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
INNER JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

https://www.postgresqltutorial.com/wp-content/uploads/2020/07/PostgreSQL-Self-Join-query-hierarchical-data.png

이 쿼리는  employees 테이블을 직원과 관리자로 두 번 참조한다. 직원의 경우 테이블 별칭 e를 사용하고 관리자의 경우 m을 사용한다.

employee_id 열과 manager_id 열의 값을 일치시켜 직원/매니저 쌍을 찾는데, 최상위 관리자는 출력에 나타나지 않는다. 최상위 관리자도 출력하기 원한다면 inner join을 left join으로 바꿔보자.

2) 같은 테이블의 행 비교

제목의 길이가 같은 모든 영화 쌍을 찾는 쿼리는 다음과 같다.

SELECT
    f1.title,
    f2.title,
    f1.length
FROM
    film f1
INNER JOIN film f2
    ON f1.film_id <> f2.film_id AND
       f1.length = f2.length;

https://www.postgresqltutorial.com/wp-content/uploads/2020/07/PostgreSQL-Self-Join-compare-rows-within-the-same-table.png

서로 다른 두개의 영화(f1.film_id <> f2.film_id)은 조인을 통해 길이가 같은 두개의 영화(f1.length = f2.length)끼리 매칭된다.

04. Cross Join

04-01. 개요

CROSS JOIN절을 사용하면 둘 이상의 테이블에 있는 행의 데카르트 곱(집합 A와 B를 곱한 집합)을 생성할 수 있다.

LEFT JOIN  또는 INNER JOIN 과 같은 다른 조인 절과 달리 CROSS JOIN 절에는 조인 조건자가 없다.

두 개의 테이블 T1과 T2에 CROSS JOIN 을 수행해야 한다고 가정한다.

T1에 n행이 있고 T2에 m행이 있으면, 출력 결과의 행 개수는n X m이다.

04-02. CROSS JOIN 예제

샘플 데이터인 T1과 T2를 생성한다.

DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);

DROP TABLE IF EXISTS T2;
CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 (label)
VALUES
	('A'),
	('B');

INSERT INTO T2 (score)
VALUES
	(1),
	(2),
	(3);

CROSS JOIN 연산자를 사용하여 테이블 T1을 테이블 T2와 조인하고 결과를 확인해보자.

SELECT *
FROM T1
CROSS JOIN T2;
 label | score
-------+-------
 A     |     1
 B     |     1
 A     |     2
 B     |     2
 A     |     3
 B     |     3
(6 rows)

아래 그림은 CROSS JOIN 을 사용할 때의 연산 과정을 나타낸 것이다.

https://www.postgresqltutorial.com/wp-content/uploads/2016/06/PostgreSQL-CROSS-JOIN-illustration.png

profile
jwKim
이전 포스트

PostgreSQL Tutorial 02. Filtering Data

다음 포스트

PostgreSQL Tutorial 04. Grouping Data

0개의 댓글