[DataBase] 04. SQL(Structured Query Language) - Intermediate(2)

 

SQL(Structured Query Language) - Intermediate(2)

#Database/Concepts/Temp
(10/10) 굉장히 공들여서 작성한 글이여서 원래 동아리 스터디 용도로 일부 공개 했었는데, 더 많은 분들이 봤으면 하는 마음에 오픈!

Outer Join

OUTER JOIN은 일치하는 행이 없더라도, 한쪽 또는 양쪽 테이블의 모든 행을 포함하여 조인한다.

• 정보 손실을 방지하는 join 작업의 확장입니다.
  • join한 다음, 다른 relation의 튜플과 일치하지 않는 튜플을 join 결과에 추가합니다.
  • Outer join은 일치하는 데이터와 일치하지 않는 데이터를 모두 기반으로 합니다.
  • NULL 값을 사용합니다.

테이블 A, 테이블 B가 있고, 테이블 A를 LEFT Table이라고 하고, 테이블 B를 RIGHT Table이라고 하자.
Outer Join은 다음과 같이 동작한다.

1. ON 조건에 따라 테이블 A, B에서 공통된 튜플을 고른다.
2. 일치하지 않는 튜플을 결과 relation에 추가해준다.

여기서 LEFT Table에 남아있는 것을 가져올 지, 아니면 RIGHT Table에 있는 것을 가져올 지, 혹은 두 Table 남아있는 것 모두를 가져올 지에 따라 3가지로 분류할 수 있다.

OUTER JOIN에는 세 가지 타입이 존재한다.

• LEFT OUTER JOIN (= LEFT JOIN)
• RIGHTOUTER JOIN (= RIGHT JOIN)
• FULL OUTER JOIN (= FULL JOIN)

참고 | Outer Join의 동작을 위에서 2가지 단계로 나눴는데, 실제로 DBMS에서는 위에서 설명한 1->2 순서로 작동하지 않는다. Inner Join과의 차이점을 설명하기 위해 저렇게 분류한 것이고, 실제 동작 순서에 대해서는 아래에서 다루겠다.

LEFT OUTER JOIN (또는 LEFT JOIN)

한 줄 설명 : 공통 튜플 + LEFT Table에 남아있는 튜플

• Left outer join은 두 테이블을 join하고 SQL 표현식이 참인 두 테이블의 모든 일치하는 행을 가져오며, 두 번째 테이블(RIGHT)에서 일치하는 행이 없는 첫 번째 테이블(LEFT)의 행도 가져옵니다.
  • 즉, Left outer join은 두 테이블의 일치하는 데이터를 가진 결과 세트 테이블을 반환하고, 왼쪽 테이블의 나머지 행과 오른쪽 테이블의 열에서 null을 반환합니다.

Syntax

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

• Left outer join은 왼쪽 테이블(table1)의 모든 레코드와 오른쪽 테이블(table2)의 일치하는 레코드를 반환합니다.
  • 일치하는 레코드가 없으면 오른쪽의 결과가 NULL이 된다.
  • —> 조인 조건에 일치하는 데이터가 오른쪽 테이블에 존재하지 않을 경우 그 결과에 오른쪽 테이블의 해당 열들이 NULL로 표시된다는 말이에요

참고) Outer Join의 동작 순서

1. ON 조건에 따라 테이블 A, B에서 공통된 튜플을 고른다.
2. 일치하지 않는 튜플을 결과 relation에 추가해준다.

이렇게 동작하는 것 처럼 보이지만 실제 DBMS에서는 위 순서로 동작하지 않습니다. 예를 들어서 동작 순서에 대해 다뤄보겠습니다.

products 테이블에 proudct_id, name, price 3가지 Attribute가 있고, Purchase 테이블에는 product_id, date, Count 3가지 Attribute가 있다고 합시다.

그리고 products.A와 Purchase.A를 Join Condition으로 사용해서 LEFT Outer Join을 한다면 다음과 같이 동작합니다.

product_id가 일치하는 것만을 골라서 추가한 뒤에, products 테이블에 남아 있는 튜플을 추가하는게 아닙니다. 실제로는 아래와 같이 동작합니다.

1. 왼쪽 테이블의 각 튜플을 가져옴:

• LEFT OUTER JOIN의 경우, DBMS는 왼쪽 테이블의 첫 번째 행(튜플)을 선택합니다.
• 이 과정은 왼쪽 테이블의 모든 행을 순차적으로 처리하면서 반복됩니다.

2. 오른쪽 테이블의 각 튜플과 비교:

• 선택된 왼쪽 테이블의 행과 오른쪽 테이블의 모든 행을 비교합니다.
• 이 비교는 ON 절에서 지정한 조건에 따라 이루어집니다.

1. 조인 조건에 일치하는 행이 있는 경우:

• 만약 오른쪽 테이블에서 조인 조건에 일치하는 행이 발견되면, 두 행을 결합하여 결과에 추가합니다.
• 결과는 두 테이블의 데이터가 합쳐진 상태로 반환됩니다.

2. 조인 조건에 일치하는 행이 없는 경우:

• 오른쪽 테이블에 일치하는 행이 없을 경우, 왼쪽 테이블의 행이 여전히 결과에 포함됩니다.
• 이때 오른쪽 테이블의 데이터(date, Count)는 NULL로 채워져서 결과에 추가됩니다.

// 의사 코드
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);
            }
    }
}

---

예제: company table과 foods table의 Left Outer Join

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
LEFT OUTER JOIN foods
ON company.company_id = food.company_id;

결과

예제: Course table과 Prerequisite table 간의 Left Outer Join

Right Outer Join

Left Outer Join과 반대로, 공통된 튜플을 가져오고 Right Table에 남아있는 튜플 또한 가져온다

• Right outer join은 두 테이블을 join하고 조건에 따라 행을 가져오며, 두 테이블에서 일치하는 행과 JOIN 절 다음에 작성된 테이블에서 일치하지 않는 행도 가져옵니다.

Syntax

SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

• table1의 일치하는 레코드와 함께 table2의 전체 레코드 세트를 가져온다. 일치하지 않으면 왼쪽에 NULL로 저장된다.

예제: company table과 foods table의 Right Outer Join

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
RIGHT OUTER JOIN foods
ON company.company_id = food.company_id;

 

 

Full Outer Join

양쪽 테이블의 모든 행을 반환, 일치하지 않는 부분은 NULL로 채움.
LEFT Outer Join + Right Outer Join 느낌

문법

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

예제: 두 테이블을 full join을 사용하여 결합

SELECT *
FROM table_A
FULL OUTER JOIN table_B
ON table_A.A=table_B.A;

• 이것은 full join이므로, 두 테이블에서 일치하는 것과 일치하지 않는 모든 행이 출력에 포함됩니다.
• table_A와 table_B 사이에 일치하는 것이 하나만 있고, 이 튜플은 output의 모든 column에 값이 표시됩니다.
• output의 나머지 행은 table_A 또는 table_B의 값만 포함하며, 나머지 column은 누락된 값으로 설정됩니다.

예제: company table과 foods table의 Full Outer Join

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name,
FROM company
FULL OUTER JOIN foods
ON company.company_id = food.company_id;

 

 

• Full outer join은 Left outer join과 Right outer join의 결과를 결합한 것입니다.

Inner Join 과 Outer Join의 차이점

아래 설명이 복잡하게 나와있는데 간단히 요약하자면,
Inner Join은 조건을 만족하는 레코드만 가져오지만, Outer Join은 조건을 만족하지 않는 레코드도 가져온다.

Inner Join

• Inner Join은 두 테이블의 모든 행을 반환하는 join의 한 종류로, 한 테이블의 키 레코드가 다른 테이블의 키 레코드와 동일한 경우입니다.
  • 이 유형의 join은 비교 연산자를 사용하여 두 테이블의 공통 필드 또는 열을 기준으로 행을 일치시킵니다.

⠀Outer Join

• Outer Join은 조건을 만족하는 테이블의 모든 행과 조건을 만족하지 않는 행도 반환합니다.
  • LEFT OUTER JOIN: 조인 절의 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하지 않는 행을 NULL 값으로 포함합니다.
  • RIGHT OUTER JOIN: 조인 절의 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하지 않는 행을 NULL 값으로 포함합니다.
  • FULL OUTER JOIN: 조인 절의 왼쪽 테이블의 모든 행과 오른쪽 테이블의 모든 행을 비교해 포함시키는데, 일치하지 않는 행은 NULL 값을 넣어 포함시킨다.

여기까지 Join 연산에 대해 모두 살펴보았다.