[DB] Join 종류와 동작방식

Intro

---

• 디비를 구성하고, 개발을 진행하다보면, 테이블 하나로 처리가 되는 경우가 매우 드물고 실제 비즈니스 로직에서는 다양한 테이블에 데이터를 합친 결과물을 전달해주는 경우가 많다.
• 이때 여러 릴레이션을 함께 사용할 때 사용하는 DB 연산을 JOIN이라고 하는데, JOIN의 종류와 어떻게 수행되는지 정리해보고자 한다.

JOIN

---

• 릴레이션 하나로 원하는 데이터를 얻을 수 없어 관계가 있는 여러 릴레이션을 함께 사용해야 하는 경우 조인 연산을 수행.
• 조인 연산은 조인 속성을 이용해 두 릴레이션을 조합하여 "하나의 결과 릴레이션을 구성."

자연 조인

• 공통된 속성을 가진 것을 비교.

세타조인

• 세타 조인은 주어진 조인 조건을 만족하는 두 릴레이션의 모든 튜플을 연결한 새로운 튜플로 결과 릴레이션을 구성. (비교 연산자 사용)

동일조인

• 세타 연산자가 '='인 특별한 조인을 동일 조인 (Equi join)이라고 한다.

외부조인

• 일치하는 속성 값이 주문 테이블에 존재하지 않아 결과 릴레이션에서 제외되었던 고객 릴레이션의 마지막 튜플이 결과 릴레이션에 포함.
• 외부 조인 연산에 참여하는 두 릴레이션에 있는 모든 튜플을 결과 릴레이션에 추합.

디비전

• R / S는 릴레이션 S의 모든 튜플과 관련 있는 릴레이션 R의 튜플로 결과 릴레이션을 구성.
• 단 릴레이션 R이 릴레이션 S의 모든 속성을 포함하고 있어야 R / S 연산이 가능.
• 하지만 릴레이션 S모든 속성과 이름이 같은 속성을 릴레이션 R이 포함하고 있어야 하는 의미는 아님.

JOIN 종류

---

• 조인의 종류는 크게 INNER JOIN, OUTER JOIN으로 구분될 수 있다.
• 아래 예시와 결과는 MySQL DB를 기준으로 진행.
• 여기서 OUTER JOIN은 다시 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 구분이 되고, 조인의 조건을 어떻게 명시하느냐에 따라 NATURAL JOIN과 CROSS JOIN(FULL JOIN, CARTESIAN JOIN)으로도 구분이 된다.

참고

• 조인의 처리에서 어느 테이블을 "먼저 읽을 것인지"를 결정하는 것은 매우 중요한데, 그에 따라 처리할 작업량이 달라진다.
• INNER JOIN의 경우 어느 테이블을 먼저 읽어도 달라지지 않는데 반해, OUTER JOIN은 반드시 OUTER가 되는 테이블 먼저 읽어야 하기 때문에 조인 순서를 DB Optimizer(MySQL)가 선택할 수 없다.
• MySQL 조인은 nested-loop 방식으로 지원.

조인 의사 코드

// inner join 의사 코드.

FOR (record1 IN TABLE1) {
        FOR (record IN TABLE2) {
            IF (record1.join_column == record2.join_column) {
                    join_record_found(record1.*, record2.*);
            } ELSE {
                    join_record_notfound();
            }
    }
}

• 조인은 반복 루프 두 개의 테이블을 조건에 맞게 연결해주는 작업으로, OUTER TABLE은 INNER TABLE 보다 먼저 읽어야 하며, 조인에서 주도적인 역할을 한다.

INNER JOIN

• 위 조인 중첩된 반복 루프에서 최종적으로 선택될 레코드가 안쪽 반복 루프에 의해 결정되는 경우를 INNER JOIN이라고 함.
• 즉, 두 개의 반복 루프를 실행하면서 TABLE2에 "IF (record1.join_column == record2.join_column)"조건을 만족하는 레코드만 조인의 결과로 가져옴.
• 위 그림 예시에서는 products 기준으로 만족되는 레코드만 가져오기 때문에, product_id = 10002는 제외하고 결과가 나옴.

OUTER JOIN

// outer join 의사 코드.

FOR (record1 IN TABLE1) {
        FOR (record IN TABLE2) {
            IF (record1.join_column == record2.join_column) {
                    join_record_found(record1.*, record2.*);
            } ELSE {
                    join_record_found(record1.*, NULL);
            }
    }
}

• OUTER JOIN은 INNER JOIN과 달리 만족하는 결과를 찾지 못하는 경우 결과를 버리지 않고, 그대로 포함해서 가져온다.
• 즉, INNER 테이블이 조인의 결과에 전혀 영향을 미치지 않고, OUTER 테이블의 내용에 따라 조인의 결과가 결정된다.
• 위 그림 예시에서 알 수 있듯이, 10002번 레코드의 대해서 Purchase 테이블에서 일치하는 레코드를 찾지 못했지만, 버려지지 않고 NULL값으로 채워 JOIN 결과가 만들어진다.

참고. LEFT, RIGHT, FULL OUTER JOIN

• OUTER JOIN은 OUTER 기준에 따라 조인의 결과가 결정이 되는데, 위 그림의 예시는 LEFT JOIN의 결과로 왼쪽 테이블을 OUTER 기준으로 삼은 것이다.
• 반대로 RIGHT OUTER 를 사용할 경우 Purchase가 기준이 된다.
• 마지막으로, FULL OUTER JOIN 의 경우 LEFT & RIGHT 모두 OUTER를 사용하고 싶을 때 사용된다.

Cartesian Product(JOIN)

• Cartesian Product은 FULL JOIN 또는 CROSS JOIN이라고 하는데, 일반적으로 조인을 수행하기 위해 하나의 테이블에서 다른 테이블로 찾아가는 연결 조건이 필요한데, Cartesian Product은 조인 조건 자체가 없어 2개의 모든 레코드의 조합을 결과로 가져오는 방식이다.

NATURAL JOIN

• NATURAL JOIN은 INNER JOIN과 같은 결과를 가져오지만 표현 방법이 조금 다른 방법 중 하나이다.

// **INNER JOIN**
SELECT * 
FROM product pd, purchase pc
WHERE pd.customer_id = pc.customer_id;

SELECT * 
FROM product pd
INNER JOIN purchase pc ON  pd.customer_id = pc.customer_id;

SELECT * 
FROM product pd
INNER JOIN purchase pc ON USING(customer_id);

// UNSING 키워드는 조인 칼럼이 같은 이름일 경우만 사용 가능.

// NATURAL JOIN
SELECT * 
FROM product pd
NATURAL JOIN purchase pc;

• 위 예시에서 NATURAL JOIN은 customer_id로 같은 칼럼 이름으로 조인이 이루어짐.
• NATURAL JOIN은 같은 칼럼 이름으로 조인이 이루어지는데, 칼럼 이름에 의해 쿼리가 자동으로 변경될 수 있는 문제가 있으므로 유지 보수 측면에서 지양하는 것이 좋다.