18강 SQL이 뭐지? PostgreSQL Self-Join

18강 SQL이 뭐지? PostgreSQL Self-Join   

PostgreSQL

SQL이 뭐지? 18강 시작합니다.

 

이번 강의에서는

▶ PostgreSQL self-join 

에 대해서 알아보겠습니다.

---

Introduction to PostgreSQL self-join   

셀프 조인은 테이블을 자신에게 조인하는 조인입니다. 일반적으로 자체 조인을 사용하여 계층 데이터를 쿼리 하거나 동일한 테이블 내의 행을 비교합니다.

 

자체 조인을 구성하려면 동일한 테이블을 다른 테이블 별칭으로 두 번 지정하고 조인 술어를 ON 키워드 뒤에 제공합니다.

 

다음 쿼리는 테이블을 자체적으로 조인하는 내부 조인을 사용합니다.

SELECT select_list
FROM table_name t1
INNER JOIN table_name t2 ON join_predicate
;   

이 구문에서 table_name은 INNER JOIN 절을 사용하여 자체에 결합됩니다.

또한 LEFT JOIN  또는 RIGHT JOIN  절을 사용하여 테이블을 자신에게 조인할 수 있습니다.

SELECT select_list
FROM table_name t1
LEFT JOIN table_name t2 ON join_predicate
;   

---

PostgreSQL self-join examples   

자체 조인을 사용하는 몇 가지 예를 보겠습니다.

 

1) Querying hierarchical data example  

시연을 위한 샘플 테이블을 준비해 봅시다.

회사가 다음과 같은 조직 구조를 가지고 있다고 가정합니다.

 

직원 테이블을 만들고 일부 샘플 데이터를 테이블에 삽입합니다.

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR (255) NOT NULL,
    last_name VARCHAR (255) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) 
    REFERENCES employee (employee_id) 
    ON DELETE CASCADE
);
INSERT INTO employee (
    employee_id,
    first_name,
    last_name,
    manager_id
)
VALUES
    (1, 'Windy', 'Hays', NULL),
    (2, 'Ava', 'Christensen', 1),
    (3, 'Hassan', 'Conner', 1),
    (4, 'Anna', 'Reeves', 2),
    (5, 'Sau', 'Norman', 2),
    (6, 'Kelsie', 'Hays', 3),
    (7, 'Tory', 'Goff', 3),
    (8, 'Salley', 'Lester', 3)
;

commit

;   

 

이 직원 테이블에서 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

;   

이 쿼리는 직원 테이블을 직원 테이블과 관리자 테이블로 두 번 참조합니다. 직원에게는 테이블 별칭을 e, 관리자에게는 m을 사용합니다.

조인 술어는 employee_id 및 manager_id 열의 값을 일치시켜 직원/관리자 쌍을 찾습니다.

 

최상위 관리자가 출력에 나타나지 않습니다. Windy Hays가 나오지 않습니다.

결과 집합에 최상위 관리자를 포함하려면 다음 쿼리에 표시된 것처럼 INNER JOIN 절 대신 LEFT JOIN 절을 사용합니다.

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

;   

---

2) Comparing the rows with the same table   

DVDRENTAL 데이터베이스에서 다음 film 테이블을 참조하십시오.

다음 쿼리는 길이가 같은 모든 필름 쌍을 찾습니다.

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
;   

조인 술어는 두 개의 서로 다른 필름 ID(f1.film_id <> f2.film_id)에서

같은 길이를 가진(f1.length = f2.length)  건을 찾습니다.

---

요약   

PostgreSQL self-join은 내부 조인 또는 left join, right join을 사용하여 테이블을 자체 조인하는 정규 조인입니다.
자체 조인은 계층 데이터를 쿼리하거나 동일한 테이블 내의 행을 비교하는 데 매우 유용합니다.

---

본 강의는 PostgreSQL Tutorial을 참조합니다. 

PostgreSQL Tutorial - Learn PostgreSQL from Scratch

실습용 테이블에 대해서는 3강을 참조하세요.

3강 SQL이 뭐지? 도구들을 설치해보자. ( PostgreSQL, HeidiSQL )

---

Do it! SQL을 찾아 주셔서 감사합니다. 여러분의 댓글이 큰 힘이 됩니다.