2021-12-08 12:44 작성

My SQL basic 메모

Table of contents

• SELF JOIN 도입
• 예시
  • 1) INNER JOIN을 사용해 SELF JOIN 하기
  • 2) LEFT JOIN을 사용해 SELF JOIN 하기
  • 3) SELF JOIN을 사용해 연속적인 rows를 비교하기

---

SELF JOIN 도입

SELF JOIN은 계층적 데이터를 쿼리할 때나 같은 table 내에서 한 row를 다른 rows와 비교할 때 종종 사용된다.

SELF JOIN을 사용할 때, table aliases(테이블 가명)을 사용해서 단일 쿼리에서 같은 table의 이름을 두 번 이상 반복하지 않도록 해야 한다.

하나의 쿼리에서 table aliases 없이 하나의 table을 두 번 혹은 그 이상 참조하는 것은 에러를 초래할 수 있다는 점에 주목하라.

예시

employees table은 employees 데이터 뿐만 아니라 조직 구조에 관한 데이터도 포함하고 있다. reportsto column은 한 employee의 manager id를 결정하는데 사용된다.

1) INNER JOIN을 사용해 SELF JOIN 하기

전체 조직 구조를 얻기 위해 employees table 자체의 employeeNumber와 reportsTo columns를 사용할 수 있다. employees 표는 두 가지 역할을 가진다

하나는 Manager이고 다른 하나는 Direct Reports이다.

SELECT
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
    employees e
INNER JOIN employees m ON
    m.employeeNumber = e.reportsTo
ORDER BY
    Manager;

output:

Manager	Direct report
Bondur, Gerard	Bondur, Loui
Bondur, Gerard	Gerard, Martin
Bondur, Gerard	Jones, Barry
Bondur, Gerard	Bott, Larry
Bondur, Gerard	Castillo, Pamela
Bondur, Gerard	Hernandez, Gerard
Bow, Anthony	Thompson, Leslie
Bow, Anthony	Firrelli, Julie

…

위의 결과에서 employees와 manger가 매칭되는 데이터만 보여주므로 President를 볼 수는 없다. 왜냐하면 INNER JOIN으로 필터링 되었기 때문이다.

2) LEFT JOIN을 사용해 SELF JOIN 하기

President는 담당 manager도 없고 reportsTo column에서 대상도 존재하지 않아 NULL로 표시되는 employee를 의미한다.

다음의 구문에서는 LEFT JOIN 절을 이용해 President를 포함한다.

SELECT
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
        'Top Manager') AS 'Manager',
    CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
    employees e
LEFT JOIN employees m ON
    m.employeeNumber = e.reportsto
ORDER BY
    manager DESC;

output:

Manager	Direct report
Top Manager	Murphy, Diane
Patterson, William	King, Tom
Patterson, William	Marsh, Peter
Patterson, William	Fixter, Andy
Patterson, Mary	Patterson, William
Patterson, Mary	Bondur, Gerard
Patterson, Mary	Bow, Anthony
Patterson, Mary	Nishi, Mami
Nishi, Mami	Kato, Yoshimi

…

3) SELF JOIN을 사용해 연속적인 rows를 비교하기

SELF JOIN을 사용해 같은 city 안에 있는 customers의 리스트를 뽑아낼 수 있다.

SELECT
    c1.city,
    c1.customerName,
    c2.customerName
FROM
    customers c1
INNER JOIN customers c2 ON
    c1.city = c2.city
    AND c1.customerName > c2.customerName
ORDER BY
    c1.city;

output:

city	customerName	customerName
Auckland	Kelly’s Gift Shop	GiftsForHim.com
Auckland	GiftsForHim.com	Down Under Souveniers, Inc
Auckland	Kelly’s Gift Shop	Down Under Souveniers, Inc
Boston	Gifts4AllAges.com	Diecast Collectables
Brickhaven	Online Mini Collectables	Collectables For Less Inc.
Brickhaven	Collectables For Less Inc.	Auto-Moto Classics Inc.
Brickhaven	Online Mini Collectables	Auto-Moto Classics Inc.
Cambridge	Marta’s Replicas Co.	Cambridge Collectables Co.
Frankfurt	Messner Shopping Network	Blauer See Auto, Co.

…

• c1.city = c2.city는 양 customers가 서로 같은 city인지 확인한다.
• c.customerName > c2.customerName은 같은 customer가 포함되지 않도록 한다.