오라클 데이터베이스 SQL 34강-INNER조인(JOIN)

DBMS 개요

데이터의 무결성

과거에는 데이터를 base화 하지 않고 각각 끊어서 개별적으로 사용했다. 그래서 같은 회사인데도 각부서 마다 데이터가 일치되게 동기화되는 속도가 느렸다. 또 한쪽 부서에서 다루는 업무에서 다른 부서의 데이터가 필요할경우 그 데이터를 단편적으로 복사하여 가져가서 사용하였다. 그러다 보니 전체적인 시스템에서는 데이터가 복제되고 다시말해 중복되고

서로다른 부서에서 다루는 데이터가 똑같은 칼럼을 가지는 경우도 존재했다. 그래서 전체적인 시스템에서 중복이 발생하고 결합이 발생하기 쉬웠다.

 

그래서 데이터를 base화 하는 방식으로 처리해서 문제를 해결하려고 했다. 데이터를 모아서 중복을 제거하는 방식으로 결함을 없애는 즉 무결성이 Database에서 중요한 부분이 었다. 

 

중복된는 것을 최대한 없애기 위해서 중복을 제거하고 그렇게 중복을 제거한 다음에 한 부서에서 쓰고자 하는 내용이, 해당 부서에 존재했지만 다른 부서가 가진는 내용과 동일함으로, 사용하지 않고 다른 부서가 가진내용을 참조하는 방식을 사용함으로 해서 전체적으로 데이터가 결함이 생기지 않게 하는 방법을 상용했다.

 

나누어져서 서로 참조하는 테이블이 있는데, 참조관계를 가지고 서로 참조하는 두개의 테이블을 원래 필요한 정보량 만큼 합치는 것을 JOIN이라고 한다.

 

조인

조인의 종류(ANSI SQL)

• INNER JOIN
• OUTER JOIN
• SELF JOIN
• CROSS JOIN(Cartesian Product)

JOIN의 예

두개의 참조 관계에 있는 데이터를 다룬다. 물론 두개만 합칠 수 있는게 아니라 각각의 테이블이 또 참조하고 있는 계층을 가질 경우에는 2개 3개를 합 칠 수도 있다. 중요한것은 그것이 2개의 테이블이든 3개의 테이블이든 4개의 테이블이든

시퀄문으로써 합치는 방법이 어떻게 되는지 알아 보는것이다.

그림 1-1

 

왼쪽 테이블에 해당하는 키가 회원을 식별할 때 사용되는 ID이다. 오른쪽 테이블에는 이글을 누가 등록했는지를 표현할 때 참조하는 키가 있다. 오른쪽 테이블의 WRITER_ID 컬럼은 누가 해당 테이블의 글을 등록했는지에 대해서 명시하고 있다.  왼쪽테이블에 대한 ID가 사용되고 있다.

 

이 상태에서 테이블이 JOIN된다고 하면 그것은 관계를 통해서 합쳐지는 것이다. 

NEWLEC이라는 ID가 글을 몇번 썻는가? 3번

왼쪽에 ID가 오른쪽에서 글이 등록 될 때 마다 사용되고 있다.

 

즉 왼쪽 테이블의 ID가 오른쪽 테이블의 한 행에 해당하는 '글'을 여러번 쓸 수 있다는 것이다.

그럼 두 테이블은 어떤 관계에 있는가? 왼쪽 테이블이 오른쪽 행(글)을 등록하는 관계에 있다. 그래서 오른쪽 테이블에서 는 WRITER_ID라는 이름을 가지고 왼쪽 테이블의 ID를 쓰게 된 것이다.

 

1:N/ 부모테이블 : 자식테이블

테이블 두개가 있는데, 한쪽이 한쪽에 등록하는 관계로서 존재한다. 이 각각의 테이블을 그냥 테이블이라고 말하는 것 보다는 둘의 차이점을 가지고, 식별할 수 있는 특화된 이름이 있다. 테이블인데 왼쪽 테이블은 오른쪽 테이블을 만들어 내는 관계에 있으므로 부모 테이블이라고 한다. 왼쪽 테이블이 오른쪽 테이블의 행(글)을 여러개 만든것이다.

대부분 부모는 하나쪽에 있고, 하나에 의해서 파생되서 여러개를 만들어내는 그런 형태의 관계를 가지고 있다. 하나쪽이 부모가되고 N쪽이 자식이 된다.

 

테이블의 부모와 자식 관계는 항상 상대적이기 때문에 오른쪽 테이블이 지금 관계에서는 자식이 되지만, 오른쪽 테이블이 또 자식을 낳을 수 도있다. 예) 게시글이 있는데 게시글이 댓글을 여러개 가질 수 있는 것 처럼

 

게시글 테이블과 댓글 테이블을 놓고 보면 그때 게시글 테이블인 오른쪽 테이블은 부모테이블이 되고, 댓글 테이블은 자식테이블이 된다.

 

그림 1-2

이제 시각적으로 두개의 테이블을 합쳐 보자. 합칠 때 레코드도 같이 합쳐지게 되는데, 멤버와 게시글의 레코드수가 다르다. 합쳐질때는 멤버테이블과 게시글 테이블의 레코드 수가 같게 맞춰져야 한다.

그래서 게시글(자식) 테이블이 참조하고 있는 행의 수만큼 멤버(부모)테이블을 복제한다.

 

그림2

위 그림과 같은 모습이 Database가 없던 시절 저장하고 있던 모습이다. 그랬던 것이 부모와 자식테이블로 나누어지고, 또 참조라는 것을 하기 위해 관계를 갖는다. 관계를 가지기 때문에 WRITER_ID같은 참조키가 존재하게 된다.

 

과거에는 위 그림과 같은 형태로 데이터를 저장했기 때문에 저장된 모델에서 정보를 퍼올수 있었고 읽을 수 있었다. 업무적으로 필요한 만큼 그 모양을 갖춰서 데이터를 저장했다.

 

하지만 지금은 데이터의 무결성을 위해 짤라서 저장하고, 합쳐서 사용한다. 번거롭지만 성능은 훨씬 좋다. 저장 할때 반복되는 것들을 다 저장하는 것이 아니라 최대한 중복을 피해서 저장하기 때문, 과거에는 수십만개의 레코드가 될 수 었던걸 하나로 줄일수 있다. 

 

그 얘기는 디스크에 저장한 것을 퍼올릴 때 퍼올릴 수량이 작아 졌다는 것 IO작업을 줄이는게 전체적인 성능을 개선하는데 굉장히 중요하다.

 

그림3

메모리에서 부모테이블의 ID를 필요한 만큼 복사한다고 할 때 복사되는 과정에서 메모리에 성능이 굉장히 높기 때문에

디스크에서 퍼오는 것보다는 수 백 수 천 수 만배 빠른 성능을 낼 수 있다. 메모리상에서 합쳐서 그림2와 같은 테이블이 완성 된다.

 

 

INNER JOIN SQL문

SELECT *

FROM MEMBER I

NNER JOIN NOTICE

ON MEMBER.ID = NOTICE.WRITER_ID;

 

결과를 보면 테이블이 합쳐졌는데 , NOTICE 테이블에 MEMBER의 글을쓴 사용자 정보가 그대 붙어서 출력된다.

MEMBER 테이블은 MEMBER ID가 있고 NOTICE 테이블은 글을 등록한 사용자에 대한 WRITER_ID가 있다. WIRTER_ID는 MEMBER의 ID가 활용된 곳이다. 이 두 테이블을 INNER JOIN할 때 어떤 식으로 JOIN이 되는지 알아보자. 

 

MEMBER 테이블에는 행(MEMBER)가 4명이 있다. 그 중 글을 등록한 멤버는 게시글 테이블에서 확인할 수 있듯이 NEWLEC밖에 없다. 나머지 MEMBER는 글을 쓴 것이 없다.  그래서 MEMBER테이블과  NOTICE테이블을 합칠 때 관계가 있는 것만 합치자 는 것은 MEMBER ID에 해당하는 것을 봤을때, NOTICE에 등록자로 쓰인, 그러니까 NOTICE 글을 작성한 사람만 합치겠다는 것이다. MEMBER 테이블에서 NOTICE 테이블과 관계가 없는 즉 공지글 을 작성하지 않는 행을 OUTER라고 한다. 정리하자면 서로 관계가 있는 레코드들으 INNER 관계가 없는 레코드들으 OUTER이다.

 

JOIN의 이해

아래와 같은 관계가 있는 테이블을 INNER 조인 할 경우에 레코드 수는?

자식테이블이 참조하는 수만큼 부모테이블의 행(MEMBER)를 복제하게된다.

그리고 합치게 된다.

 

관계가 있는 레코드들만 합치는 조인을 INNER JOIN이라고 함.

 

참조키를 기준으로 일치하는 행만 조인

MEMBER

INNER JOIN NOTICE

ON MEMBER.ID = NOTICE.WRITER_ID

 

JOIN의 이해

아래 같은 관계가 있는 테이블을 INNER 조인 할 경우에 레코드(행) 수는?

부모테이블과 자식테이블의 OUTER개수를 제거하고 남은 자식테이블의 개수가 INNER JOIN을 하고나서 레코드(행) 개수가 된다.

INNER JOIN을 하려고 하는데 아까는 MEMBER테이블에서 자식 테이블과 관계가 없는 행이 3개있다. 그 3개의 행을 OUTER라고 한다. 근데 NOTICE테이블에서도 WRUTER_ID에 값이 있는데, 부모테이블에 존재하지 않는 값이 있다. 그러면 이 두개의 테이블을 합치는 과정이 어떻게 일어나게 될까? 부모테이블과 자식테이블 모두 참조관계가 없는 OUTER행은 빠지게 된다.

 

부모테이블과 자식테이블에서 관계가 없는 OUTER행이 사라지고, 관계가 있는 것만 남는다. 그리고 부모는 참조하고 있는 수많큼 복제가 되서, 합쳐지게 된다.

 

 

 

=>오라클에서만 사용하는 SQL이 아니라 ANSI 표준 SQL이다.

 

 

[출처- https://youtu.be/BZPX-arrwHI]