[Oracle SQL] 쉽게 배워보는 JOIN의 원리 (Cross Join, Inner Join, Left Join)

소개

6개월차 신입 데이터 분석가입니다. SQL에 대해 조금 더 깊게 이해하고자 공부를 시작합니다. 

 

www.yes24.com/Product/Goods/64391533

불친절한 SQL 프로그래밍

본 시리즈는 기본적으로 해당 도서를 참조하여 작성되었습니다.

아래와 관련된 키워드로 검색해보시면 환경세팅 관련 많은 도움을 받으실 수 있습니다.
- oracle sql developer scott hr
- oracle sql developer 설치
11g 설치는 https://data-make.tistory.com/2 해당 링크가 설명이 잘되어 있습니다.

환경은 Oracle SQL Developer에서 제공해주는 hr과 scott DB를 사용합니다. 해당 DB에서 기본적으로 제공해주는 테이블이 있고 제가 직접 테이블을 업로드하거나 생성할 수도 있습니다. 

 

gibles-deepmind.tistory.com/5

 

SQL 관련 포스팅 링크를 모아둔 아카이브 페이지가 있습니다. 

본 시리즈의 기본적인 문법은 Oracle SQL입니다. 현업에서 몇개월 간 종사해본 입장에서 DB별 SQL 문법 차이는 그렇게 크지 않습니다. 문법보다는 원리에 집중하시면 좋을 것 같습니다. 

글의 목표

1. 병합의 의미에 대해서 생각해봅니다. 
2. 간단한 Python 코드로 CROSS JOIN과 INNER JOIN을 살펴봅니다.
3. LEFT OUTER JOIN에서 왜 OUTER를 해야하는지에 대해 생각해봅시다. 
4. NESTED FOR LOOP라는 단어의 의미를 살펴 봅니다.

---

1. 병합

A join is a query that combines rows from two or more tables, views, or materialized views.
- https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF30046

오라클 공식 문서에 따르면 join = combine이다.  한자로는 병합이라고 한다. 

병합 : 둘 이상의 기구나 단체, 나라 따위가 하나로 합쳐짐. 또는 그렇게 만듦.
- https://ko.dict.naver.com/#/entry/koko/939828fb2da642beb90765d7f4ac2ccd

병합이란 두개 이상의 대상을 하나로 합치는 행위를 말한다. 여기서 중요한 것은 합치는 기준이다. 그리고, 각 병합의 대상에서 어떤 정보를 남길 것인지도 중요하다. 

1. 병합 기준
2. 병합 대상에서 남길 정보

결론적으로 JOIN을 하기 위해서는 위 두 가지에 대해서 의사결정을 해야 한다.

---

2. Python Code로 살펴보는 CROSS JOIN과 INNER JOIN

2-1. CROSS JOIN

CROSS JOIN은 Cartesian Products를 기반으로 한다.

https://www.wikiwand.com/en/Cartesian_product

Cartesian Products는 A = {x, y, z}, B = {1, 2, 3} 다음 두 집합이 있을 때, 각 집합의 모든 원소가 서로 순서쌍을 이루는 것을 말한다. Python Code를 한번 살펴보자.

 

Col_one = [1, 2, 3, 4, 5]
Col_two = [1, 3, 3, 5, 5]

total_row = 0
for i in Col_one :
    for j in Col_two : 
        print(f"첫 번재 열 : {i}, 두 번째 열 : {j}")
        total_row+=1
print(f"총 {total_row}행 {len(Col_one)}X{len(Col_two)} = {total_row}")

Col_one과 Col_two 두 개의 리스트 순서쌍을 출력하는 코드이다. 

Col_one 리스트의 각 원소 하나 하나에 대해 Col_two 리스트의 모든 원소들이 대응되는 형식이다. 이에 따라 총 5X5 =25개의 행이 출력되었다. 

 

결론적으로 위와 같은 결과가 출력된다. 따라서, Cross join에서 출력되는 행의 수는 테이블1의 행의 수 X 테이블2의 행의 수가 된다. 만약, 테이블이 3개이면?

​

테이블1의 행의 수 X 테이블2의 행의 수 X 테이블3의 행의 수가 된다. 즉, 테이블 개수가 늘어나면 그에 따라 행 수가 굉장히 많이 늘어날 것이다. 왜 이런 결과가 출력됐을까?

If two tables in a join query have no join condition
- https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52351

결론적으로 조인에 어떤 조건도 없기 때문이다.

즉, 조인에 조건이 없기 때문에 병합은 하되 가능한 모든 경우의 수를 출력한 것이다.

-- CROSS JOIN
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A, purchase_order B;

-- CROSS JOIN (ANSI)
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A
CROSS JOIN purchase_order B;

ORACLE SQL에서 CROSS JOIN을 하는 문법이다. INNER JOIN에서 코드는 좀 더 자세히 설명하겠다.

2-2. INNER JOIN

INNER JOIN은 테이블 A와 테이블 B 두 테이블에 공통적으로 존재하는 정보에 대해서만 병합을 진행한다. 

Col_one = [1, 2, 3, 4, 5]
Col_two = [1, 3, 3, 5, 5]

아까 예시 리스트를 보면 Col_one에서 1, 3, 5 그리고, Col_two에서 1, 3, 5가 병합 대상 후보라고 할 수 있다.

total_row = 0
for i in Col_one :
    for j in Col_two :
        if i == j :
            print(f"첫 번재 열 : {i}, 두 번째 열 : {j}")
            total_row+=1
        else:
            pass
print(f"총 {total_row}행")

잠깐, 틀린그림찾기! CROSS_JOIN과 차이가 뭘까?

 

정답은! i == j 

 

동일한 값인지 체크하는 조건이 추가되었다. 이로써 아까 언급했던 "INNER JOIN은 테이블 A와 테이블 B 두 테이블에 공통적으로 존재하는 정보에 대해서만 병합을 진행" INNER JOIN의 조건을 충족시켰다.

결과적으로 Col_one과 Col_two에 공통적으로 존재하는 모든 정보들이 매핑되었다. 

-- INNER JOIN
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A, purchase_order B
WHERE A.personID = B.personID
order by 1;

-- INNER JOIN(ANSI)
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A
INNER JOIN purchase_order B
ON A.personID = B.personID
order by 1;

여기서 ANSI는 표준 SQL 문법이다. 그리고, INNER JOIN만 표시되어 있는 상단 쿼리는 ORACLE 문법을 의미 한다. 

 

앞서 CROSS JOIN과의 어떤 차이가 보이는가?

 

정답은 ON/WHERE 이다. 즉, 어떤 조건을 가지고 병합을 했다는 의미가 된다. INNER JOIN에서 그 조건이란 등호 즉, 같다이다. 

 

A와 B는 왜 붙이지? FROM에 나오는 테이블과 INNER JOIN에 나오는 테이블은 뭐지?에 대한 궁금증이 있을 수 있다. 관련 내용은 다음 챕터에서 자세히 설명하겠다.

---

3. LEFT OUTER JOIN

OUTER는 INNER의 반대말이다. 뭔? 나도 그정도는 아는데 할 수도 있지만 굉~장히 중요하다. 

https://www.dataquest.io/blog/sql-joins-tutorial/

SQL JOIN에 대한 벤 다이어그램이다. 우리는 여기서 INNER JOIN과 LEFT JOIN에 대해서만 살펴볼 것이다. 왜냐하면, INNER/LEFT 두 가지를 이해하면 나머지 개념에 대한 이해는 자연스럽기 때문이다.

 

LEFT JOIN과 RIGHT JOIN 각 단어 사이에는 OUTER라는 단어가 빠져있다. 

OUTER : INNER에서 확장한다.

위와 같이 OUTER의 의미를 이해할 수 있다. 그렇다면, 우리는 여기서 무엇을 확장할 것인지에 대해 의사결정을 해야 한다. 

 

앞서 말했던 "각 병합의 대상에서 어떤 정보를 남길 것인지도에 대한 의사결정" 과 매치 된다. 관련해서 일전에 만들었던 데이터 셋을 참고해보자. gibles-deepmind.tistory.com/6

[Oracle SQL] 직접 만들어보며 배우는 SQL 기본키(Primary Key) VS 외래키(Foreign Key)

앞서 위와 같이 두 가지 테이블을 생성했었다. 여기서 구매 이력이 있는 유저와 그렇지 않은 유저를 구분해보고자 한다. 

-- LEFT OUTER JOIN
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A, purchase_order B
WHERE A.personID = B.personID(+)
order by 1;

-- LEFT OUTER JOIN(ANSI)
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A
LEFT OUTER JOIN purchase_order B
ON A.personID = B.personID
order by 1;

쿼리 실행

쿼리를 실행하면 다음과 같은 결과가 나오는데 여기서 PURCHASE_ID가 NULL인 유저가 구매 이력이 없는 유저 NULL이 아닌 유저가 구매 이력이 있는 유저라고 할 수 있다. 

JOIN 문법 상세

ANSI LEFT OUTER JOIN을 기준으로 JOIN 문법을 한번 뜯어 보자.

-- LEFT OUTER JOIN(ANSI)
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A
LEFT OUTER JOIN purchase_order B
ON A.personID = B.personID
order by 1;

FROM user_info A
LEFT OUTER JOIN purchase_order B

 

Driving Table / OUTER TABLE / INNER TABLE 등 JOIN 테이블을 부르는 여러 이름들이 있다. 따라서, "기준"이라는 의미는 너무 신경쓰지 말자. 우선은 LEFT즉 왼쪽에 있는 테이블의 행이 보존된다는 의미만 기억하자.

쿼리 실행

ON A.personID = B.personID은 JOIN 기준을 의미한다. A와 B의 personID가 서로 일치하는지 비교한다는 의미가 된다. 

 

또한, FROM user_info A LEFT OUTER JOIN purchase_order B 이런식으로 각각 A와 B로 테이블 이름을 약어로 지정했다. 이렇게 약어 지정을 한 이유는 쿼리 하나에 사용되는 테이블이 여러개인 경우 각 테이블을 명시해주어야 하기 때문이다. 그리고, 그때마다 user_info, purchase_order를 치기에는 너무 길다!

 

그래서, A/B 이렇게 약어를 사용한다. A.personID와 B.personID는 각각 A테이블과 B테이블에서 나온 personID라는 의미가 된다. 

-- LEFT OUTER JOIN
SELECT A.personID info_ID
     , B.personID purchase_ID
FROM user_info A, purchase_order B
WHERE A.personID = B.personID(+)
order by 1;

이제 다시 Oracle Join 문법을 살펴보자. (+)가 Outer로 보이지 않는가? 즉, B.personID를 Outer하겠다는 의미가 된다.

---

4. Nested For loop

대부분 조인을 처음 접하며 배우는 형태는 Nested For loop 조인이다.

사실, 이걸 설명하기 위해 파이썬 코드를 사용했다. 

total_row = 0
for i in Col_one :
    for j in Col_two :
        if i == j :
            print(f"첫 번재 열 : {i}, 두 번째 열 : {j}")
            total_row+=1
        else:
            pass
print(f"총 {total_row}행")

파이썬을 잘 모르더라도 시각적으로 for이 두번 중첩되어 있다는 것을 알 수 있다. 즉, 첫 번째 For에 해당하는 Col_one의 각 원소들에 대해 Col_two의 모든 원소들이 각각 대응된다는 것을 알 수 있다.

 

Driving Table의 의미를 생각하면 Col_two가 먼저냐 Col_one이 먼저냐도 따져봐야 하지만, 우선은 이중 루프에 대해서만 생각해보자.

 

테이블이 클수록 당연히 연산량이 많아질 것이다. 따라서, 다루는 테이블이 많고 커질수록 각 테이블의 크기를 비교하거나 Hash Join / Merge Join / Map Join(Spark), Indexing등의 기법이 중요해지는 것 같다.

 

아직 모르는 것이 너무 많은 것 같다. 꾸준히 공부해야지.